ROLLUP·CUBE·GROUPING SETS 결과표 읽기
이 장에서 배우는 것
지금까지는 조건에 맞는 행을 걸러내거나(EXISTS, 상관 서브쿼리) 표준 조인으로 필요한 컬럼을 모으는 방법을 다뤘다. 이번 장은 이미 모은 행을 여러 기준으로 묶어서 소계와 총계를 한 번의 GROUP BY로 뽑아내는 방법을 다룬다. ROLLUP, CUBE, GROUPING SETS는 문법 자체는 짧지만 결과표에 소계·총계 행이 섞여 나오기 때문에, 쿼리를 실행하기 전에 몇 행이 나올지 손으로 먼저 계산하는 훈련이 필요하다.
- ROLLUP, CUBE, GROUPING SETS가 각각 만들어내는 소계·총계 행의 규칙을 구분한다
- GROUPING 함수로 소계 행에서 생긴 NULL과 원본 데이터의 NULL을 구분한다
- ROLLUP·CUBE에 나열하는 컬럼 순서가 소계 기준과 결과 행 수를 어떻게 바꾸는지 설명한다
- 쿼리를 실행하기 전에 결과 행 수를 계산식으로 예측한다
문제 상황
서점 운영팀에서 매달 초 "카테고리별·지역별 매출을 한 화면에 정리해 달라"는 요청이 온다. 지금까지는 카테고리별 합계 쿼리, 지역별 합계 쿼리, 전체 합계 쿼리를 따로 세 번 돌려서 스프레드시트에 붙여넣는 방식으로 처리했다. 문제는 매번 소계를 붙이는 위치가 담당자마다 달라서, 지난달 보고서와 이번 달 보고서의 숫자 배열이 미묘하게 달라지는 일이 반복됐다는 점이다. 게다가 한 번은 ROLLUP으로 뽑은 결과표를 다른 사람이 이어받아 쓰면서, 카테고리 소계 행을 상세 행으로 착각해 매출을 다시 합산하는 바람에 총액이 두 배로 부풀려진 채 보고된 적도 있었다. 한 번의 GROUP BY로 상세·소계·총계를 동시에 뽑을 수 있다면 이런 반복 작업과 혼동을 줄일 수 있는데, 그러려면 결과표에 섞여 나오는 행의 정체를 정확히 읽어낼 수 있어야 한다.
이 장에서는 아래 네 테이블을 그대로 쓴다.
| 테이블 | 주요 컬럼 | 행 수 | 비고 |
|---|---|---|---|
| 회원 | 회원번호, 이름, 지역 | 3 | 지역: 서울 2명, 부산 1명 |
| 도서 | 도서번호, 제목, 카테고리, 정가 | 4 | 카테고리: 소설 2권, 경제 1권, 컴퓨터 1권 |
| 주문·주문상세 | 주문번호, 회원번호, 도서번호, 금액 | 5 / 7 | 주문상세 7행이 이 장 계산의 기준이 된다 |
소계·총계 행이 생기는 규칙
세 문법은 모두 GROUP BY에 어떤 컬럼 조합으로 소계를 만들지 지시하는 확장 문법이다. 두 컬럼 A, B를 기준으로 비교하면 다음과 같다.
- ROLLUP(A, B): (A,B), (A), () 세 단계만 만든다. 나열한 순서대로 뒤 컬럼부터 하나씩 떼어내며 계층적으로 합산한다.
- CUBE(A, B): (A,B), (A), (B), () 네 조합을 모두 만든다. 컬럼 사이의 계층 관계를 따지지 않고 가능한 조합을 전부 뽑는다.
- GROUPING SETS(...): 괄호 안에 원하는 조합만 직접 나열한다. ROLLUP과 CUBE는 사실 GROUPING SETS의 특수한 경우다. 즉
ROLLUP(A,B)는GROUPING SETS((A,B),(A),())와,CUBE(A,B)는GROUPING SETS((A,B),(A),(B),())와 같은 결과를 낸다.
이 장의 샘플 데이터에서 카테고리·지역 조합으로 매출이 존재하는 경우는 5가지(경제·서울, 경제·부산, 소설·서울, 소설·부산, 컴퓨터·서울)뿐이다. 이 5행을 기준으로 ROLLUP과 CUBE가 소계 행을 어떻게 얹는지 그림으로 보면 아래와 같다.
GROUPING 함수로 소계 행 구분
ROLLUP·CUBE·GROUPING SETS로 만든 소계 행은 집계에서 빠진 컬럼 값을 NULL로 채운다. 문제는 원본 데이터에 실제 NULL이 들어있는 컬럼이라면, 결과표의 NULL이 "이 컬럼을 접어서 만든 소계"인지 "원래 값이 없어서 NULL"인지 값만 보고는 구분할 수 없다는 점이다. GROUPING(컬럼) 함수는 그 컬럼이 해당 행에서 집계로 인해 NULL이 되었으면 1, 원래 값을 그대로 갖고 있으면(원본이 NULL인 경우 포함) 0을 반환한다.
이 장의 샘플 데이터는 지역·카테고리 컬럼에 NULL을 허용하지 않으므로, 결과표에 나오는 모든 NULL은 예외 없이 소계나 총계를 뜻한다. 하지만 실무 테이블에는 지역 미상 회원처럼 원본 NULL이 실제로 존재하는 경우가 많다. NULL이 섞인 컬럼을 ROLLUP·CUBE로 묶을 때는 GROUPING 함수 없이 NULL만으로 소계 행을 필터링하면 안 된다는 점을 기억해 둔다.
컬럼 순서에 따른 결과 차이
ROLLUP과 CUBE는 나열한 컬럼 순서에 따라 결과가 달라진다. CUBE는 모든 조합을 다 만들기 때문에 순서를 바꿔도 조합의 집합 자체는 같지만, ROLLUP은 계층적으로 소계를 만들기 때문에 첫 번째로 나열한 컬럼이 소계의 기준이 된다. ROLLUP(카테고리, 지역)은 카테고리별 소계를 만들고, ROLLUP(지역, 카테고리)는 지역별 소계를 만든다.
이 장의 데이터는 카테고리가 3종류, 지역이 2종류라서 두 순서의 결과 행 수도 달라진다. 카테고리를 먼저 두면 소계가 3개 생기고, 지역을 먼저 두면 소계가 2개만 생긴다.
결과 행 수는 컬럼 값의 종류 수(카디널리티)만 알면 실행 전에 계산할 수 있다. 카테고리 3종, 지역 2종, 카테고리·지역 조합 5종(6종 중 컴퓨터·부산 조합은 데이터가 없어 나오지 않는다)인 이 장의 데이터를 기준으로 정리하면 다음과 같다.
| 문법 | 행 수 공식(A, B 두 컬럼) | 이 장 데이터 결과 |
|---|---|---|
| ROLLUP(카테고리, 지역) | 조합 수 + 카테고리 종류 수 + 1 | 5 + 3 + 1 = 9행 |
| CUBE(카테고리, 지역) | 조합 수 + 카테고리 종류 수 + 지역 종류 수 + 1 | 5 + 3 + 2 + 1 = 11행 |
| GROUPING SETS((카테고리),(지역)) | 카테고리 종류 수 + 지역 종류 수 | 3 + 2 = 5행 |
| ROLLUP(지역, 카테고리) | 조합 수 + 지역 종류 수 + 1 | 5 + 2 + 1 = 8행 |
표준 SQL과 Oracle, PostgreSQL은 세 문법을 함수형 표기로 모두 지원하지만 MySQL 8은 ROLLUP만 다른 방식으로 지원한다.
| 문법 | MySQL 8 | Oracle / PostgreSQL |
|---|---|---|
| ROLLUP | GROUP BY 컬럼, … WITH ROLLUP (나열 순서가 곧 계층) | GROUP BY ROLLUP(컬럼, …) 함수형 표기 지원 |
| CUBE | 지원하지 않음 | GROUP BY CUBE(컬럼, …) 지원 |
| GROUPING SETS | 지원하지 않음 | GROUP BY GROUPING SETS(…) 지원 |
| GROUPING() 함수 | 8.0.12 이상, WITH ROLLUP과 함께 사용 | 지원 |
완성 코드
아래 스크립트는 표준 SQL의 GROUP BY 확장 문법(ROLLUP, CUBE, GROUPING SETS, GROUPING)을 그대로 쓴다. Oracle과 PostgreSQL에서 수정 없이 실행되며, MySQL 8에서 대체하는 방법은 뒤의 "실무에서 자주 틀리는 것"에서 다룬다.
CREATE TABLE 회원 (
회원번호 INTEGER PRIMARY KEY,
이름 VARCHAR(20) NOT NULL,
지역 VARCHAR(10) NOT NULL
);
CREATE TABLE 도서 (
도서번호 INTEGER PRIMARY KEY,
제목 VARCHAR(50) NOT NULL,
카테고리 VARCHAR(10) NOT NULL,
정가 INTEGER NOT NULL
);
CREATE TABLE 주문 (
주문번호 INTEGER PRIMARY KEY,
회원번호 INTEGER NOT NULL,
주문일자 DATE NOT NULL,
FOREIGN KEY (회원번호) REFERENCES 회원(회원번호)
);
CREATE TABLE 주문상세 (
주문번호 INTEGER NOT NULL,
도서번호 INTEGER NOT NULL,
수량 INTEGER NOT NULL,
금액 INTEGER NOT NULL,
PRIMARY KEY (주문번호, 도서번호),
FOREIGN KEY (주문번호) REFERENCES 주문(주문번호),
FOREIGN KEY (도서번호) REFERENCES 도서(도서번호)
);
INSERT INTO 회원 VALUES (1, '김도윤', '서울');
INSERT INTO 회원 VALUES (2, '이서준', '부산');
INSERT INTO 회원 VALUES (3, '박하은', '서울');
INSERT INTO 도서 VALUES (101, '미드나잇 라이브러리', '소설', 15000);
INSERT INTO 도서 VALUES (102, '달러구트 꿈 백화점', '소설', 14000);
INSERT INTO 도서 VALUES (103, '부의 추월차선', '경제', 18000);
INSERT INTO 도서 VALUES (104, '클린 코드', '컴퓨터', 22000);
INSERT INTO 주문 VALUES (1001, 1, DATE '2026-07-03');
INSERT INTO 주문 VALUES (1002, 2, DATE '2026-07-05');
INSERT INTO 주문 VALUES (1003, 1, DATE '2026-08-01');
INSERT INTO 주문 VALUES (1004, 3, DATE '2026-08-10');
INSERT INTO 주문 VALUES (1005, 2, DATE '2026-08-15');
INSERT INTO 주문상세 VALUES (1001, 101, 1, 15000);
INSERT INTO 주문상세 VALUES (1001, 103, 1, 18000);
INSERT INTO 주문상세 VALUES (1002, 102, 2, 28000);
INSERT INTO 주문상세 VALUES (1003, 104, 1, 22000);
INSERT INTO 주문상세 VALUES (1004, 101, 1, 15000);
INSERT INTO 주문상세 VALUES (1004, 102, 1, 14000);
INSERT INTO 주문상세 VALUES (1005, 103, 1, 18000);
-- 카테고리·지역별 매출 상세(조인 결과 7행)를 뷰로 만들어 재사용한다
CREATE VIEW 매출 AS
SELECT b.카테고리, m.지역, od.금액
FROM 주문상세 od
JOIN 주문 o ON o.주문번호 = od.주문번호
JOIN 도서 b ON b.도서번호 = od.도서번호
JOIN 회원 m ON m.회원번호 = o.회원번호;
-- 쿼리 1: ROLLUP(카테고리, 지역)
SELECT 카테고리, 지역, SUM(금액) AS 매출액,
GROUPING(카테고리) AS 카테고리집계,
GROUPING(지역) AS 지역집계
FROM 매출
GROUP BY ROLLUP(카테고리, 지역)
ORDER BY GROUPING(카테고리), 카테고리, GROUPING(지역), 지역;
-- 쿼리 2: CUBE(카테고리, 지역)
SELECT 카테고리, 지역, SUM(금액) AS 매출액
FROM 매출
GROUP BY CUBE(카테고리, 지역)
ORDER BY GROUPING(카테고리), 카테고리, GROUPING(지역), 지역;
-- 쿼리 3: 필요한 소계만 GROUPING SETS로 지정
SELECT 카테고리, 지역, SUM(금액) AS 매출액
FROM 매출
GROUP BY GROUPING SETS ((카테고리), (지역))
ORDER BY 카테고리 NULLS LAST, 지역 NULLS LAST;
-- 쿼리 4: 컬럼 순서를 바꾼 ROLLUP(지역, 카테고리)
SELECT 지역, 카테고리, SUM(금액) AS 매출액
FROM 매출
GROUP BY ROLLUP(지역, 카테고리)
ORDER BY GROUPING(지역), 지역, GROUPING(카테고리), 카테고리;
줄별 해설
- 회원·도서·주문·주문상세 네 테이블은 기본서에서 쓴 그대로다. 주문상세는 (주문번호, 도서번호) 복합 기본키를 갖는다.
- INSERT 문 7건의 주문상세가 이 장 계산의 출발점이다. 각 행은 주문 1건, 도서 1건에만 연결되므로 조인해도 행 수가 늘지 않는다.
CREATE VIEW 매출은 주문상세·주문·도서·회원을 조인해 카테고리·지역·금액 3컬럼짜리 7행을 만든다. 이후 모든 GROUP BY 예제가 이 뷰 하나를 재사용한다.- 쿼리 1의
GROUP BY ROLLUP(카테고리, 지역)은 (카테고리,지역) 상세 5행, 카테고리별 소계 3행, 전체 총계 1행, 총 9행을 만든다. GROUPING(카테고리)와GROUPING(지역)은 각 행에서 어느 컬럼이 소계로 접혔는지를 0/1로 알려준다. 상세 행은 (0,0), 카테고리 소계는 (0,1), 전체 총계는 (1,1)이 된다.ORDER BY GROUPING(카테고리), 카테고리, GROUPING(지역), 지역은 상세 행을 먼저, 그 카테고리의 소계를 바로 뒤에, 전체 총계를 맨 마지막에 배치해 결과를 읽기 쉽게 만든다.- 쿼리 2는 같은 뷰에 CUBE를 적용해 지역별 소계(서울, 부산) 2행이 추가되어 총 11행이 된다.
- 쿼리 3은 상세 행과 전체 총계 없이 카테고리별 소계 3행, 지역별 소계 2행만 뽑는다.
NULLS LAST는 카테고리가 NULL인 지역 소계 행을 카테고리 소계 뒤로 밀어 순서를 고정한다. - 쿼리 4는 컬럼 순서를 지역, 카테고리로 바꿔 지역별 소계만 만든다. 카테고리가 3종, 지역이 2종이므로 쿼리 1(9행)보다 한 행 적은 8행이 나온다.
실행 결과
PostgreSQL이나 Oracle에서 위 스크립트를 그대로 실행한다.
$ psql -h localhost -U bookstore -d bookstore -f rollup_demo.sql쿼리 1은 9행을 반환한다.
카테고리 | 지역 | 매출액 | 카테고리집계 | 지역집계
----------+------+--------+--------------+----------
경제 | 부산 | 18000 | 0 | 0
경제 | 서울 | 18000 | 0 | 0
경제 | | 36000 | 0 | 1
소설 | 부산 | 28000 | 0 | 0
소설 | 서울 | 44000 | 0 | 0
소설 | | 72000 | 0 | 1
컴퓨터 | 서울 | 22000 | 0 | 0
컴퓨터 | | 22000 | 0 | 1
| | 130000 | 1 | 1
쿼리 2는 11행을 반환한다. 카테고리 소계 3행 뒤에 지역 소계 2행이 추가된다.
카테고리 | 지역 | 매출액
----------+------+--------
경제 | 부산 | 18000
경제 | 서울 | 18000
경제 | | 36000
소설 | 부산 | 28000
소설 | 서울 | 44000
소설 | | 72000
컴퓨터 | 서울 | 22000
컴퓨터 | | 22000
| 부산 | 46000
| 서울 | 84000
| | 130000
쿼리 3은 상세 행과 전체 총계 없이 5행만 반환한다. 카테고리 소계의 합(130000)과 지역 소계의 합(130000)이 같다는 점으로 계산이 맞는지 검산할 수 있다.
카테고리 | 지역 | 매출액
----------+------+--------
경제 | | 36000
소설 | | 72000
컴퓨터 | | 22000
| 부산 | 46000
| 서울 | 84000
쿼리 4는 지역이 카테고리보다 종류가 적어 8행만 반환한다.
지역 | 카테고리 | 매출액
------+----------+--------
부산 | 경제 | 18000
부산 | 소설 | 28000
부산 | | 46000
서울 | 경제 | 18000
서울 | 소설 | 44000
서울 | 컴퓨터 | 22000
서울 | | 84000
| | 130000
실무에서 자주 틀리는 것
WHERE 절에서 GROUPING을 걸러내려는 실수
WHERE 절은 GROUP BY보다 먼저 평가되므로 그 시점에는 아직 소계 행이 존재하지 않는다. GROUPING은 GROUP BY 이후에만 의미가 있다.
-- 틀린 코드: WHERE에서 GROUPING을 참조해 오류가 난다
SELECT 카테고리, SUM(금액) AS 매출액
FROM 매출
WHERE GROUPING(카테고리) = 1
GROUP BY ROLLUP(카테고리);
-- 고친 코드: 그룹핑 이후 필터는 HAVING에서 처리한다
SELECT 카테고리, SUM(금액) AS 매출액
FROM 매출
GROUP BY ROLLUP(카테고리)
HAVING GROUPING(카테고리) = 1;
CUBE를 계층으로 착각해 불필요한 조합을 남발
카테고리별 합계만 필요한데 습관적으로 CUBE를 쓰면 지역별 소계, 전체 총계까지 다 생겨서 애플리케이션에서 다시 걸러내야 한다. 컬럼이 늘어날수록 CUBE의 조합 수는 2의 컬럼 수 제곱으로 늘어나므로 필요 이상으로 무거워진다.
-- 틀린 코드: 카테고리별 합계만 필요한데 CUBE로 모든 조합을 다 뽑는다
SELECT 카테고리, 지역, SUM(금액) AS 매출액
FROM 매출
GROUP BY CUBE(카테고리, 지역);
-- 고친 코드: 필요한 조합만 GROUPING SETS로 지정한다
SELECT 카테고리, SUM(금액) AS 매출액
FROM 매출
GROUP BY GROUPING SETS ((카테고리));
소계로 생긴 NULL과 원본 NULL을 혼동
지역 컬럼에 실제로 미상(NULL) 값이 있는 테이블이라면, ROLLUP 결과의 NULL만 보고 필터링하면 소계 행까지 함께 걸려 매출이 중복 집계된다.
-- 틀린 코드: 지역이 NULL인 행만 뽑으려다 소계 행까지 섞인다
SELECT 카테고리, 지역, SUM(금액) AS 매출액
FROM 매출
GROUP BY ROLLUP(카테고리, 지역)
HAVING 지역 IS NULL;
-- 고친 코드: GROUPING으로 소계가 아닌 원본 NULL만 남긴다
SELECT 카테고리, 지역, SUM(금액) AS 매출액
FROM 매출
GROUP BY ROLLUP(카테고리, 지역)
HAVING GROUPING(지역) = 0 AND 지역 IS NULL;
MySQL에서 표준 CUBE·GROUPING SETS 문법을 그대로 쓴 실수
MySQL 8은 CUBE(...)와 GROUPING SETS(...) 함수형 표기를 지원하지 않는다. ROLLUP만 WITH ROLLUP 형태로 대체할 수 있다.
-- 틀린 코드: MySQL 8에서 구문 오류
SELECT 카테고리, 지역, SUM(금액) AS 매출액
FROM 매출
GROUP BY CUBE(카테고리, 지역);
-- 고친 코드(MySQL 8): ROLLUP은 WITH ROLLUP으로, CUBE는 UNION ALL로 대체
SELECT 카테고리, 지역, SUM(금액) AS 매출액
FROM 매출
GROUP BY 카테고리, 지역 WITH ROLLUP;
한눈에 보기
| 구문 | 만들어지는 조합 | 기억할 점 |
|---|---|---|
| ROLLUP(A,B) | (A,B), (A), () | 나열 순서가 계층이며 앞 컬럼이 소계 기준이 된다 |
| CUBE(A,B) | (A,B), (A), (B), () | 모든 조합을 만들며 컬럼이 늘수록 조합이 급증한다 |
| GROUPING SETS(...) | 명시한 조합만 | ROLLUP·CUBE로 못 만드는 임의 조합도 지정할 수 있다 |
| GROUPING(컬럼) | 해당 없음 | 소계로 생긴 NULL(1)과 원본 값(0, NULL 포함)을 구분한다 |
연습 문제
- 이 장의 매출 뷰에
ROLLUP(지역, 카테고리, 연월)처럼 세 컬럼을 넣으면 계층적으로 몇 단계의 조합이 만들어지는가? 각 단계를 괄호로 나열하라. - 이 장의 데이터를 기준으로
GROUP BY CUBE(카테고리, 지역)의 결과 행 수를 계산식과 함께 구하라. - 어떤 결과 행에서
GROUPING(카테고리) = 0,GROUPING(지역) = 1이 나왔다. 이 행이 의미하는 것은 무엇인가? - 상세 행과 전체 총계 없이 카테고리별 합계와 지역별 합계만 뽑는 SQL을 작성하라. 이 장의 매출 뷰를 사용한다.
정답과 해설
1. ROLLUP(A,B,C) 형태이므로 (지역,카테고리,연월), (지역,카테고리), (지역), () 네 단계가 만들어진다. 일반적으로 컬럼이 n개면 n+1 단계의 조합이 생기며, 각 단계의 실제 행 수는 해당 조합의 값 종류 수에 따라 달라진다.
2. 카테고리·지역 조합 5종 + 카테고리 소계 3종 + 지역 소계 2종 + 전체 총계 1행 = 11행이다. CUBE는 두 컬럼의 모든 부분집합 조합, 즉 (카테고리,지역), (카테고리), (지역), ()을 다 만들기 때문이다.
3. GROUPING(카테고리)=0은 카테고리 값이 그대로 남아있다는 뜻이고, GROUPING(지역)=1은 지역이 소계로 인해 NULL이 되었다는 뜻이다. 즉 이 행은 "특정 카테고리의 지역 소계"이며 전체 총계 행은 아니다. 전체 총계라면 두 GROUPING 값이 모두 1이어야 한다.
4. 아래처럼 GROUPING SETS로 필요한 두 조합만 지정하면 된다.
SELECT 카테고리, 지역, SUM(금액) AS 매출액
FROM 매출
GROUP BY GROUPING SETS ((카테고리), (지역))
ORDER BY 카테고리 NULLS LAST, 지역 NULLS LAST;
ROLLUP이나 CUBE는 상세 행이나 전체 총계를 함께 만들기 때문에 이런 조합에는 맞지 않는다. 원하는 소계만 골라 뽑아야 할 때는 GROUPING SETS가 적절하다.