PostgreSQL 8.3.3문서
Prev Fast Backward Chapter 33. SQL 확장 Fast Forward Next

33.10. 사용자 정의의 집계

PostgreSQL에 있어서의 집계 함수는, 상태치와 상태 전환 함수로 표현되고 있습니다. 즉, 1개의 집계는, 입력행이 처리될 때 변경되는 상태값을 사용해 연산됩니다. 새로운 집계 함수를 정의하기 위해서는, 상태값의 데이터형, 상태의 초기값, 그리고 상태 전환 함수를 선택합니다. 상태 전환 함수는 단순한 보통 함수로, 집계 이외의 문맥에서도 사용할 수 있습니다. 요구되는 집계의 결과가 상태값을 유지해야 하는 데이터와 다른 경우는, 최종 함수도 지정할 수가 있습니다.

따라서, 집계의 사용자로 보이는 인수와 결과 데이터형 이외에도, 두 인수와 결과형과 모두 다른 내부 상태값의 데이터형이 있습니다.

만약 최종 함수를 사용하지 않는 집계를 정의했다고 하면, 각각의 행의 열값의 실행 함수로 계산된 결과를 집계 할 수 있습니다. sum는 그러한 집계의 일례입니다. sum는 0으로부터 시작되어, 항상 현재의 행의 값을 그 시점까지의 총 실행값에 추가합니다. 예를 들면, 만약 sum집계를 복소수의 데이터형으로 동작하게 하고 싶으면, 그 데이터형의 가산 함수만이 필요하게 됩니다. 집계 정의는 이하와 같이 됩니다. 

CREATE AGGREGATE sum (complex)
(
    sfunc = complex_add,
    stype = complex,
    initcond = '(0,0)'
);

SELECT sum(a) FROM test_complex;

   sum
-----------
 (34,53.9)

(함수의 오버라이드(override) 기능에 의존하고 있는 것에 주의해 주세요. sum라는 이름의 집계 함수는 복수 존재하지만, PostgreSQL는 열의 형태complex에 적용할 수 있는 sum 함수를 찾아낼 수 있습니다. )

상기의 sum의 정의는, 만약 비NULL의 입력값이 없으면 0(초기 상태)을 돌려줍니다. 그 경우, NULL를 돌려주고 싶은 것이 아닐까 생각하고, 표준 SQL에서는 sum가 그처럼 동작하는 것을 기대하고 있습니다. 그렇게 하기 위해서 단지 initcond구를 생략 하면, 초기 상태가 NULL가 됩니다. 통상 sfunc가 NULL 상태의 입력을 체크해야만 한다는 것을 의미합니다만, sum나, 그 외 max,min와 같이 단순한 집계에 있어서는, 최초의 비NULL 입력값을 상태 변수에 삽입해, 2번째의 비NULL 입력값으로 상태 전환 함수를 적용시키기 시작하면 충분합니다. PostgreSQL는, 만약 초기 상태가 NULL로 상태 전환 함수가"엄밀(strict)"과 설정되어 있는 경우, 자동적으로 그것을 실행합니다(즉 NULL 입력으로는 불려 가지 않게 됩니다).

또다른 "엄밀(strict)"인 상태 전환 함수의 디폴트 동작은 NULL 입력값이 나타나기 전 상태값이 변함없이에 유지되는 것이 있습니다. 따라서, NULL값은 무시됩니다. 만약 NULL 입력에 대해 다른 동작이 필요한 경우는, 상태 전환 함수를 strict로 정의하지 않고, NULL 입력의 검사를 실시하도록 코딩 해, 필요시 사용하면 좋습니다.

avg(평균값 계산)는 좀 더 복잡한 집계의 일례입니다. 거기에는 2개의 변동하는 상태가 필요하게 됩니다. 입력의 합계와 입력수의 카운트입니다. 최종 결과는 이 두 값을 나눗셈하는 것에 의해 얻을 수 있습니다. 평균값 계산은 전형적으로 2 요소의 배열을 상태 전환값으로 사용해 구현됩니다. 예를 들면, avg(float8)의 내부적인 구현은 이하와 같이 되어 있습니다. 

CREATE AGGREGATE avg (float8)
(
    sfunc = float8_accum,
    stype = float8[],
    finalfunc = float8_avg,
    initcond = '{0,0}'
);

집계 함수는 다양한 상태 전환 함수나 최종 함수를 사용할 수 있습니다. 이것에 의해, 같은 함수를 사용해 복수의 집계를 구현할 수 있습니다. Section 33.2.5에 다양한 함수의 설명이 있습니다. 좀 더 세세하게 말하면, 집계 함수 자신은, 단일의 집계 정의로 복수의 입력 데이터형을 취급할 수가 있듯이, 다양한 입력형과 상태형을 지정할 수 있습니다. 이하에 다양 집계의 예를 나타냅니다. 

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

여기서, 집계를 실시하는 호출의 실제 상태는, 그 요소가 되는 실제의 입력형을 가진 배열형입니다.

이하에 서로 다른 두 가지의 실제 데이터형을 인수로서 사용한 출력예를 나타냅니다. 

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,spclocation,spcacl}
(1 row)

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

   attrelid    |   array_accum   
---------------+-----------------
 pg_tablespace | {19,26,25,1034}
(1 row)

C언어로 작성된 함수는, 예를 들면 이하와 같이 함수 호출"문맥"으로서AggState노드로부터 건네받았는지 어떠했는지를 확인하는 것으로, 집계의 전환 함수로서 불려 갔는지 최종 함수로서 불려 갔는지를 검출할 수가 있습니다. 

        if (fcinfo->context && IsA(fcinfo->context, AggState))

이 점검을 실시하는 이유의 하나는, 첫번째 입력은 임시 전환 값이어야 하며, 새로운 복사본을 할당하기보다는 안전하게 수정되어야 한다는 것입니다. (이것은 함수내에서 참조 인도의 입력을 안전하게 변경할 수 있는 유일한 경우입니다). 예는, int8inc()를 참조해 주세요.

더 자세한 정보는, CREATE AGGREGATE 명령을 참조해 주세요.

Prev Home Next
C언어 함수 Up 사용자 정의의 형태