Summary

여러 그룹핑 조합을 한 번에 집계해야 할 때, 개별 GROUP BY를 조합 개수만큼 반복하지 않고 GROUPING SETS로 원천 테이블을 단 1회만 스캔해 처리할 수 있다.

CUBE는 가능한 모든 조합을 생성하므로, 식별자 컬럼이 반드시 포함돼야 하는 집계에는 불필요한 조합까지 만들어 낭비가 생긴다.

GROUPING() 플래그를 필터 조건으로 재사용하면, 집계 결과를 레벨별 CTE로 나누고 COALESCE로 계층적 폴백 매칭을 구현할 수 있다.

스캔·정렬 비용은 조합 개수 $k$ 배에서 상수로 줄어들지만, 해시 집계 연산량은 줄지 않고 메모리 사용량은 오히려 늘어난다.

들어가며

고객별 매출을 지역별로도, 카테고리별로도, 둘 다로도 봐야 한다. 기준이 4개면 GROUP BY를 4번 돌려야 하는가?

이 글은 그 반복을 한 번의 스캔으로 줄이는 GROUPING SETS를 다룬다. 온라인 쇼핑몰의 주문 테이블 orders(customer_id, region, category, amount)를 예로 쓰며, 필요한 집계 레벨은 다음 4가지다.

Level A: (customer_id, region, category) — 고객 × 지역 × 카테고리별 매출
Level B: (customer_id, region) — 고객 × 지역별 매출
Level C: (customer_id, category) — 고객 × 카테고리별 매출
Level D: (customer_id) — 고객별 총매출

GROUP BY 와 GROUPING SETS

여러 번의 GROUP BY

가장 직관적인 방법은 기준마다 CTE를 하나씩 만들어 각각 GROUP BY를 거는 것이다.

레벨별로 반복되는 GROUP BY

WITH sales_k1 AS (
    SELECT customer_id, region, category, SUM(amount) AS total_amount
    FROM orders GROUP BY customer_id, region, category
),
sales_k2 AS (
    SELECT customer_id, region, SUM(amount) AS total_amount
    FROM orders GROUP BY customer_id, region
),
sales_k3 AS (
    SELECT customer_id, category, SUM(amount) AS total_amount
    FROM orders GROUP BY customer_id, category
),
sales_k4 AS (
    SELECT customer_id, SUM(amount) AS total_amount
    FROM orders GROUP BY customer_id
)

문제는 동일한 원천 테이블(orders)을 조합 개수(4번)만큼 반복해서 스캔하고, 그만큼 정렬(Sort) 연산도 반복된다는 점이다. 데이터가 대용량일수록 이 비용이 그대로 곱해진다. 4개의 CTE는 각각 다른 GROUP BY를 가지므로 스캔은 4번 발생한다.

GROUP BY GROUPING SETS

GROUPING SETS를 쓰면 원천 테이블을 1회만 스캔하면서 필요한 조합을 동시에 집계할 수 있다.

GROUPING SETS로 4개 레벨 동시 집계

WITH sales_grouped AS (
    SELECT customer_id,
           region,
           category,
           GROUPING(region) AS g_region,
           GROUPING(category) AS g_category,
           SUM(amount) AS total_amount
    FROM orders
    GROUP BY GROUPING SETS (
        (customer_id, region, category), -- Level A (g_region=0, g_category=0)
        (customer_id, region),           -- Level B (g_region=0, g_category=1)
        (customer_id, category),         -- Level C (g_region=1, g_category=0)
        (customer_id)                    -- Level D (g_region=1, g_category=1)
    )
)

GROUPING(컬럼)은 해당 컬럼이 이번 그룹핑 세트에서 집계됐으면 0, 빠졌으면(즉 상위 레벨로 롤업됐으면) 1을 반환한다. 이 값으로 결과 행이 어느 레벨(A~D)에 해당하는지 구분할 수 있다.

비교

성능 및 장단점

원천 행 수를 $R$ , 그룹핑 조합 개수를 $k$ , 각 조합의 결과 그룹 수를 $G_{i}$ 라 하자.

항목	개별 GROUP BY (Multiple CTEs)	GROUP BY GROUPING SETS
스캔 횟수	조합 개수만큼 반복 ( $k$ 회)	단 1회 (Single Pass)
읽는 총 행 수	$O (k \cdot R)$	$O (R)$
정렬 비용	$O (k \cdot R lo g R)$	$O (R lo g R)$
해시 집계 연산	$O (k \cdot R)$	$O (k \cdot R)$ (동일)
메모리	$O (max G_{i})$ — 한 번에 1개	$O (\sum G_{i})$ — 동시에 $k$ 개
가독성	중복 CTE가 여러 개 생김	하나의 집계 CTE로 통합

GROUPING SETS가 아끼는 것은 반복 스캔과 반복 정렬이며, 해시 갱신 자체는 줄지 않는다.

장점

I/O와 정렬: 스캔이 $k$ 회에서 1회로 줄고, 정렬 비용도 같은 비율로 줄어든다.
스냅샷 정합성: 단일 집계라 레벨 간 합계가 어긋나지 않는다. 기존 GROUP BY는 스캔이 $k$ 번이라 그 사이 유입된 행 때문에 “지역별 합계 ≠ 지역·카테고리별 합계의 합”이 될 수 있다.

단점

해시 갱신 연산: 각 튜플을 $k$ 개 조합에 반영하는 CPU 작업은 어느 쪽이든 $k$ 번 일어나므로 줄지 않는다.
메모리: $k$ 개의 집계 상태를 동시에 들고 있어야 한다.

조합별 스캔 횟수

축이 늘면 조합 수는 $2^{n}$ 으로 증가한다. GROUP BY는 조합 1개가 추가될 때마다 풀스캔 1회가 함께 추가되지만, GROUPING SETS는 스캔이 여전히 1회다.

축 개수 $n$	조합 수 $k$	`GROUP BY` 스캔	`GROUPING SETS` 스캔
2	4	4회	1회
3	8	8회	1회
4	16	16회	1회

$R = 1, 000 만$ 행에서 조합이 4개면 GROUP BY는 4천만 행을 읽는다. GROUPING SETS는 1천만 행을 읽는다.

주의점

GROUPING SETS를 쓸 때 결과가 틀어지거나 성능이 되레 나빠지는 지점은 두 군데다.

레벨 구분에 IS NULL을 쓰지 않는다

원본 region에 실제 NULL이 있으면 “롤업돼서 NULL”인 행과 구분되지 않아 결과가 틀어진다.
반드시 GROUPING() 플래그로 판별한다.

메모리 한계를 확인한다

$k$ 개의 해시 테이블이 동시에 올라가므로, 조합이 많고 distinct 값이 크면 work_mem을 초과해 디스크 스필이 발생한다.
EXPLAIN (ANALYZE, BUFFERS)의 Disk Usage로 확인한다.

GROUPING SETS vs CUBE

CUBE(customer_id, region, category)는 세 컬럼으로 만들 수 있는 모든 조합( $2^{3} = 8$ 개)을 자동으로 생성한다.

CUBE가 만드는 8개 조합

[CUBE가 만드는 8개 조합]
1. (customer_id, region, category)  -> 필요 (Level A)
2. (customer_id, region)            -> 필요 (Level B)
3. (customer_id, category)          -> 필요 (Level C)
4. (customer_id)                    -> 필요 (Level D)
--------------------------------------------------------
5. (region, category)               -> 불필요, customer_id 누락
6. (region)                         -> 불필요, customer_id 누락
7. (category)                       -> 불필요, customer_id 누락
8. ()  전체 총합                    -> 불필요, customer_id 누락

5~8번처럼 고객 식별자(customer_id)가 빠진 조합은 “고객별 매출”이라는 목적에서 의미가 없다. CUBE를 쓰면 이런 불필요한 조합까지 계산 비용을 지불하게 되므로, 필요한 조합만 명시할 수 있는 GROUPING SETS가 더 적합하다.

불필요 연산 차단: 식별자가 빠진 조합(5~8번)을 애초에 제외해 리소스를 아낀다.
핀포인트 집계: 실제 필요한 리포트 레벨만 골라 집계한다.

위 예시에서 CUBE는 8개 중 4개가 불필요하므로 집계 연산의 절반이 버려진다. 축이 늘어도 식별자가 빠진 조합의 비율은 $\frac{2 ^{n} - 2 ^{n - 1}}{2 ^{n}} = \frac{1}{2}$ 로 항상 절반이다.

계층별 우선순위 매칭

주문 행마다 가장 구체적인 레벨의 집계값을 붙이고, 그 레벨에 값이 없으면 상위 레벨로 폴백(fallback)한다. 매칭 우선순위는 Level A → B → C → D다.

GROUPING() 플래그를 필터 조건으로 재사용해 sales_grouped를 레벨별 CTE로 쪼개고, COALESCE로 우선순위를 매긴다.

레벨별 CTE 분리 + COALESCE 우선순위 매칭

WITH sales_grouped AS (
    SELECT customer_id,
           region,
           category,
           GROUPING(region) AS g_region,
           GROUPING(category) AS g_category,
           SUM(amount) AS total_amount
    FROM orders
    GROUP BY GROUPING SETS (
        (customer_id, region, category), -- Level A (g_region=0, g_category=0)
        (customer_id, region),           -- Level B (g_region=0, g_category=1)
        (customer_id, category),         -- Level C (g_region=1, g_category=0)
        (customer_id)                    -- Level D (g_region=1, g_category=1)
    )
),
-- 계층별 필터링: GROUPING 플래그로 레벨 A~D를 각각 CTE로 분리
k1 AS (SELECT * FROM sales_grouped WHERE g_region = 0 AND g_category = 0), -- Level A: 지역+카테고리 일치
k2 AS (SELECT * FROM sales_grouped WHERE g_region = 0 AND g_category = 1), -- Level B: 지역만 일치
k3 AS (SELECT * FROM sales_grouped WHERE g_region = 1 AND g_category = 0), -- Level C: 카테고리만 일치
k4 AS (SELECT * FROM sales_grouped WHERE g_region = 1 AND g_category = 1)  -- Level D: 고객 전체
SELECT o.customer_id,
       o.region,
       o.category,
       o.amount,
       COALESCE(k1.total_amount, k2.total_amount, k3.total_amount, k4.total_amount) AS matched_total_amount,
       CASE
           WHEN k1.total_amount IS NOT NULL THEN 'A'
           WHEN k2.total_amount IS NOT NULL THEN 'B'
           WHEN k3.total_amount IS NOT NULL THEN 'C'
           ELSE 'D'
       END AS matched_level
FROM orders o
LEFT JOIN k1 ON o.customer_id = k1.customer_id AND o.region = k1.region AND o.category = k1.category
LEFT JOIN k2 ON o.customer_id = k2.customer_id AND o.region = k2.region
LEFT JOIN k3 ON o.customer_id = k3.customer_id AND o.category = k3.category
LEFT JOIN k4 ON o.customer_id = k4.customer_id;

CTE별 기능

CTE	필터	역할	JOIN 키
`sales_grouped`	—	4개 레벨을 단일 스캔으로 집계	—
`k1`	`g_region=0, g_category=0`	Level A — 지역·카테고리 모두 일치 (최우선)	고객 + 지역 + 카테고리
`k2`	`g_region=0, g_category=1`	Level B — 지역만 일치	고객 + 지역
`k3`	`g_region=1, g_category=0`	Level C — 카테고리만 일치	고객 + 카테고리
`k4`	`g_region=1, g_category=1`	Level D — 고객 전체 (최종 폴백)	고객

g_region/g_category 조합이 4가지뿐이라 필터 조건 하나로 정확히 4개 CTE로 나뉜다. 이미 계산된 값을 재사용하므로 레벨마다 GROUP BY를 다시 돌 필요가 없다.

COALESCE는 앞쪽 인자부터 NULL이 아닌 값을 채택하므로, 인자 순서가 곧 폴백 우선순위가 된다. matched_level은 어느 레벨에서 매칭됐는지를 남겨 이후 검증·디버깅에 쓴다.

마치며

여러 그룹핑 조합이 필요하면 GROUPING SETS로 단일 스캔 처리한다. 조합 축이 늘수록 이득이 커진다.
식별자가 반드시 포함돼야 하는 집계에서는 CUBE 대신 GROUPING SETS로 필요한 조합만 명시한다.
레벨 구분은 IS NULL이 아니라 GROUPING()으로 한다. 원본 NULL과 롤업 NULL이 섞이면 결과가 틀어진다.

참고사이트

junhkang.com - GROUPING SETS와 CUBE

HAEJUN RECORDS

GROUPING SETS와 CUBE

들어가며

GROUP BY 와 GROUPING SETS

여러 번의 GROUP BY

GROUP BY GROUPING SETS

비교

성능 및 장단점

조합별 스캔 횟수

주의점

GROUPING SETS vs CUBE

계층별 우선순위 매칭

마치며

Table of Contents

Graph View