집계 함수

집계 함수 (Aggregate Functions)

"여러 행을 묶어서 하나의 결과를 내고 싶다"면 집계 함수가 정답이에요. 총합, 평균, 개수, 최대·최소 같은 기본 집계부터 통계·순위 관련 집계까지, PostgreSQL이 제공하는 내장 집계 함수들을 시그니처와 함께 정리해 볼게요.

출처: 공식문서

집계 함수란

집계 함수는 입력 값 집합에서 하나의 결과를 계산해요. 일반 목적 집계(Table 9.62), 통계 집계(Table 9.63), within-group ordered-set 집계(Table 9.64), within-group hypothetical-set 집계(Table 9.65), 그리고 집계와 밀접한 그룹핑 연산(Table 9.66)이 있어요.

여기서 유용한 개념 하나를 짚어둘게요. Partial Mode를 지원하는 집계 함수는 병렬 집계 같은 최적화에 참여할 수 있어요. 또 아래 모든 집계는 선택적으로 ORDER BY 절을 받을 수 있는데, 출력이 정렬에 영향을 받는 집계에만 그 절이 명시돼 있어요.

일반 목적 집계 함수

반환 값과 관련된 주의점

count 를 제외하면 이 함수들은 선택된 행이 없을 때 널을 반환해요. 특히 행이 없는 sum 은 기대와 달리 0이 아니라 널이고, array_agg 는 입력 행이 없으면 빈 배열이 아니라 널이에요. 필요하면 coalesce 로 널을 0이나 빈 배열로 바꿀 수 있어요.

array_agg, json_agg, jsonb_agg, json_agg_strict, jsonb_agg_strict, json_object_agg, jsonb_object_agg, json_object_agg_strict, jsonb_object_agg_strict, json_object_agg_unique, jsonb_object_agg_unique, json_object_agg_unique_strict, jsonb_object_agg_unique_strict, string_agg, xmlagg 처럼 순서에 따라 결과가 달라지는 집계는 입력 값의 순서에 따라 의미 있는 결과 값이 정해져요. 이 순서는 기본적으로 불특정이지만, 집계 호출 안에 ORDER BY 절을 쓰면 제어할 수 있어요. 정렬된 서브쿼리에서 입력 값을 공급하는 방법도 보통 동작해요. 예:

SELECT xmlagg(x) FROM (SELECT x FROM test ORDER BY y DESC) AS tab;

주의할 점은, 바깥 쿼리 레벨에 조인 같은 추가 처리가 있으면 서브쿼리 출력이 집계 전에 재정렬될 수 있어 이 방법이 실패할 수 있어요.

참고: bool 집계

불리언 집계 bool_and, bool_or 는 SQL 표준 집계 every 와 any/some 에 대응돼요. PostgreSQL은 every 는 지원하지만 any/some 은 지원하지 않는데, 표준 문법에 애매함이 내장돼 있기 때문이에요. b1 = ANY((SELECT b2 FROM t2 ...)) 같은 표현에서 ANY 는 서브쿼리 도입자로도, 집계 함수로도 해석될 수 있거든요.

참고: count 성능

다른 DBMS에 익숙한 사용자는 테이블 전체에 count 를 적용할 때 성능이 실망스러울 수 있어요. SELECT count(*) FROM sometable; 같은 쿼리는 테이블 크기에 비례하는 작업이 필요해요. PostgreSQL은 테이블 전체나 테이블의 모든 행을 포함하는 인덱스 전체를 스캔해야 하거든요.

통계 집계 함수

통계 분석에 주로 쓰는 집계예요. 아래에서 numeric_type 을 받는 함수는 smallint, integer, bigint, numeric, real, double precision 전부에 사용 가능해요. 설명에서 N 은 모든 입력 표현식이 널이 아닌 입력 행 수를 뜻해요. 모든 경우에 N 이 0이라 계산이 무의미하면 널을 반환해요.

Ordered-Set 집계 함수

이 집계들은 ordered-set 집계 문법을 쓰며, 가끔 "역분포(inverse distribution)" 함수라고도 불려요. 집계 입력은 ORDER BY 로 도입되고, 집계되지 않는 직접 인자도 받을 수 있는데 그건 한 번만 계산돼요. 이 함수들은 모두 집계 입력의 널 값을 무시해요. fraction 매개변수를 받는 함수는 그 값이 0과 1 사이여야 하고, 범위 밖이면 오류를 내요. 다만 널 fraction 값은 그냥 널 결과를 만들어요.

Hypothetical-Set 집계 함수

Table 9.65의 각 "hypothetical-set" 집계는 같은 이름의 윈도우 함수와 연관돼요. 각 경우 집계의 결과는, args 로 만든 "가상(hypothetical)" 행이 sorted_args 가 나타내는 정렬된 행 그룹에 추가됐다면 연관 윈도우 함수가 반환했을 값이에요. 각 함수의 args 직접 인자 목록은 sorted_args 의 집계 인자 수·타입과 일치해야 해요. 대부분의 내장 집계와 달리 이 함수들은 strict하지 않아서(널을 포함한 입력 행을 버리지 않아서) 널 값은 ORDER BY 절이 지정한 규칙대로 정렬돼요.

그룹핑 연산

GROUPING ( group_by_expression(s) ) → integer — 현재 그룹핑 집합에 포함되지 않은 GROUP BY 표현식을 나타내는 비트 마스크를 반환해요. 비트는 가장 오른쪽 인자가 최하위 비트에 대응하고, 각 비트는 해당 표현식이 현재 결과 행을 만든 그룹핑 집합의 그룹핑 기준에 포함되면 0, 아니면 1이에요.

그룹핑 연산은 그룹핑 집합(grouping sets)과 함께 결과 행을 구분하는 데 쓰여요. GROUPING 함수의 인자는 실제로 평가되지는 않지만, 연관 쿼리 레벨의 GROUP BY 절에 주어진 표현식과 정확히 일치해야 해요. 예를 들어 볼게요.

SELECT * FROM items_sold;

 make  | model | sales
-------+-------+-------
 Foo   | GT    |  10
 Foo   | Tour  |  20
 Bar   | City  |  15
 Bar   | Sport |  5
(4 rows)

SELECT make, model, GROUPING(make,model), sum(sales)
FROM items_sold GROUP BY ROLLUP(make,model);

 make  | model | grouping | sum
-------+-------+----------+-----
 Foo   | GT    |        0 | 10
 Foo   | Tour  |        0 | 20
 Bar   | City  |        0 | 15
 Bar   | Sport |        0 |  5
 Foo   |       |        1 | 30
 Bar   |       |        1 | 20
       |       |        3 | 50
(7 rows)

여기서 처음 네 행의 grouping 값 0 은 두 그룹핑 컬럼 모두로 정상 그룹핑됐다는 뜻이고, 값 1 은 마지막 두 행에서 model 로 그룹핑되지 않았음을, 값 3 은 마지막 행에서 make 와 model 모두로 그룹핑되지 않았음(그래서 전체 입력 행에 대한 집계)을 나타내요.

더 알아보기 (Learn more)

• 윈도우 함수
• 집계 표현식 문법
• 그룹핑 집합