사용자 정의 집계 함수 만들기

http://postgresql.kr/blog/pg_aggregate.html

사용자 정의 집계 함수 만들기

1. 왜 집계 함수까지 만들지?

sum(), avg() 처럼 여러 자료를 집계 해서 하나의 값으로 표현하는 것을 집계 함수라고 합니다. 요즘 같이 데이터 사이언스 이야기가 자주 등장하는 때에 집계 함수는 아주 중요한 키워드 가운데 하나입니다. 대부분 통계 함수들이죠. 평균, 분산, 표준편차, 왜도, 첨도, ... 이런 여러 통계학에서 사용되는 계산들이 대부분 RDBMS에서는 집계 함수라고 합니다.

PostgreSQL은 거의 대부분의 집계 함수를 이미 내장하고 있습니다. 공식 설명서 9.20에서 이들을 자세히 설명하고 있습니다.

일반적으로 사용자 정의 집계 함수는 그 집계 대상 자료가 사용자 정의 자료형인 경우에 그 자료를 집계 해야 할 때 그 집계 함수 또한 사용자가 만들어서 씁니다.

가장 대표적인 것이, postgis 확장 모듈에 있는 각종 집계 함수들이겠지요. postgis 모듈에는 지리 정보라는 자료가 있고, 그 지리 정보에 대한 집계가 필요합니다. 예를 들어 전국 고속도로를 기준으로 하는 국토 총 면적 이런 것들이겠죠. 

이런 작업을 하려고 할 때, 사용자 정의 집계 함수를 만듭니다.

2. 집계 함수를 만들 때 꼭 필요한 것

집계 함수의 기본 작동 원리는 그 함수가 반환되는 자료형과 입력값을 계산하는 함수, 이 두 가지는 꼭 필요합니다.

예를 들어서, sum(int) 함수라면, int형 값들을 더해서 중간 집계하는 함수와, 최종 합계를 반환할 때의 자료형이 필요합니다.

여기서 중간 집계 값을 상태 값이라고 하고, 이 중간 집계 작업을 하는 함수를 전달 함수라고 합니다. 즉, 상태 값 자료형과 전달 함수가 꼭 필요합니다. 이것이 집계 함수를 만들 때 꼭 필요한 것들입니다.

3. 집계 함수 종류

집계 함수의 종류에 대한 자세한 설명은 공식 설명서 36.10에서 자세히 소개하고 있습니다. 여기서는 간단하게 요약만 하겠습니다.

  • 그냥 쭉 계산하는 일반 집계 함수: sum
  • 이동 집계 모드를 이용하는 창(window) 함수: rank
  • 입력 자료형에 구애받지 않는 범용 집계 함수: min, max
  • 집계 함수의 입력 인자가 자료의 값이 아니라, 상수인 정렬된 집합 집계 함수: percentile_disc
  • 병렬 쿼리 지원 집계 함수

이렇게 나열하면, 그냥 막연하게 알고 있던 집계 함수의 종류와 달리 꽤 복잡하다는 것을 알 수 있을 것입니다.

위의 각종 집계 함수를 사용자가 직접 구현할 수 있도록 하기 위해서 PostgreSQL의 CREATE AGGREGATE SQL 구문은 생각보다 복잡합니다. 자세한 내용은 링크를 참조하세요.

4. 상권 구하기

자, 이제 구체적인 예제 하나로 사용자 정의 집계 함수를 만들어 봅니다. 물론 이 상권을 구하는 집계 함수는 postgis 모듈에 이미 있습니다. 실무 안에서는 각 상점들의 지리 정보를 그냥 postgis 모듈에서 제공하고 있는 집계 함수를 사용하면 간단하게 구할 수 있습니다. (postgis 모듈 사용자 설명서의 집계 함수 부분 참조)

여기서는 각 상점을 그냥 PostgreSQL 기본 자료형인 점(point)으로 저장하고, 그 점들을 모두 포함 하는 사각형(box)를 상권으로 처리하도록 하겠습니다.

4.1 표본 자료

$ create table store as 
select point(int4(random() * 100 ), int4(random() * 200)), null::text 
from generate_series(1,100);
SELECT 100
$ \d store
   "public.store" 테이블
 필드명 | 종류  | 기타 조건
--------+-------+-----------
 point  | point |
 text   | text  |

$ select * from store limit 5;
  point   | text
----------+------
 (81,186) |
 (1,172)  |
 (25,6)   |
 (90,151) |
 (18,35)  |
(5개 행)

4.2 집계 함수의 입력 자료형과 반환 자료형

위의 store 테이블의 point 칼럼을 대상으로 상권 범위를 사각형으로 나타내는 sum() 집계 함수를 만들려고 합니다. 이 함수의 입력 자료형은 당연히 point 형이고, 반환 자료형은 box형입니다.

즉, 이 집계 함수는 상태 값이 저장될 자료형이 곧 반환 자료형입니다.

4.3 sum() 집계 함수에서 사용할 전달 함수

집계 함수에 필요한 두 가지 가운데, 나머지 하나는 이 중간 상태 값을 계산 하는 전달 함수입니다.

이 전달 함수는 규칙이 있습니다. 이 전달 함수의 반환 자료형은 상태 값으로 사용하는 자료형이고, 입력 인자의 자료형은 두 개(또는 그 이상)인데, 그 입력 인자의 첫번째 인자는 반드시 현 상태값이 입력되어야 합니다.

여기서는 이 전달 함수의 이름을 appendpoint 라고 했습니다.

CREATE OR REPLACE FUNCTION public.appendpoint(p_box box, p_point point)
 RETURNS box
 LANGUAGE plpgsql
AS $function$
declare
ret_box box;
begin
if p_box is null then
  ret_box := box(p_point,p_point);
else
  ret_box := box(
  point(greatest((p_box[0])[0],p_point[0]), greatest((p_box[0])[1],p_point[1])),
  point(least((p_box[1])[0],p_point[0]), least((p_box[1])[1],p_point[1]))
  );
end if;
return ret_box;
end;
$function$;

설명하면, 사각형과 점을 합쳐 그 점을 포함하는 다른 사각형을 만드는 함수입니다.

입력 되는 현재 상태값(함수의 첫번째 입력 인자)이 null 이면, 두번째 입력되는 점값 그 자체를 사각형으로 해서 반환하고, 그렇지 않으면, 점을 포함하는 사각형을 반환합니다.

point 형의 x, y 값은 각각 [0],[1] 형태로 구하고, box 의 우측 상단([0])과 좌측 하단([1])에 대한 x,y값은 p_box([0])[0] 형태로 구합니다. 이것들의 greatest, least 함수 반환값으로 처리합니다.

함수 작동 예제:

$ select appendpoint(null, '(1,1)');
 appendpoint
-------------
 (1,1),(1,1)
(1개 행)

$ select appendpoint('((1,1),(1,1))', '(2,2)');
 appendpoint
-------------
 (2,2),(1,1)
(1개 행)

4.4 sum(point) 집계 함수

이제 집계 함수를 만들어봅니다.

$ create aggregate sum(point) ( sfunc = appendpoint, stype =  box);
CREATE AGGREGATE
$ select sum(point) from store;
      sum
----------------
 (99,197),(0,1)
(1개 행)

거의 전 영역에 골고루 분포하네요. (random() 함수의 품질을 확인하는 기회)

이때 sfunc 이 전달 함수이고, stype 이 중간 집계 용 상태 값 자료형입니다. 이들은 그냥 이름만 지정합니다.

5. 집계 함수의 병렬 처리

PostgreSQL 9.6 버전에서는 쿼리의 병렬 처리가 가능합니다. (물론 아직 많이 부족하지만, 잘 쓰면 기존 성능보다 좋은 성능을 낼 수 있습니다.)

앞에서 만든 sum(point) 집계 함수를 병렬로 처리할 수 있도록 하려면, 윗 집계 함수를 만들 때, combinefunc 옵션을 추가해야 합니다. 이 옵션의 값으로 지정하는 것은 중간 집계 함수 입니다.

sum(point) 집계 함수에서 중간 집계 함수는 중간 집계 값(box)들의 계산으로 또 다른 box 형을 반환하는 함수여야 합니다.

CREATE OR REPLACE FUNCTION public.appendbox(p_box1 box, p_box2 box)
 RETURNS box
 LANGUAGE plpgsql
AS $function$
declare
ret_box box;
begin
if p_box1 is null then
  ret_box := p_box2;
elsif p_box2 is null then
  ret_box := p_box1;
else
  ret_box := box(
   point(greatest((p_box1[0])[0],(p_box2[0])[0]), greatest((p_box1[0])[1],(p_box2[0])[1])),
   point(least((p_box1[1])[0],(p_box2[1])[0]), least((p_box1[1])[1],(p_box2[1])[1]))
  );
end if;
return ret_box;
end;
$function$;

이렇게 중간 집계 함수를 만들었으면, 이제 sum(point) 집계 함수를 다시 만듭니다. 이때 기억해야 할 것은 이 sum(point) 집계 함수는 병렬 처리를 해도 안전하다는 옵션도 함께 지정해야 합니다. (parallel = safe)

$ drop sum(point);
DROP AGGREGATE
$ create aggregate sum(point) ( sfunc = appendpoint, stype =  box, 
  combinefunc = appendbox, parallel = safe);
CREATE AGGREGATE

앞에서 표본 자료는 자료량이 너무 적어서 병렬처리를 하지 않습니다. 병렬 처리를 하는 최소 테이블 크기(기본값은 8MB)보다 작기 때문입니다. 그래서 일단 store 테이블에 자료를 더 추가하고 병렬 처리 테스트를 합니다.

$ insert into store 
select point(int4(random() * 100 ), int4(random() * 200)), null::text 
from generate_series(1,1000000);
INSERT 0 1000000
$ set max_parallel_workers_per_gather = 4; -- 병렬 처리 활성화
SET
$ explain verbose  select sum(point) from store;
                                           QUERY PLAN
------------------------------------------------------------------------------------------------
 Finalize Aggregate  (cost=114750.79..114750.80 rows=1 width=32)
   Output: sum(point)
   ->  Gather  (cost=114750.08..114750.29 rows=2 width=32)
         Output: (PARTIAL sum(point))
         Workers Planned: 2
         ->  Partial Aggregate  (cost=113750.08..113750.09 rows=1 width=32)
               Output: PARTIAL sum(point)
               ->  Parallel Seq Scan on public.store  (cost=0.00..9573.08 rows=416708 width=16)
                     Output: point
(9개 행)

이 글의 범위에는 벗어나지만, 이렇게 병렬 처리를 하면, 쿼리 수행 시간이 확실히 줄어듭니다. 그 작업 비용은 직접 확인해 보세요.

6. 마무리

지금까지 소개한 것 외에 사용자 정의 집계 함수를 참 다양하게 만들 수 있습니다.  이 부분은 만드는 이의 몫입니다.

지금까지 소개한 것은 전달 함수, 중간 집계 함수만 소개했는데, 마무리 함수, 이동 집계 전달 함수, .... 이런 저런 꽤 많은 함수들이 집계 함수를 만드는데 사용됩니다.

실무 안에서는 이들 집계 함수를 구성하는 작업 함수들은 대부분 C 언어로 만들어집니다. 그래야 집계 작업이 최적의 성능을 낼테니까요.  이 부분에 대해서는 이 글의 범위에서 벗어나 생략합니다. 또한 집계 함수를 만들 때 기억 해야할 것들도 제법 있는데, 이런 부분은 공식 설명서를 참조하세요.

참신한 집계 함수를 만들어 공유하는 분들이 많아졌으면 좋겠네요.