CREATE AGGREGATE

제목

CREATE AGGREGATE -- 새 집계 함수 정의

요약

CREATE AGGREGATE 이름 ( [ 인자모드 ] [ 인자이름 ] 입력값_자료형 [ , ... ] ) (
    SFUNC = 전달_함수,
    STYPE = 상태값_자료형
    [ , SSPACE = 상태값_크기 ]
    [ , FINALFUNC = 마무리_함수 ]
    [ , FINALFUNC_EXTRA ]
    [ , COMBINEFUNC = combinefunc ]
    [ , SERIALFUNC = serialfunc ]
    [ , DESERIALFUNC = deserialfunc ]
    [ , INITCOND = 초기_상태값 ]
    [ , MSFUNC = msfunc ]
    [ , MINVFUNC = minvfunc ]
    [ , MSTYPE = mstate_data_type ]
    [ , MSSPACE = mstate_data_size ]
    [ , MFINALFUNC = mffunc ]
    [ , MFINALFUNC_EXTRA ]
    [ , MINITCOND = minitial_condition ]
    [ , SORTOP = 정렬_연산자 ]
    [ , PARALLEL = { SAFE | RESTRICTED | UNSAFE } ]
)

CREATE AGGREGATE 이름 ( [ [ 인자모드 ] [ 인자이름 ] 입력값_자료형 [ , ... ] ]
                        ORDER BY [ 인자모드 ] [ 인자이름 ] 입력값_자료형 [ , ... ] ) (
    SFUNC = 전달_함수,
    STYPE = 상태값_자료형
    [ , SSPACE = 상태값_크기 ]
    [ , FINALFUNC = 마무리_함수 ]
    [ , FINALFUNC_EXTRA ]
    [ , INITCOND = 초기_상태값 ]
    [ , PARALLEL = { SAFE | RESTRICTED | UNSAFE } ]
    [ , HYPOTHETICAL ]
)

or the old syntax

CREATE AGGREGATE 이름 (
    BASETYPE = base_type,
    SFUNC = 전달_함수,
    STYPE = 상태값_자료형
    [ , SSPACE = 상태값_크기 ]
    [ , FINALFUNC = 마무리_함수 ]
    [ , FINALFUNC_EXTRA ]
    [ , COMBINEFUNC = combinefunc ]
    [ , SERIALFUNC = serialfunc ]
    [ , DESERIALFUNC = deserialfunc ]
    [ , INITCOND = 초기_상태값 ]
    [ , MSFUNC = msfunc ]
    [ , MINVFUNC = minvfunc ]
    [ , MSTYPE = mstate_data_type ]
    [ , MSSPACE = mstate_data_size ]
    [ , MFINALFUNC = mffunc ]
    [ , MFINALFUNC_EXTRA ]
    [ , MINITCOND = minitial_condition ]
    [ , SORTOP = 정렬_연산자 ]
)

설명

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 접근 권한이 있어야 한다.

매개 변수

이름

새로 만드는 집계 함수 이름(스키마 이름도 함께 지정할 수 있음).

인자모드

사용할 수 있는 값은 IN 또는 VARIADIC. (집계 함수에서는 OUT 값은 지원하지 않는다.) 지정하지 않으면, IN으로 간주함. 또한 마지막 입력용으로 가변 인자 처리를 위해 VARIADIC을 쓸 수 있다.

인자이름

인자 이름. 이 지정은 문서화에만 유용하다. 집계 작업에서는 사용되지 않는다. 생략하면 그 인자의 이름은 없다.

입력값_자료형

집계 함수가 집계 작업에 사용할 입력 값의 자료형. 이 인자가 없는 경우는 입력 인자를 지정하는 자리에, * 문자를 지정한다. (이런 집계 함수 가운데 대표적인 것이 count(*) 함수다.)

base_type

이것은 CREATE AGGREGATE 명령의 옛날 구문에서 입력 값에 대한 자료형을 지정할 때 사용했다. 옛날 구문에서는 입력 값에 대해서 하나의 자료형만 사용할 수 있었다. 또한 입력 인자가 없는 경우는 * 문자를 사용하지 않고 "ANY"를 사용한다. 옛 구문에서는 정렬된-집합 집계 함수를 만들 수 없다.

전달_함수

The name of the state transition function to be called for each input row. For a normal N-argument aggregate function, the 전달_함수 must take N+1 arguments, the first being of type 상태값_자료형 and the rest matching the declared input data type(s) of the aggregate. The function must return a value of type 상태값_자료형. This function takes the current state value and the current input data value(s), and returns the next state value.

For ordered-set (including hypothetical-set) aggregates, the state transition function receives only the current state value and the aggregated arguments, not the direct arguments. Otherwise it is the same.

상태값_자료형

집계 함수 내부에서 사용할 상태 값의 자료형.

상태값_크기

The approximate average size (in bytes) of the aggregate's state value. If this parameter is omitted or is zero, a default estimate is used based on the 상태값_자료형. The planner uses this value to estimate the memory required for a grouped aggregate query. The planner will consider using hash aggregation for such a query only if the hash table is estimated to fit in work_mem; therefore, large values of this parameter discourage use of hash aggregation.

ffunc

The name of the final function called to compute the aggregate's result after all input rows have been traversed. For a normal aggregate, this function must take a single argument of type 상태값_자료형. The return data type of the aggregate is defined as the return type of this function. If ffunc is not specified, then the ending state value is used as the aggregate's result, and the return type is 상태값_자료형.

For ordered-set (including hypothetical-set) aggregates, the final function receives not only the final state value, but also the values of all the direct arguments.

If FINALFUNC_EXTRA is specified, then in addition to the final state value and any direct arguments, the final function receives extra NULL values corresponding to the aggregate's regular (aggregated) arguments. This is mainly useful to allow correct resolution of the aggregate result type when a polymorphic aggregate is being defined.

combinefunc

The combinefunc function may optionally be specified to allow the aggregate function to support partial aggregation. If provided, the combinefunc must combine two 상태값_자료형 values, each containing the result of aggregation over some subset of the input values, to produce a new 상태값_자료형 that represents the result of aggregating over both sets of inputs. This function can be thought of as an 전달_함수, where instead of acting upon an individual input row and adding it to the running aggregate state, it adds another aggregate state to the running state.

The combinefunc must be declared as taking two arguments of the 상태값_자료형 and returning a value of the 상태값_자료형. Optionally this function may be "strict". In this case the function will not be called when either of the input states are null; the other state will be taken as the correct result.

For aggregate functions whose 상태값_자료형 is internal, the combinefunc must not be strict. In this case the combinefunc must ensure that null states are handled correctly and that the state being returned is properly stored in the aggregate memory context.

serialfunc

An aggregate function whose 상태값_자료형 is internal can participate in parallel aggregation only if it has a serialfunc function, which must serialize the aggregate state into a bytea value for transmission to another process. This function must take a single argument of type internal and return type bytea. A corresponding deserialfunc is also required.

deserialfunc

Deserialize a previously serialized aggregate state back into 상태값_자료형. This function must take two arguments of types bytea and internal, and produce a result of type internal. (Note: the second, internal argument is unused, but is required for type safety reasons.)

초기_상태값

The initial setting for the state value. This must be a string constant in the form accepted for the data type 상태값_자료형. If not specified, the state value starts out null.

msfunc

The name of the forward state transition function to be called for each input row in moving-aggregate mode. This is exactly like the regular transition function, except that its first argument and result are of type mstate_data_type, which might be different from 상태값_자료형.

minvfunc

The name of the inverse state transition function to be used in moving-aggregate mode. This function has the same argument and result types as msfunc, but it is used to remove a value from the current aggregate state, rather than add a value to it. The inverse transition function must have the same strictness attribute as the forward state transition function.

mstate_data_type

The data type for the aggregate's state value, when using moving-aggregate mode.

mstate_data_size

The approximate average size (in bytes) of the aggregate's state value, when using moving-aggregate mode. This works the same as 상태값_크기.

mffunc

The name of the final function called to compute the aggregate's result after all input rows have been traversed, when using moving-aggregate mode. This works the same as ffunc, except that its first argument's type is mstate_data_type and extra dummy arguments are specified by writing MFINALFUNC_EXTRA. The aggregate result type determined by mffunc or mstate_data_type must match that determined by the aggregate's regular implementation.

minitial_condition

The initial setting for the state value, when using moving-aggregate mode. This works the same as 초기_상태값.

정렬_연산자

The associated sort operator for a MIN- or MAX-like aggregate. This is just an operator name (possibly schema-qualified). The operator is assumed to have the same input data types as the aggregate (which must be a single-argument normal aggregate).

PARALLEL

The meanings of PARALLEL SAFE, PARALLEL RESTRICTED, and PARALLEL UNSAFE are the same as for CREATE FUNCTION. An aggregate will not be considered for parallelization if it is marked PARALLEL UNSAFE (which is the default!) or PARALLEL RESTRICTED. Note that the parallel-safety markings of the aggregate's support functions are not consulted by the planner, only the marking of the aggregate itself.

HYPOTHETICAL

For ordered-set aggregates only, this flag specifies that the aggregate arguments are to be processed according to the requirements for hypothetical-set aggregates: that is, the last few direct arguments must match the data types of the aggregated (WITHIN GROUP) arguments. The HYPOTHETICAL flag has no effect on run-time behavior, only on parse-time resolution of the data types and collations of the aggregate's arguments.

The parameters of CREATE AGGREGATE can be written in any order, not just the order illustrated above.

참고

함수 이름을 설정 값으로 하는 옵션들에서는 그 함수 이름에 필요하다면, 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.)

The forward transition function for moving-aggregate mode is not allowed to return NULL as the new state value. If the inverse transition function returns NULL, this is taken as an indication that the inverse function cannot reverse the state calculation for this particular input, and so the aggregate calculation will be redone from scratch for the current frame starting position. This convention allows moving-aggregate mode to be used in situations where there are some infrequent cases that are impractical to reverse out of the running state value.

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.

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

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

예제

36.10절 참고.

호환성

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

관련 항목

ALTER AGGREGATE, DROP AGGREGATE