설치·접속: PostgreSQL 설치와 접속
부제: 카테고리별·월별 매출에 소계와 총계, 환불액까지 붙인 리포트를 UNION ALL 없이 단일 SELECT로 뽑을 때
문제 상황
카테고리별 매출, 카테고리+월별 매출, 그리고 전사 총계를 한 표에 뿌려야 한다. 흔한 방식은 GROUP BY가 다른 쿼리를 3~4개 만들어 UNION ALL로 붙이거나, 앱에서 각 레벨을 다시 집계하는 것이다. 여기에 "완료 매출과 환불액을 나란히" 요구가 붙으면 쿼리는 더 늘어난다. 이걸 ROLLUP + FILTER + GROUPING() 조합으로 단일 SELECT에 담는다.
검증용 스크래치 테이블은 주문 200건에 카테고리(electronics/apparel/grocery), 판매월(1~4월), 상태(paid/refunded/canceled), 금액을 붙여 만들었다.
1단계 — ROLLUP으로 소계와 총계를 한 번에
GROUP BY ROLLUP(category, mon)은 (category, mon) → (category) → () 세 grouping set을 한 번에 계산한다. 즉 카테고리+월 상세, 카테고리 소계, 전체 총계가 한 결과에 쌓인다.
SELECT category, extract(month FROM sold_at)::int AS mon, sum(amount) AS revenue
FROM sales
GROUP BY ROLLUP(category, extract(month FROM sold_at));소계 행에서는 mon이 NULL, 총계 행에서는 category까지 NULL로 나온다. 문제는 이 NULL이 "값이 없는 행"인지 "소계 행"인지 구분이 안 된다는 점이다.
2단계 — GROUPING()으로 소계 행을 라벨링
GROUPING(col)은 그 컬럼이 이번 grouping set에서 집계로 뭉개졌으면 1, 살아있으면 0을 준다. 이걸로 NULL이 "진짜 데이터의 NULL"인지 "소계라서 비운 칸"인지 확실히 구분해 라벨을 붙인다.
3단계 — FILTER로 환불액을 같은 줄에
sum(amount) FILTER (WHERE status='refunded')는 환불 건만 골라 합산한다. CASE WHEN ... THEN amount ELSE 0 END를 감싸는 대신 의도가 그대로 드러난다. 세 조각을 합친 최종 리포트:
SELECT
CASE WHEN GROUPING(category)=1 THEN 'ALL (total)' ELSE category END AS category,
CASE WHEN GROUPING(mon)=1 THEN 'subtotal' ELSE mon::text END AS mon,
sum(amount) AS revenue,
sum(amount) FILTER (WHERE status='refunded') AS refunded,
count(*) FILTER (WHERE status='canceled') AS cancels,
GROUPING(category, mon) AS grp
FROM (SELECT category, extract(month FROM sold_at)::int AS mon, amount, status
FROM sales) t
GROUP BY ROLLUP(category, mon)
ORDER BY GROUPING(category), category, GROUPING(mon), mon; category | mon | revenue | refunded | cancels | grp
-------------+----------+----------+----------+---------+-----
apparel | 1 | 1425.53 | 220.63 | 3 | 0
apparel | 2 | 2418.79 | 89.18 | 7 | 0
apparel | 3 | 2228.70 | 581.14 | 3 | 0
apparel | 4 | 2627.08 | 1168.86 | 5 | 0
apparel | subtotal | 8700.10 | 2059.81 | 18 | 1
electronics | 1 | 3139.91 | 557.01 | 4 | 0
electronics | 2 | 2656.91 | 702.85 | 1 | 0
electronics | 3 | 2200.97 | 45.45 | 3 | 0
electronics | 4 | 3065.11 | 967.45 | 3 | 0
electronics | subtotal | 11062.90 | 2272.76 | 11 | 1
grocery | 1 | 4636.73 | 1554.54 | 3 | 0
grocery | 2 | 3501.69 | 1275.40 | 2 | 0
grocery | 3 | 4134.65 | 906.44 | 4 | 0
grocery | 4 | 1842.27 | 229.68 | 2 | 0
grocery | subtotal | 14115.34 | 3966.06 | 11 | 1
ALL (total) | subtotal | 33878.34 | 8298.63 | 40 | 3
(16 rows)grp=0은 상세 행, grp=1은 카테고리 소계, 마지막 ALL (total) 행이 전사 총계다. 상세 12행 + 소계 3행 + 총계 1행 = 16행이 한 쿼리에서 나왔다. 같은 걸 UNION ALL로 짜면 최소 세 개의 SELECT를 붙여야 하고, 나중에 컬럼 하나 추가할 때마다 세 군데를 똑같이 고쳐야 한다.
결론 — 언제 이 조합을 쓰나
"상세 + 소계 + 총계"를 한 표로 내려보내는 리포트, 그리고 그 안에서 상태별(완료/환불/취소) 수치를 나란히 놓아야 하는 대시보드가 정확히 이 조합의 자리다. ROLLUP이 계층 소계를, GROUPING()이 소계 행 라벨을, FILTER가 조건부 집계 컬럼을 담당한다. 셋 다 core 기능이라 확장도 필요 없다.
이렇게도 쓴다
특정 소계 조합만 필요하면 ROLLUP 대신 GROUPING SETS로 정확히 지정한다. (조합: GROUPING SETS)
SELECT category, extract(month FROM sold_at)::int AS mon, sum(amount) AS revenue
FROM sales
GROUP BY GROUPING SETS ((category), (extract(month FROM sold_at)), ()); category | mon | revenue
-------------+-----+----------
apparel | | 8700.10
electronics | | 11062.90
grocery | | 14115.34
| 1 | 9202.17
| 2 | 8577.39
| 3 | 8564.32
| 4 | 7534.46
| | 33878.34
(8 rows)
카테고리별 매출 구성비(%)는 윈도우 집계로 붙인다. sum(sum(amount)) OVER ()가 전체 합. (조합: 윈도우 함수)
SELECT category, sum(amount) AS revenue,
round(100 * sum(amount) / sum(sum(amount)) OVER (), 1) AS pct
FROM sales GROUP BY category ORDER BY revenue DESC; category | revenue | pct
-------------+----------+------
grocery | 14115.34 | 41.7
electronics | 11062.90 | 32.7
apparel | 8700.10 | 25.7
모든 부분집합(카테고리 단독, 월 단독, 교차, 총계)이 다 필요하면 CUBE. (조합: CUBE)
SELECT category, extract(month FROM sold_at)::int AS mon, sum(amount)
FROM sales GROUP BY CUBE(category, extract(month FROM sold_at));
정렬을 소계가 맨 아래로 가게 하려면 ORDER BY GROUPING(...)을 앞에 둔다. NULL 정렬에 의존하지 않아 안전하다.
... GROUP BY ROLLUP(category, mon)
ORDER BY GROUPING(category), category, GROUPING(mon), mon;차원이 3개를 넘어가고 계층이 고정(연-분기-월-일)이면 ROLLUP이 여전히 깔끔하지만, 임의 다차원 슬라이스를 사용자가 직접 돌리는 BI 성격이면 집계를 미리 물질화(MATERIALIZED VIEW)해 두고 그 위에서 조회하는 편으로 갈아탄다.