36.10. 사용자 정의 집계 함수

36.10.1. 이동-집계 모드

집계 함수는 이동-집계 모드 moving-aggregate mode 를 선택적으로 지원한다. 이 기능은 창 함수 사용에서 프레임 시작 위치를 이동하는 것을 지원해 집계 작업을 보다 빠르게 수행할 수 있도록 한다. (3.5절과 4.2.8절에서 창 함수 기능을 하는 집계 함수에 대한 설명을 하고 있다.) 기본 아이디어는 일반적인 "순차 진행" 전달 함수에다가, 역 진행 전달 함수를 추가해서, 창 프레임을 벗어날 때 창 범위 안에서의 변경된 상태 값들을 그 창 안에서만 사용하도록 하는 것이다. sum 집계 함수를 예로 들면, 순차 진행 전달 함수 - 더하기 기능 - 와, 역 진행 전달 함수 - 빼기 기능 - 를 더 추가한다. 이때 역 진행 전달 함수를 사용하지 않으면, 새로운 창에서 시작하는 기본 상태 값을 계산하기 위해서 매 번 전체 자료를 다시 계산해야 하는 문제가 발생한다. 이런 문제를 피하기 위해 역 진행 전달 함수를 사용하면 해당 창을 벗어 날 때는 창 시작 시점의 상태 값을 반환해서 그 값으로 다음 창에서 집계 작업을 추가 작업 없이 시작할 수 있다.

역 진행 전달 함수에는 현재 상태 값과 이전 집계 작업에서 사용된 현재 값을 입력 받는다. 이전 값이 없이 이 호출이 해당 창의 첫 호출이었다면, 현재 상태 값을 재구성해야 한다. 이런 특성 때문에, 순차 진행 전달 함수는 일반적인 집계 함수의 전달 함수 보다 많은 상태 값을 처리해야 한다. 그러므로, 이동-집계 모드를 지원하려면, 일반 집계 함수와 별개로 이 집계 작업 관련 함수를 지정해야 한다. 물론 필요하다면, 창을 벗어날 때 따로 지정하는 마무리 함수도 필요할 것이다. 특수한 상황이 아니라면, 이들 함수에서 사용하는 입력, 반환 자료형은 일반 집계 함수에서 사용하는 것과 동일하다. state.

위에서 작성한 복소수 합계용 sum 집계 함수를 창 함수로 사용하려면 다음과 같이 구성한다:

CREATE AGGREGATE sum (complex)
(
    sfunc = complex_add,
    stype = complex,
    initcond = '(0,0)',
    msfunc = complex_add,
    minvfunc = complex_sub,
    mstype = complex,
    minitcond = '(0,0)'
);

옵션 이름들은 모두 앞에 m 문자를 덧붙여 기존 이름과 같이 사용한다. 역 진행 전달 함수를 지정하는 옵션 이름만 minvfunc으로 새롭게 사용한다.

이동-집계 모드에서 쓸 순차 진행 전달 함수는 새 상태 값으로 null을 반환할 수 없다. 역 진행 전달 함수가 null을 반환하면, 현재 입력된 값과 상태 값을 대상으로 이전 상태 값을 계산 할 수 없음을 의미한다. 이런 상황에서는 현재 창 처음 위치로 다시 이동해서 다시 계산한다. 이 정책은 현재 상태 값이 처리 불가능한 특수한 상황에서도 이동-집계 모드를 사용할 수 있도록 하기 위함이다. 역 진행 함수는 이 경우 "희생타"를 칠 수 있다. 이렇게 해서 대부분의 경우는 그대로 계속 진행하고, 역 진행 함수용 상태 값은 초기화 된다. 예를 들어 부동소수 계산에서 가끔 발생하는 NaN (숫자 아님, 음수 제곱근인 경우) 값이 입력될 경우, 현 상태 값에 반영을 하지 않도록 처리한다.

이동-집계 모드를 지원하는 집계 함수를 만들 때는, 역 진행 전달 함수의 상태 값이 그 함수 실행 결과가 이전 순차 진행 전달 함수의 결과와 완벽하게 같도록 주의해야 한다. 그렇지 않으면, 최종 실행 결과 값이 전혀 엉뚱하게 나올 수 있다. 예를 들어, float4 또는 float8 자료형에 대한 sum 집계 함수를 단순하게 아래와 같이 작성했을 때:

CREATE AGGREGATE unsafe_sum (float8)
(
    stype = float8,
    sfunc = float8pl,
    mstype = float8,
    msfunc = float8pl,
    minvfunc = float8mi
);

이 함수의 결과는 다음과 같은 경우, 역 진행 전달 함수가 있을 때와 없을 때의 차이가 난다. 다음 쿼리를 가정해 보자.

SELECT
  unsafe_sum(x) OVER (ORDER BY n ROWS BETWEEN CURRENT ROW AND 1 FOLLOWING)
FROM (VALUES (1, 1.0e20::float8),
             (2, 1.0::float8)) AS v (n,x);

두번째 결과값으로 1이 나와야 하는데, 0이 나왔다. 이 원인은 부동소수형의 정밀도 문제다. 1 더하기 1e20 는 부동소수형에서는 1e20이며, 거기서 1e20 값을 빼니 0이 되어버린 것이다. 이 한계는 PostgreSQL의 문제가 아니라, 컴퓨터가 처리하는 부동소수형의 한계다.

36.10.2. 다형 자료형과 가변 인자 집계 함수

집계 함수의 전달 함수와 마무리 함수에서 다형 자료형을 사용할 수 있다. 이런 형태로 만들면, 하나의 함수로 여러 집계 함수에 사용할 수 있다. 다형 자료형 함수에 대한 자세한 설명은 36.2.5절에서 하고 있다. 더 나아가 집계 함수 그 자체도 상태 값과 입력 자료형으로 다형 자료형을 사용할 수도 있다. 물론 이런 집계 함수는 다양한 자료형에 대해서 범용적으로 쓸 수 있다. 다음은 다형 자료형을 사용하는 집계 함수 예제다:

CREATE AGGREGATE array_accum (anyelement)
(
    sfunc = array_append,
    stype = anyarray,
    initcond = '{}'
);

해당 집계 함수의 입력 자료형으로는 어떤 형태의 자료형이 와도 되고, 그 자료를 처리하고, 상태 값을 보관할 때는 임의의 배열형을 사용한다. 이 함수는 입력 값들을 모두 배열 나열하는 배열을 반환하는 함수다. (참고: array_agg 내장 집계 함수가 같은 기능을 하는 함수이며, 위의 함수보다는 보다 성능이 좋다.)

이 집계 함수는 다음과 같이 서로 다른 자료형에 대해서도 사용할 수 있다:

SELECT attrelid::regclass, array_accum(attname)
    FROM pg_attribute
    WHERE attnum > 0 AND attrelid = 'pg_tablespace'::regclass
    GROUP BY attrelid;

   attrelid    |              array_accum              
---------------+---------------------------------------
 pg_tablespace | {spcname,spcowner,spcacl,spcoptions}
(1 row)

SELECT attrelid::regclass, array_accum(atttypid::regtype)
    FROM pg_attribute
    WHERE attnum > 0 AND attrelid = 'pg_tablespace'::regclass
    GROUP BY attrelid;

   attrelid    |        array_accum        
---------------+---------------------------
 pg_tablespace | {name,oid,aclitem[],text[]}
(1 row)

통상, 다형 자료형을 반환하는 집계 함수는 윗 예제처럼 다형 자료형 상태 값을 사용한다. 이렇게 하는 이유는 마무리 함수의 반환 자료형을 알맞게 선택할 수 없기 때문이다. 마무리 함수의 반환 값은 다형 자료형이어야 하는데, 내부적으로 사용한 자료형이 다형 자료형이 아닌 경우, CREATE FUNCTION 명령에서 자료형 불일치로 해당 함수를 선언할 수 없게 된다. 하지만 때로는 상태 값으로 사용할 자료형으로 다형 자료형을 사용하는 것이 불편할 때가 있다. 대부분 집계 함수를 만들 때 지정하는 내장 함수들은 C 언어로 만들며, 이때는 SQL 수준으로 처리하지 않기 때문에, internal 형으로 지정한다. 이 경우, 집계 작업에 사용한 입력 인자 자료형과 맞출 부가적인 "더미" 매개 변수를 마무리 함수에서 사용할 수 있도록 지정할 수 있다(finalfunc_extra). 이 더미 인자는 마무리 함수가 호출 될 때 임의의 값이 할당 되지 않는 한 항상 null을 전달한다. 이 인자는 단지 집계 작업에서 사용하는 입력 자료형과 마무리 함수의 반환 자료형을 연결하는 용도로만 사용된다. 예를 들어, 실제 내장 array_agg 집계 함수는 다음과 같이 선언되어 있다.

CREATE FUNCTION array_agg_transfn(internal, anynonarray)
  RETURNS internal ...;
CREATE FUNCTION array_agg_finalfn(internal, anynonarray)
  RETURNS anyarray ...;

CREATE AGGREGATE array_agg (anynonarray)
(
    sfunc = array_agg_transfn,
    stype = internal,
    finalfunc = array_agg_finalfn,
    finalfunc_extra
);

위와 같이 finalfunc_extra 옵션을 설정하면, 마무리 함수는 상태 값과 함께 집계 작업에서 사용한 입력 인자와 관련된 부가적인 더미 인자를 함께 사용한다. 윗 예제에는 array_agg_finalfn 함수는 그것으로 anynonarray형을 부가적으로 사용한다.

집계 함수는 대부분의 일반 함수에 사용하는 방법처럼, 마지막 인자에 VARIADIC 배열을 사용해서 가변 인자를 구성하는 것과 같이 가변 인자를 사용할 수 있다; 36.4.5절 참조. 이렇게 전달되는 인자들은 전달 함수에서도 같은 방법으로 사용할 수 있다. 지정하는 방법은 VARIADIC 인자 사용하는 방법과 같으며, 반드시 사용해야 하는 것은 아니다.

36.10.3. 정렬된-집합 집계 함수

지금까지 소개한 집계 함수는 "일반" 집계 함수였다. PostgreSQL에서는 일반 집계 함수와는 두가지가 다른 정렬된-집합 집계 함수도 지원한다. 첫번째, 일반 집계 함수는 입력 인자가 매번 계산되지만, 정렬된-집합 집계 함수에서는 딱 한 번만 계산되는 "직접" 인자를 사용할 수 있다. 두번째, 이 함수는 반드시 정렬에 대해 지정하는 구문이 반드시 뒤에 있어야 한다. 정렬된-집합 집계 함수는 일반적으로 순위나, 백분율 같이 어떤 기준으로 정렬된 로우 집합을 대상으로 계산할 때 사용한다. 그래서, 이 함수 내부 계산에서 이 정렬된 집합은 항상 사용된다. 예를 들어, percentile_disc 내장 집계 함수는 다음과 같이 정의되어 있다:

CREATE FUNCTION ordered_set_transition(internal, anyelement)
  RETURNS internal ...;
CREATE FUNCTION percentile_disc_final(internal, float8, anyelement)
  RETURNS anyelement ...;

CREATE AGGREGATE percentile_disc (float8 ORDER BY anyelement)
(
    sfunc = ordered_set_transition,
    stype = internal,
    finalfunc = percentile_disc_final,
    finalfunc_extra
);

이 집계 함수는 직접 인자로 float8형 인자로 지정했고 (이 인자에 백분율 값을 지정함), 정렬된 집합으로 사용할 그 기준으로 임의의 자료형을 지정했다. 이 집계 함수는 다음과 같이 사용한다:

SELECT percentile_disc(0.5) WITHIN GROUP (ORDER BY income) FROM households;
 percentile_disc
-----------------
           50489

여기서 0.5는 직접 인자다. 이 값은 집계 작업 도중 바뀌지 않으며, 각 로우의 백분율 값과는 관계 없이 고정값이다.

일반 집계 함수와 달리 정렬된-집합 집계 함수에서 사용하는 입력 로우의 정렬 작업은 내부에서 자동으로 처리되지 않는다. 이 작업 해당 집계 함수에서 지정한 내부 함수들에서 해야 한다. 일반적으로 "tuplesort" 객체를 상태 값으로 사용해서 입력 값들을 그곳에 담아두고, 정렬을 끝나면, 마무리 함수에서 특정 작업을 수행해서 그 "가상" 로우를 실자료와 연결해서 반화는 방식으로 진행된다. 일반 집계 함수에서 사용하는 내부 함수들은 PL/pgSQL 또는 기타 PL 함수로 만들 수 있지만, 정렬된-집합 집계 함수에서 쓸 함수들은 그 상태 값을 SQL 자료형으로 정의할 수가 없어 C 언어로만 작성한다. (윗 예제에서도 상태 값 자료형으로 internal 형을 지정했다 — 이게 일반적이다.)

정렬된-집합 집계함수의 상태 전달 함수는 현재 상태 값과 해당 로우에 대한 집계된 입력 값을 받아, 상태 값을 갱신하고, 반환한다. 일반 전달 함수와 같은 방식이기는 하나, 지정한 직접 인자가 이 함수에서는 사용되지 않음을 기억해야 한다. 마무리 함수에서 이 직접 인자를 사용하고(있다면), (finalfunc_extra 옵션을 지정했다면, null 값과 집계 함수의 입력 자료형과 연결하는 일을 하고), 최종 값을 반환한다. 일반 집계 함수와 마찬가지로, 입력 값의 다형 자료형을 처리할 때 finalfunc_extra 옵션을 사용해서 자료형 일치 작업을 한다.

현재 버전에서는 정렬된-집합 집계 함수는 윈도우 함수로 사용할 수 없기에, 여기서는 이동-집계 모드 관련 설정을 할 필요는 없다.

36.10.4. 부분 집계

선택적으로, 집계 함수는 부분 집계 기능을 사용할 수 있다. 부분 집계 기능은 독립적인 부분 입력 값에 대한 개별 전달 함수가 각자 자신의 상태 값을 가지고 집계 작업을 진행하고, 최종적으로 그 중간 집계된 것을 상태값들을 다시 집계 할 수 있도록 하는 것이다. 이 기능은 병렬 쿼리용 작업자 프로세스가 한 테이블에 대한 일정 부분의 집계 작업을 하는데 사용된다. (또한 미래에는 로컬과 원격 테이블의 통합 집계 작업에서도 사용될 수 있을 것이다. 하지만, 아직 구현하지 못 했다.)

병렬 집계 기능을 사용하려면, 먼저 부분 집계에 작업에서 반환된 자료를 가지고, 이들을 합치는 결합 함수를 제공해야 한다. 이 때 합칠 자료의 순서가 지켜져야 할 집계 작업이라면, 이 결합 함수를 사용할 수 없다. 왜냐하며, 입력 인자 순서가 어느 것이 먼저 올지 알 수 없기 때문이다.

간단한 예로, MAX 또는 MIN 집계 함수가 부분 집계 기능을 사용할 수 있도록 두 배 큼, 또는 두 배 작음 같은 기능을 하는 함수를 전달 함수로 사용할 경우, 그것을 그대로 결합 함수로 사용할 수 있다. SUM 집계 함수인 경우도 그냥 전달 함수를 결합 함수로 쓸 수 있다. (물론 자료형 범위 문제로 형변환을 해야 한다면, 그것을 고려하면 된다.)

결합 함수는 상태 값과 입력 값으로 처리하는 전달 함수와 거의 같은 형태로 처리된다. 특히, null 처리와, strict 함수 처리 방식도 비슷하다. 또한 initcond 옵션 값으로 null 아닌 값을 지정한 경우, 이 초기 상태 값은 각 부분 집계 작업에서도 초기 상태 값으로 사용하고, 결합 함수 작업의 초기 상태 값으로도 사용한다.

상태 값 자료형으로 internal 형을 지정했다면, 결합 함수는 자기가 쓸 상태 값에 맞는 올바른 메모리 컨텍스트를 접근해야 한다. 이 말은 첫번째 입력 값이 NULL인데, 두번째 입력 값 계산을 위한 현재 상태 값으로 null을 사용할 경우, 잘못된 집계를 할 가능성이 발생할 수 있다.

집계 함수에서 사용하는 상태 값을 위한 자료형으로 internal 형을 사용할 경우, 한 프로세스가 다른 프로세스로 전달 할 임시 상태 값 처리를 위한 serialization 함수와 deserialization 함수도 지정하는 것이 일반적이다. 이 함수들을 지정하지 않으면, 병렬 집계 작업을 할 수 없다. 또한 앞으로 구현할 로컬/원격 통합 집계 작업에서도 사용할 수 없을 것이다.

serialization 함수는 internal 형을 입력 인자로, bytea 형을 반환 인자로 사용해야 하며, 넘겨줄 상태 값은 직렬화된 blob 바이트 형태여야 한다. 반대로 deserialization 함수는 bytea, internal 두 입력 인자와 internal 형의 반환 인자로 구성한다. (두번째 입력인자는 사용되지 않고, 항상 0으로 지정하지만, 이는 자료 안정성 문제로 필요하다.) deserialization 함수로 반환된 값은 결합 함수의 반환 값과 달리, 현재 메모리 컨텍스트 안에서 단순하게 할당되어야 한다. 해당 변수의 수명이 길지 않다.

병렬 집계가 가능하려면, 해당 집계 함수 자체를 PARALLEL SAFE 옵션을 지정하고 만들어야 한다. 이 옵션은 그 병렬 집계 함수에 지정한 내부용 각 함수들의 병렬 처리 옵션과 관계가 없다.

36.10.5. 집계 함수용 함수 지원

집계 함수로 사용할 함수를 C로 만든다면, AggCheckCallContext 함수를 사용해서 먼저 이 함수가 집계 함수용임을 다음과 같이 선언해야 한다.

if (AggCheckCallContext(fcinfo, NULL))

이렇게 확인하는 이유는 이 함수가 전달 함수로 사용 될 때, 초기 상태 값에 대한 지정과 그 값의 메모리 영역을 집계 작업 내 계속 사용하기 위함이다. int8inc() 함수의 소스를 보면 그 사용 방법을 알 수 있다. (이것은 참조에 의한 입력 인자(C 포인트 변수)를 변경하는 함수의 안전한 사용 방법일 뿐이다. 집계 작업 끝에 마무리 함수를 따로 지정한 경우는 그 마무리 함수에서는 입력 매개 변수 값을 바로 수정하면 안된다. 왜냐하면, 그 값 그대로 마지막 상태 값이 되는 경우도 있기 때문이다.)

AggCheckCallContext 함수의 두번째 인자는 집계 작업에서 사용하는 상태 값이 있는 메모리 컨텍스트를 찾을 때 사용된다. 이 인자는 상태 값의 내용이 "확장" 객체(36.11.1절 참조)를 사용하는 경우에 유용한다. 첫번째 함수 호출에서 집계 작업의 상태 컨텍스트의 하위 메모리 컨텍스트인 확장 객체를 반환하고, 이후 호출부터는 그 객체를 계속 사용하면 된다. 이 관련 예제는 array_append() 함수의 소스가 대표적이다. (이 array_append() 함수는 범용 내장 집계 함수에서 쓰는 전달 함수는 아니지만, 사용자 정의 집계 함수를 만들 때, 유용하게 쓸 수 있도록 만들어졌다.)

집계 함수용으로 C 언어로 함수를 만들 때 지원하는 함수는 AggGetAggref이다. 이 함수는 집계 작업을 호출하는 Aggref 부분 노드를 반환한다. 이 함수는 정렬된 집합에 대한 집계 작업에서 유용하다. 이 함수는 Aggref 노드의 하위 구조를 조사하는데 사용된다. 예제로 PostgreSQL 소스 안에 orderedsetaggs.c 파일을 살펴보면 된다.