CREATE AGGREGATE

설명

CREATE AGGREGATE 명령은 새로운 사용자 정의 집계 함수를 만든다. 일반적으로 자주 사용하는 범용적인 집계 함수들은 내장 함수로 제공한다. 이 함수들의 설명은 9.20절에서 설명하고 있다. 만일 사용자 정의 자료형을 만들었고, 그 자료형에 대한 집계 작업을 위한 집계 함수가 필요한데 없다면, CREATE AGGREGATE 명령을 이용해서 원하는 기능을 직접 구현할 수 있다.

이 함수 이름에 스키마 이름을 지정한다면 (예, CREATE AGGREGATE myschema.myagg ...), 지정한 스키마 안에 만들어진다. 그렇지 않다면, 현재 스키마에 만들어진다.

집계 함수도 일반 함수와 같이 함수 이름과 입력 매개 변수의 자료형으로 구분된다. 입력 매개 변수의 자료형만 달리 한다면, 같은 이름의 집계 함수를 여러 개 만들 수 있다. 또한 이름도 같고, 입력 매개 변수의 자료형도 같은 집계 함수와 일반 함수가 같은 스키마 안에 있을 수도 있다. 이런 특성은 일반 함수 오버로딩 특성을 그대로 따르기 때문이다 (CREATE FUNCTION 참조).

단순한 집계 함수는 하나 또는 두 개의 일반 함수를 지정해서 만든다: 하나는 전달_함수이고, 추가적으로 마무리_함수를 지정하기도 한다. 이렇게 만들어진 집계 함수는 다음과 같은 흐름으로 작동 한다:

전달_함수( 내부_상태_값, 다음_입력_값 ) ---> 다음_내부_상태_값
마무리_함수( 내부_상태_값 ) ---> 최종_집계_값

PostgreSQL에서는 집계 작업을 위해 집계 작업 중에 사용할 상태 값으로 사용할 상태_값_자료형이 필요하다. 집계 함수를 사용하면, 먼저 그 함수의 입력 인자로 사용한 입력 값에 대해서 차례로 상태 값 전달 함수를 호출 하고, 그 함수는 각 로우별 현재 상태 값을 계산한다. 그렇게 바뀐 상태 값을 다음 전달 함수가 호출 될 때 그 함수의 입력 인자로 사용한다. 여기서 그 집계 함수에 마무리 함수를 지정했다면, 집계 작업이 끝난 다음에, 마지막으로 입력 된 상태 값을 입력 매개 변수로 받아 일련의 작업을 하고, 집계 함수의 반환 값을 반환 한다. 마무리 함수를 지정하지 않았다면, 마지막 전달 함수가 반환한 상태 값이 그 집계 함수의 반환 값이 된다.

하나의 집계 함수에 하나의 초기 조건을 제공할 수 있다. 이는 집계 작업에 필요한 내부용 초기 상태 값을 지정하는 것이다. 이 값을 저장할 자료형으로는 text 자료형 같이 데이터베이스 내 이미 사용할 수 있는 것이여야하며, 그 자료형이 수용하는 상수 값이어야 한다. 이 초기 상태 값을 지정하지 않으면 null 값을 기본 값으로 사용한다.

상태 전달 함수에 "strict" 옵션이 지정되었다면, 집계 대상이 되는 값에 null 값이 있는 경우 이 전달 함수는 호출되지 않는다. 즉, 집계 작업을 건너 뛰게 되고, 상태 값도 변경 되지 않는다. 초기 상태 값이 null이면, 상태 전달 함수에 최초로 입력된 그 값을 상태 값으로 해서 반환한다. 이렇게 해서 각 notnull 자료에 대해서 각 row 별로 순차적으로 실행해서 집계 작업을 진행한다. 이런 방식은 max 집계 함수를 손 쉽게 구현할 수 있다. 기억 해야 할 부분은 이런 정책은 상태값_자료형과 상태 전달 함수가 처음 호출되는 시점에 사용된 입력값_자료형이 같아야 바르게 작동한다는 점이다. 만일 전달 함수가 사용하는 내부용 상태 값의 자료형과, 입력 값으로 사용하는 자료형이 다른 경우는 반드시 null 값이 아닌 초기 조건 값(상태 값으로 사용할 초기 상수 값) 을 지정하거나, 전달 함수가 null 입력 값에도 호출되고, 그 함수 내에서 알맞은 상태 값을 반환 할 수 있도록 해야 한다.

상태 전달 함수가 null 입력값을 허용하는 경우라면, 모든 로우에 대해서 호출된다. 즉 그 함수 안에서 입력된 적절한 처리를 해야한다. 이렇게 해서 집계 작업에서 일어날 수 있는 다양한 상황에 대처 할 수 있다.

마무리 함수가 "strict" 옵션을 사용한다면, 이 또한 마지막 상태 값이 null 이면 이 함수 자체가 호출되지 않고, 자동으로 null 값을 반환한다. (strict 옵션이 지정된 일반 함수의 작동 방식과 동일하다.) 어떤 집계 작업에서는 이런 특성으로 작동 되어야 하는 경우도 있기 때문에, 마무리 함수를 만들 때 잘 고려해야 한다. 예를 들어, avg 집계 함수인 경우, 평균값을 구하기 위해 나눌 로우 수가 0이라면, 마무리 함수에서는 마지막 상태 값을 null로 처리해서 마무리 함수 호출을 안하고 바로 null을 반환한다.

여러 자료형에 대해서 하나의 집계 함수만 만들어 처리하고 싶다면, 내부적으로 입력 값의 자료형이 무엇이었는지를 판단하는 추가적인 매개 변수가 더 필요하다. 이때 마무리 함수는 그 부가 정보를 이용해서 최종 반환 함수의 자료형을 결정해서 처리한다. 이 부가 매개 변수는 항상 null 값으로 전달 되기 때문에, 마무리 함수는 입력 값으로 null을 허용해야한다. (FINALFUNC_EXTRA 옵션을 지정했다면, 마무리 함수는 "strict" 옵션을 사용하면 안된다) 마무리 함수에서는 get_fn_expr_argtype 함수를 이용해서 실재 입력 매개 변수의 자료형이 무엇인지 판단하고 처리한다.

집계 함수는 이동-집계 모드를 선택적으로 지원한다. (이것에 대한 설명은 36.10.1절에서 다룬다.) 이 기능을 사용하려면, MSFUNC, MINVFUNC, MSTYPE 옵션은 필요하고, MSPACE, MFINALFUNC, MFINALFUNC_EXTRA, MINITCOND 옵션은 선택적으로 필요하다. MINVFUNC 옵션을 제외하고, 나머지 옵션은 M 글자를 뺀 옵션들의 역활과 비슷하다. 차이점은 여기에 필요한 옵션들은 창 함수에서의 프레임 범위 안 집계에 적용되는 것들이고, 일반 옵션들은 전체 집계에 적용된다.

정렬된-집합 집계(또는 HYPOTHETICAL이 지정되면 가상-집합 집계)라고 하는 집계 함수를 사용하는 부분에서 ORDER BY 구문을 사용하는 함수를 만들 수 있다. 이런 집계 함수는 집계 작업 전에 정렬 규칙에 따른 집합 하나가 만들어져야 하기 때문에 정렬 순서를 지정하는 것은 필수적이다. 또한 이 함수의 인자로 직접 인자를 사용할 수 있다. 이 인자는 집계 작업을 하는 각 입력 값에 대해서 사용는 것이 아니라, 최종 만들어진 정렬된 집합 대상으로 한 번만 계산하는데 사용된다. Hypothetical-set aggregates are a subclass of ordered-set aggregates in which some of the direct arguments are required to match, in number and data types, the aggregated argument columns. This allows the values of those direct arguments to be added to the collection of aggregate-input rows as an additional "hypothetical" row.

집계 함수는 36.10.4절에서 설명 하고 있는 부분 집계 기능도 제공한다. 이 기능을 구현하려면, COMBINEFUNC 옵션이 필요하다. 상태값_자료형으로 internal형을 사용한다면, 추가적으로 SERIALFUNC, DESERIALFUNC 옵션도 설정해야 한다. 또한 집계 작업에서 병렬 처리가 가능 하도록 하려면, PARALLEL SAFE 옵션도 있어야 한다.

집계 함수는 MIN, MAX 같은 경우, 모든 입력값을 조사 해서 그 값을 구하는 것보다, 인덱스를 사용해서 그 최종 값을 구하는 것이 성능면에서 훨씬 낫다. 이런 경우, 정렬 연산자를 지정하고, 그 입력 값에 대한 인덱스가 있다면, 내부적으로 그 인덱스를 사용할 수 있다. 이렇게 구현되려면, 집계 결과 값이 정렬 자료의 첫번째여야 하는 조건이 만족되어야 한다. 달리 말하면:

SELECT agg(col) FROM tab;

윗 쿼리의 결과는 아래 쿼리의 결과와 같아야 한다.

SELECT col FROM tab ORDER BY col USING sortop LIMIT 1;

이 기능은 입력되는 모든 값이 null 이 아니다는 가정을 전제로 한다. 일반적으로 MIN 함수에서는 < 연산자가 사용되고, MAX 함수에서는 > 연산자를 지정한다. 여기서 기억해야 할 부분은 해당 자료형의 B-트리 인덱스 연산자 클래스 정의에 "작거나 같다" 또는 "크거나 같다"와 관련된 전략 번호에 대한 선언이 없다면, 이 최적화 기능은 사용할 수 없다는 것이다.

사용자 정의 집계 함수를 만들려면, 만드는 이가 그 집계 함수에서 사용하는 입력 매개 변수 자료형, 상태 값으로 사용하는 자료형, 최종 반환 자료형에 대해서 USAGE 접근 권한이 있어야 하고, 집계 함수에 정의하는 각종 내부용 함수들에 대해서는 EXECUTE 접근 권한이 있어야 한다.

매개 변수

CREATE AGGREGATE 명령에서 사용하는 옵션들은 위에서 언급한 순서와 상관 없이 나열 해도 된다.

참고

함수 이름을 설정 값으로 하는 옵션들에서는 그 함수 이름에 필요하다면, SFUNC = public.sum 같이 스키마 이름을 포함할 수도 있다. 여기서는 함수의 입력 인자를 지정할 필요는 없다. — 이 부분은 다른 옵션들을 지정함으로 해결 한다.

If an aggregate supports moving-aggregate mode, it will improve calculation efficiency when the aggregate is used as a window function for a window with moving frame start (that is, a frame start mode other than UNBOUNDED PRECEDING). Conceptually, the forward transition function adds input values to the aggregate's state when they enter the window frame from the bottom, and the inverse transition function removes them again when they leave the frame at the top. So, when values are removed, they are always removed in the same order they were added. Whenever the inverse transition function is invoked, it will thus receive the earliest added but not yet removed argument value(s). The inverse transition function can assume that at least one row will remain in the current state after it removes the oldest row. (When this would not be the case, the window function mechanism simply starts a fresh aggregation, rather than using the inverse transition function.) -- 집계 함수가 이동-집계 모드를 지원하고, 이동 프레임의 시작이있는 창 (즉, UNBOUNDED PRECEDING 이외의 프레임 시작 모드)로 집계 사용되는 경우에는 계산의 효율성이 향상된다. 개념적으로는 전방 전이 함수는 창 프레임 아래에서 들어갈 때 집약 상태로 입력 값을 추가하고, 반대로 전이 함수는 프레임을 위에서 빠질 때 그것을 제거한다. 따라서 값이 제거 될 때마다 추가되었을 때와 같은 순서로 제거된다. 따라서, 반대 전이 함수가 실행될 때는 언제든지 가장 먼저 추가되었지만 아직 제거되지 않은 인수 값을 받는다. 반대로 전이 함수는 가장 오래된 행을 제거한 후 현재 상태에 적어도 한 행이 남아있는 것을 전제로 한다. (그렇지 않을 경우, 윈도우 함수의 구조는 역 전환 함수를 사용하는 것이 아니라 단순히 새로운 집계를 시작한다.) -- 구글

이동-집계 모드에서 사용하는 전방위 전달 함수는 새 상태 값으로 NULL 값을 반환할 수 없다. 역방향 전달 함수가 NULL 값을 반환한다면, 그 역 집계 작업을 정상적으로 처리하지 못했음을 의미한다. 이 때는 현재 프래임의 시작 위치부터 집계 작업을 다시 한다. 이렇게 처리하면, 중간 과정에서 가끔 발생하는 집계 오류를 방지할 수 있다.

If no moving-aggregate implementation is supplied, the aggregate can still be used with moving frames, but PostgreSQL will recompute the whole aggregation whenever the start of the frame moves. Note that whether or not the aggregate supports moving-aggregate mode, PostgreSQL can handle a moving frame end without recalculation; this is done by continuing to add new values to the aggregate's state. It is assumed that the final function does not damage the aggregate's state value, so that the aggregation can be continued even after an aggregate result value has been obtained for one set of frame boundaries.

The syntax for ordered-set aggregates allows VARIADIC to be specified for both the last direct parameter and the last aggregated (WITHIN GROUP) parameter. However, the current implementation restricts use of VARIADIC in two ways. First, ordered-set aggregates can only use VARIADIC "any", not other variadic array types. Second, if the last direct parameter is VARIADIC "any", then there can be only one aggregated parameter and it must also be VARIADIC "any". (In the representation used in the system catalogs, these two parameters are merged into a single VARIADIC "any" item, since pg_proc cannot represent functions with more than one VARIADIC parameter.) If the aggregate is a hypothetical-set aggregate, the direct arguments that match the VARIADIC "any" parameter are the hypothetical ones; any preceding parameters represent additional direct arguments that are not constrained to match the aggregated arguments.

현재, 정렬된-집합 집계 기능은 이동-집계 모드에서는 사용할 수 없다. 그래서, 이 기능을 지정한 집계 함수는 창 함수로 사용할 수 없다.

부분 (병렬 처리 포함) 집계 작업은 정렬된-집합 집계 작업에서는 사용할 수 없다. 또한 병렬 처리가 가능한 집계 작업에서 DISTINCT나 ORDER BY 구문을 포함하는 집계 함수를 사용해서는 안된다.

예제

36.10절 참고.

호환성

CREATE AGGREGATE 명령은 PostgreSQL 확장 기능이다. 표준 SQL에는 사용자 정의 집계 함수를 위한 기능을 제공하지 않는다.

관련 항목