소스: sql-src/adv_06_rollup/01_union_subtotal.sql. 부서 2개(개발·영업)와 주문 12건으로 부서×월(1~3월) 매출을 집계합니다. 개발은 김개발·남개발, 영업은 이영업·박영업 소속입니다.
ROLLUP 을 쓸 수 없을 때는 그룹 단계마다 쿼리를 만들어 UNION ALL 로 붙입니다. 소계 행인지 표시하는 구분 컬럼 lvl(0 상세, 1 소계, 2 총계)과 라벨을 함께 만듭니다.
WITH s AS (
SELECT
e.dept
, EXTRACT(MONTH FROM o.ord_date) AS mon
, o.amt
FROM orders o
JOIN emp e ON e.id = o.emp_id
)
SELECT
u.dept
, u.mon
, u.sales
, u.lvl
, u.label
FROM (
SELECT dept, mon, SUM(amt) AS sales, 0 AS lvl, '상세' AS label
FROM s
GROUP BY dept, mon
UNION ALL
SELECT dept, NULL, SUM(amt), 1, '소계'
FROM s
GROUP BY dept
UNION ALL
SELECT NULL, NULL, SUM(amt), 2, '총계'
FROM s
) u
ORDER BY CASE WHEN u.lvl = 2 THEN 1 ELSE 0 END, u.dept, u.lvl, u.mon;DEPT | MON | SALES | LVL | LABEL
-----+------+-------+-----+------
개발 | 1 | 250 | 0 | 상세
개발 | 2 | 200 | 0 | 상세
개발 | 3 | 300 | 0 | 상세
개발 | NULL | 750 | 1 | 소계
영업 | 1 | 260 | 0 | 상세
영업 | 2 | 270 | 0 | 상세
영업 | 3 | 340 | 0 | 상세
영업 | NULL | 870 | 1 | 소계
NULL | NULL | 1620 | 2 | 총계
(9행)정렬은 세 단계입니다. 총계(lvl 2)를 맨 뒤로 보내고, 부서 안에서 상세(0)가 소계(1)보다 앞에 오게 lvl 로 정렬한 뒤, 월 순으로 나열합니다. 소계 행의 mon 은 합쳐졌다는 뜻의 NULL 이고 총계의 dept·mon 도 NULL 입니다.
lvl 구분 컬럼은 정렬과 라벨 두 가지에 쓰입니다. 이 패턴은 뒤에서 진짜 NULL 을 구분할 때도 그대로 씁니다.
같은 소계를 ORDER BY dept, mon 만으로 정렬하면 소계 위치가 NULL 정렬 규칙에 맡겨집니다. 아래는 총계를 뺀 상세 + 소계 8행입니다.
WITH s AS (
SELECT
e.dept
, EXTRACT(MONTH FROM o.ord_date) AS mon
, o.amt
FROM orders o
JOIN emp e ON e.id = o.emp_id
)
SELECT
u.dept
, u.mon
, u.sales
FROM (
SELECT dept, mon, SUM(amt) AS sales
FROM s
GROUP BY dept, mon
UNION ALL
SELECT dept, NULL, SUM(amt)
FROM s
GROUP BY dept
) u
ORDER BY u.dept, u.mon;DEPT | MON | SALES
-----+------+------
개발 | NULL | 750
개발 | 1 | 250
개발 | 2 | 200
개발 | 3 | 300
영업 | NULL | 870
영업 | 1 | 260
영업 | 2 | 270
영업 | 3 | 340
(8행)H2 는 오름차순에서 NULL 을 앞에 두어 소계가 부서 맨 위에 나왔습니다. 확인된 사실 기준으로 Oracle 은 오름차순에서 NULL 을 마지막에 두고 MySQL·MSSQL 은 처음에 둡니다. 같은 쿼리가 DB 에 따라 소계 위치가 달라지므로, 순서에 의미가 있는 보고서는 구분 컬럼으로 정렬해야 합니다.
ROLLUP(dept, mon) 은 예제 1 의 세 쿼리를 한 번에 만듭니다. GROUPING() 값도 함께 뽑고, 그 값으로 정렬해 소계가 부서 끝에 오게 합니다.
-- Oracle · Tibero, MSSQL 2008 이상
SELECT
dept
, mon
, SUM(amt) AS sales
, GROUPING(dept) AS gd
, GROUPING(mon) AS gm
FROM sales_v
GROUP BY ROLLUP(dept, mon)
ORDER BY GROUPING(dept), dept, GROUPING(mon), mon;sales_v 는 부서·월·금액을 돌려주는 뷰이고 소스는 02_cube_alt.sql 에 있습니다.
도식(H2 미지원, 실행 결과 아님). 예제 1 의 UNION ALL 결과와 같은 값입니다.
DEPT | MON | SALES | GD | GM
-----+------+-------+----+---
개발 | 1 | 250 | 0 | 0
개발 | 2 | 200 | 0 | 0
개발 | 3 | 300 | 0 | 0
개발 | NULL | 750 | 0 | 1
영업 | 1 | 260 | 0 | 0
영업 | 2 | 270 | 0 | 0
영업 | 3 | 340 | 0 | 0
영업 | NULL | 870 | 0 | 1
NULL | NULL | 1620 | 1 | 1GD 가 1 이면 부서까지 합쳐진 총계, GD 0·GM 1 이면 부서 소계입니다. 정렬 키 GROUPING(dept) 가 총계를 맨 뒤로 보내고 GROUPING(mon) 이 소계를 상세 뒤로 보냅니다.
GD·GM 숫자 대신 사람이 읽는 라벨을 만듭니다. 월은 숫자라서 라벨 문자열과 섞으려면 CAST 로 문자열로 바꿉니다.
SELECT
CASE WHEN GROUPING(dept) = 1 THEN '총계' ELSE dept END AS dept_label
, CASE WHEN GROUPING(dept) = 1 THEN '-'
WHEN GROUPING(mon) = 1 THEN '소계'
ELSE CAST(mon AS VARCHAR(10)) END AS mon_label
, SUM(amt) AS sales
FROM sales_v
GROUP BY ROLLUP(dept, mon)
ORDER BY GROUPING(dept), dept, GROUPING(mon), mon;도식(H2 미지원, 실행 결과 아님).
DEPT_LABEL | MON_LABEL | SALES
-----------+-----------+------
개발 | 1 | 250
개발 | 2 | 200
개발 | 3 | 300
개발 | 소계 | 750
영업 | 1 | 260
영업 | 2 | 270
영업 | 3 | 340
영업 | 소계 | 870
총계 | - | 1620이 라벨에는 NULL 이 남지 않습니다. CASE 가 GROUPING() 으로 판단하기 때문에, 데이터의 NULL 과 소계의 NULL 이 섞여도 틀리지 않습니다.
팁소계 행 정렬과 라벨은 항상
GROUPING()으로 합니다.ORDER BY dept만 쓰거나dept IS NULL로 총계를 판단하면 DB 별NULL정렬과 진짜NULL에 흔들립니다.