2. 핵심 원리
2.1 세 문법이 만드는 그룹
GROUP BY 에 컬럼 조합(그룹핑 집합)을 여러 개 지정하는 것이 이 문법의 본질입니다. 세 문법은 조합을 만드는 방식이 다릅니다.
| 문법 | 만드는 그룹 조합 | 조합 수 |
|---|---|---|
ROLLUP(a, b) |
(a,b), (a), () |
3 |
CUBE(a, b) |
(a,b), (a), (b), () |
4 |
GROUPING SETS((a),(b)) |
적은 조합만 그대로 | 적은 개수 |
() 는 빈 조합으로 전체 합계, 즉 총계입니다. ROLLUP 은 오른쪽 컬럼부터 하나씩 떼어 가는 계층 합계라서 컬럼 순서가 중요합니다. ROLLUP(dept, mon) 은 부서별 소계가 나오고, ROLLUP(mon, dept) 는 월별 소계가 나옵니다.
CUBE 는 모든 조합이라 컬럼이 n 개면 2 의 n 제곱 개 조합이 나옵니다. GROUPING SETS 는 필요한 조합만 직접 고르는 가장 일반적인 형태이고, ROLLUP·CUBE 는 그 축약형입니다.
핵심
ROLLUP(a, b)는(a,b),(a),()세 단계,CUBE(a, b)는(a,b),(a),(b),()네 조합입니다. 소계로 합쳐진 컬럼 값은NULL로 나옵니다.
2.2 GROUPING 함수
소계 행의 합쳐진 컬럼은 NULL 로 보입니다. 그 NULL 이 "소계로 합쳐서" 생긴 것인지 "원래 데이터가 NULL 이어서" 생긴 것인지 구분하는 함수가 GROUPING(컬럼) 입니다.
| GROUPING(컬럼) | 뜻 |
|---|---|
| 0 | 이 컬럼으로 그룹핑된 행(원래 값) |
| 1 | 이 컬럼이 소계로 합쳐진 행 |
이 값으로 CASE WHEN GROUPING(dept) = 1 THEN '총계' ELSE dept END 처럼 라벨을 붙입니다. 소계 행이 결과의 어디에 나올지는 DB 가 보장하지 않으므로, 순서가 필요하면 ORDER BY GROUPING(dept), dept, GROUPING(mon), mon 처럼 GROUPING() 을 정렬에 넣습니다.
Oracle·MSSQL 에는 여러 컬럼의 GROUPING 값을 비트로 묶은 GROUPING_ID(a, b) 도 있습니다. 둘 다 합쳐지면 3, b 만 합쳐지면 1, a 만 합쳐지면 2 입니다.
2.3 DB 별 지원 범위
| 기능 | Oracle·Tibero | MySQL | MSSQL |
|---|---|---|---|
ROLLUP |
8i 부터 | WITH ROLLUP |
2008 부터 ROLLUP() |
CUBE |
8i 부터 | 없음 | 2008 부터 CUBE() |
GROUPING SETS |
8i 부터 | 없음 | 2008 부터 |
GROUPING() |
8i 부터 | 8.0 부터 | 지원 |
GROUPING_ID() |
9i 부터 | 없음 | 2008 부터 |
MSSQL 2005 이하는 GROUP BY dept, mon WITH ROLLUP·WITH CUBE 형식만 있었고, 2008 부터 표준형 ROLLUP()·CUBE()·GROUPING SETS 가 추가됐습니다. MySQL 은 WITH ROLLUP 만 있어서 CUBE·GROUPING SETS 는 UNION ALL 로 대체합니다.
MySQL 5.7 이하에는 GROUPING() 함수가 없어 소계 NULL 과 진짜 NULL 을 구분하기 어렵습니다. H2 2.3 은 세 문법을 모두 지원하지 않으므로 이 레슨의 실행 예제는 UNION ALL 대체 쿼리를 씁니다.
주의
ROLLUP·CUBE·GROUPING SETS는 H2 미지원이라 이 레슨의 해당 결과 표는 손으로 그린 도식입니다. 도식 값은 같은 데이터로 실행한UNION ALL대체 쿼리 결과와 같은 행·값입니다.