소스: sql-src/adv_06_rollup/02_cube_alt.sql, 03_dialects.sql. 02 는 예제와 같은 12건이고, 03 은 부서가 NULL 인 사원의 주문 1건을 더합니다.
CUBE(dept, mon) 은 상세, 부서 소계, 월 소계, 총계 네 조합을 만듭니다. UNION ALL 로 대체하고 그룹 종류를 g 컬럼으로 구분합니다.
SELECT
u.dept
, u.mon
, u.sales
, u.g
FROM (
SELECT dept, mon, SUM(amt) AS sales, 0 AS g
FROM sales_v
GROUP BY dept, mon
UNION ALL
SELECT dept, NULL, SUM(amt), 1
FROM sales_v
GROUP BY dept
UNION ALL
SELECT NULL, mon, SUM(amt), 2
FROM sales_v
GROUP BY mon
UNION ALL
SELECT NULL, NULL, SUM(amt), 3
FROM sales_v
) u
ORDER BY CASE u.g WHEN 2 THEN 1 WHEN 3 THEN 2 ELSE 0 END, u.dept, u.g, u.mon;DEPT | MON | SALES | G
-----+------+-------+--
개발 | 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 | 1 | 510 | 2
NULL | 2 | 470 | 2
NULL | 3 | 640 | 2
NULL | NULL | 1620 | 3
(12행)표준 문법은 이렇게 됩니다(H2 미지원). 도식 결과는 위 표에서 g 컬럼만 뺀 같은 12행입니다.
-- Oracle · Tibero, MSSQL 2008 이상 (MySQL 은 CUBE 없음)
SELECT
dept
, mon
, SUM(amt) AS sales
FROM sales_v
GROUP BY CUBE(dept, mon)
ORDER BY CASE WHEN GROUPING(dept) = 1 THEN 1 ELSE 0 END
, dept, GROUPING(mon), mon;ROLLUP 과 달리 월별 소계 3행(510, 470, 640)이 추가돼 12행이 됩니다. 정렬 키는 부서가 합쳐진 행을 뒤로 보내고, 그 안에서 월 소계를 총계 앞에 둡니다.
GROUPING SETS 는 필요한 조합만 고릅니다. 상세와 총계 없이 부서별 합계와 월별 합계만 원하는 경우입니다.
SELECT
u.dept
, u.mon
, u.sales
FROM (
SELECT dept, NULL AS mon, SUM(amt) AS sales, 1 AS g
FROM sales_v
GROUP BY dept
UNION ALL
SELECT NULL, mon, SUM(amt), 2
FROM sales_v
GROUP BY mon
) u
ORDER BY u.g, u.dept, u.mon;DEPT | MON | SALES
-----+------+------
개발 | NULL | 750
영업 | NULL | 870
NULL | 1 | 510
NULL | 2 | 470
NULL | 3 | 640
(5행)표준 문법은 이렇게 씁니다(H2 미지원). 도식 결과는 위 표에서 g 를 뺀 같은 5행입니다.
-- Oracle · Tibero, MSSQL 2008 이상 (MySQL 은 GROUPING SETS 없음)
SELECT
dept
, mon
, SUM(amt) AS sales
FROM sales_v
GROUP BY GROUPING SETS ((dept), (mon))
ORDER BY GROUPING(dept), dept, mon;GROUPING SETS ((dept), (mon)) 은 조합 두 개만 만들어 5행입니다. 앞 두 행은 부서로 그룹된 행이라 GROUPING(dept) 가 0 이고, 뒤 세 행은 부서가 합쳐져 1 이므로 정렬이 이 순서가 됩니다. CUBE 결과에서 상세·총계를 뺀 것과 같습니다.
03_dialects.sql 은 부서가 NULL 인 사원 최임시의 3월 주문 50 을 더합니다. 소계 행의 dept 는 NULL 로 나오고, 이 사원의 상세 행도 dept 가 NULL 이라 둘이 섞입니다. dept IS NULL 로 총계 라벨을 붙이면 어떻게 되는지 봅니다.
SELECT
CASE WHEN dept IS NULL THEN '총계' ELSE dept END AS dept_label
, mon
, SUM(amt) AS sales
FROM sales_v
GROUP BY dept, mon
ORDER BY CASE WHEN dept IS NULL THEN 1 ELSE 0 END, dept, mon;DEPT_LABEL | MON | SALES
-----------+-----+------
개발 | 1 | 250
개발 | 2 | 200
개발 | 3 | 300
영업 | 1 | 260
영업 | 2 | 270
영업 | 3 | 340
총계 | 3 | 50
(7행)최임시의 주문 50 이 "총계" 로 표시됐습니다. 데이터의 NULL 을 총계 라벨과 구분하지 못한 결과입니다. 구분 컬럼 lvl 을 쓰면 해결됩니다. 같은 쿼리를 SET MODE Oracle, MySQL, MSSQLServer 로 각각 돌려도 결과가 같습니다.
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 sales_v
GROUP BY dept, mon
UNION ALL
SELECT dept, NULL, SUM(amt), 1, '소계'
FROM sales_v
GROUP BY dept
UNION ALL
SELECT NULL, NULL, SUM(amt), 2, '총계'
FROM sales_v
) u
ORDER BY CASE WHEN u.lvl = 2 THEN 1 ELSE 0 END
, CASE WHEN u.dept IS NULL 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 | 3 | 50 | 0 | 상세
NULL | NULL | 50 | 1 | 소계
NULL | NULL | 1670 | 2 | 총계
(11행)dept 가 NULL 인 상세 행(50)과 그 소계(50), 총계(1670)가 lvl 로 구분됩니다. 정렬의 두 번째 키가 진짜 NULL 부서를 총계 바로 앞으로 모읍니다.
표준 문법에서는 GROUPING(dept) 가 같은 역할을 합니다. 진짜 NULL 인 부서 행은 GROUPING(dept) 가 0 이고 소계로 합쳐진 행은 1 입니다.
Oracle·Tibero 와 MSSQL 2008 이상은 예제 3 의 ROLLUP(dept, mon) 그대로이고 GROUPING_ID(dept, mon) 을 더 쓸 수 있습니다. MySQL 만 문법이 다르고, 결과는 예제 3 의 도식과 같은 값입니다.
-- MySQL (WITH ROLLUP 만 있음, GROUPING() 은 8.0 부터)
SELECT
dept
, mon
, SUM(amt) AS sales
, GROUPING(dept) AS gd
, GROUPING(mon) AS gm
FROM sales_v
GROUP BY dept, mon WITH ROLLUP;GROUPING_ID(dept, mon) 은 상세 0, 부서 소계 1, 총계 3 입니다. 이 쿼리는 실행하지 않은 문법 검토용입니다.