2과목 · SQL 기본 및 활용 / 심화

그룹 함수

ROLLUP·CUBE·GROUPING SETS로 여러 단위의 소계와 합계를 한 쿼리로 구하고, GROUPING 함수로 소계 행을 구분합니다.

출제기출 11회분 중 20문항 · 많이 나온 주제: ROLLUP/CUBE/GROUPING SETS(20)

학습 목표

ROLLUP·CUBE·GROUPING SETS로 여러 단위의 소계와 합계를 한 쿼리로 구하고, GROUPING 함수로 소계 행을 구분합니다.

그룹 함수 한눈에 보기

그룹 함수는 여러 단위의 소계와 합계를 한 쿼리로 구하는 GROUP BY 확장 기능입니다. 매출 보고서에서 '부서+직무별 / 부서별 / 전체 합계'를 SELECT 세 번으로 나눠 구하지 않고 한 번에 뽑습니다. ROLLUP, CUBE, GROUPING SETS 세 가지가 있고, 모두 GROUP BY 절 안에서만 씁니다.

만들어지는 그룹 조합 수는 열이 n개일 때 ROLLUP은 n+1개, CUBE는 2ⁿ개입니다. GROUPING SETS는 직접 나열한 조합만 만듭니다. 소계·합계 행에서는 집계에서 빠진 열이 NULL로 표시됩니다.

개념 정리그룹 함수의 종류 — 같은 조합도에서 무엇을 만드나

ROLLUP
계층적 소계
(A, B) → (A) → () — n+1단계. 오른쪽 열부터 하나씩 빼며 소계
CUBE
모든 조합
(A, B) → (A) → (B) → () — 2ⁿ단계. 큐브의 모든 면
SETS
GROUPING SETS
지정한 조합만 — 가장 유연. 예: GROUPING SETS(A, B)는 (A)와 (B)만, 전체 합계 ()는 직접 써야 생김

A, B 두 열일 때 만들어지는 그룹 조합
GROUP BY 뒤	그룹 조합	개수
`A, B` (기본)	(A, B)	1
`ROLLUP(A, B)`	(A, B), (A), ()	3 (n+1)
`CUBE(A, B)`	(A, B), (A), (B), ()	4 (2ⁿ)
`GROUPING SETS(A, B)`	(A), (B)	2 (나열한 것만)

이 단원의 예시는 사원 3명의 급여입니다. 결과 행 수를 비교하면 기본 GROUP BY 3행 → ROLLUP 6행 → CUBE 8행이고, GROUPING SETS((DEPT_ID), (JOB), ())는 5행입니다.

TABLEEMP — 사원 3명3행

DEPT_ID	JOB	SAL
D1	MGR	5000
D1	CLERK	2000
D2	MGR	4500

ROLLUP

ROLLUP은 나열한 열의 오른쪽부터 하나씩 빼면서 계층적 소계를 만들고, 마지막에 전체 합계를 만듭니다. ROLLUP(DEPT_ID, JOB)은 (부서, 직무) → (부서) 소계 → () 전체 합계의 3단계이고, ROLLUP(A, B, C)라면 (A, B, C) → (A, B) → (A) → ()의 4단계입니다.

그래서 ROLLUP은 인수 순서가 중요합니다. ROLLUP(부서, 직급)은 부서별 소계를, ROLLUP(직급, 부서)는 직급별 소계를 만듭니다. ROLLUP(부서, 직급)에는 직급 단독 소계 (NULL, 직급)가 없습니다. GROUP BY A, ROLLUP(B)처럼 일부 열에만 적용할 수도 있습니다.

ROLLUP(DEPT_ID, JOB) — 오른쪽 열부터 빼기

ROLLUP

(DEPT_ID, JOB)
(DEPT_ID) 부서 소계
() 전체 합계

CUBE는 여기에 추가

(JOB) 직무 소계

ROLLUP에는 (JOB) 단독 소계가 없습니다. 직무별 합계까지 보려면 CUBE를 씁니다.

SELECT DEPT_ID, JOB, SUM(SAL)
  FROM EMP
 GROUP BY ROLLUP(DEPT_ID, JOB);

EMP 3행에 ROLLUP을 적용하면

EMP 3행

DEPT	JOB	SAL
D1	MGR	5000
D1	CLERK	2000
D2	MGR	4500

ROLLUP(DEPT, JOB)부서 소계 + 전체 합계 추가

ROLLUP 결과 6행

DEPT	JOB	SUM
D1	MGR	5000
D1	CLERK	2000
D1	NULL	7000
D2	MGR	4500
D2	NULL	4500
NULL	NULL	11500

색칠한 3행이 ROLLUP이 추가로 만든 행입니다. D1·D2 부서 소계와 맨 아래 전체 합계이고, 빠진 열은 NULL로 표시됩니다.

CUBE와 GROUPING SETS

CUBE는 나열한 열로 만들 수 있는 모든 조합의 소계를 만듭니다. CUBE(DEPT_ID, JOB)은 ROLLUP 결과에 직무별 소계 (NULL, JOB)까지 더해 2ⁿ = 4가지 조합입니다. 모든 조합을 만들기 때문에 인수 순서를 바꿔도 같은 조합이 나옵니다.

GROUPING SETS는 필요한 조합만 직접 나열합니다. 나열하지 않은 조합은 만들지 않으므로 전체 합계 ()도 자동으로 들어가지 않습니다. 인수 순서를 바꿔도 결과 항목은 같고, 각 그룹의 GROUP BY 결과를 UNION ALL로 이어 붙인 것과 같습니다. 결과 순서가 필요하면 ORDER BY를 따로 씁니다.

-- CUBE: ROLLUP 6행 + 직무별 소계 2행 = 8행
SELECT DEPT_ID, JOB, SUM(SAL)
  FROM EMP
 GROUP BY CUBE(DEPT_ID, JOB);

-- GROUPING SETS: 부서별, 직무별, 전체만 = 5행
SELECT DEPT_ID, JOB, SUM(SAL)
  FROM EMP
 GROUP BY GROUPING SETS((DEPT_ID), (JOB), ());

CUBE는 ROLLUP 결과에 직무 소계를 더한다

ROLLUP 6행

DEPT	JOB	SUM
D1	MGR	5000
D1	CLERK	2000
D1	NULL	7000
D2	MGR	4500
D2	NULL	4500
NULL	NULL	11500

CUBE에만 있는 2행

DEPT	JOB	SUM
NULL	MGR	9500
NULL	CLERK	2000

CUBE 결과 = ROLLUP 6행 + 직무별 소계 2행 = 8행입니다. ROLLUP에는 (NULL, 직무) 행이 없습니다.

TABLEGROUPING SETS((DEPT_ID), (JOB), ()) 결과 — 5행5행

DEPT_ID	JOB	SUM(SAL)
D1	NULL	7000
D2	NULL	4500
NULL	MGR	9500
NULL	CLERK	2000
NULL	NULL	11500

-- 위 GROUPING SETS와 같은 결과
SELECT DEPT_ID, NULL, SUM(SAL) FROM EMP GROUP BY DEPT_ID
UNION ALL
SELECT NULL, JOB, SUM(SAL) FROM EMP GROUP BY JOB
UNION ALL
SELECT NULL, NULL, SUM(SAL) FROM EMP;

GROUPING과 결과 해석

결과에 보이는 NULL이 원래 데이터의 NULL인지 소계 때문에 생긴 NULL인지는 값만 봐서는 알 수 없습니다. GROUPING(열) 함수는 그 열이 소계·합계로 집계에서 빠진 행이면 1, 일반 행이면 0을 돌려줍니다.

GROUPING 값으로 행을 고르거나 이름을 붙일 수 있습니다. HAVING GROUPING(A) + GROUPING(B) <> 1은 합이 0인 상세 행과 2인 전체 합계 행만 남기고, 중간 소계 행(합 1)은 뺍니다. CASE WHEN GROUPING(JOB) = 1 THEN '소계' ...처럼 소계 행에 표시 문구를 붙이는 데도 씁니다.

SELECT DEPT_ID, JOB, SUM(SAL) AS SUM_SAL,
       GROUPING(DEPT_ID) AS G_DEPT,
       GROUPING(JOB) AS G_JOB
  FROM EMP
 GROUP BY ROLLUP(DEPT_ID, JOB);

TABLEGROUPING 결과6행

DEPT_ID	JOB	SUM_SAL	G_DEPT	G_JOB
D1	MGR	5000	0	0
D1	CLERK	2000	0	0
D1	NULL	7000	0	1
D2	MGR	4500	0	0
D2	NULL	4500	0	1
NULL	NULL	11500	1	1

-- 중간 소계(합 1)를 빼고 상세 3행 + 전체 합계 1행만
SELECT DEPT_ID, JOB, SUM(SAL)
  FROM EMP
 GROUP BY ROLLUP(DEPT_ID, JOB)
HAVING GROUPING(DEPT_ID) + GROUPING(JOB) <> 1;

개념을 불러오는 중…

A, B 두 열일 때 만들어지는 그룹 조합

GROUP BY 뒤

그룹 조합

개수

A, B (기본)

(A, B)

ROLLUP(A, B)

(A, B), (A), ()

3 (n+1)

CUBE(A, B)

(A, B), (A), (B), ()

4 (2ⁿ)

GROUPING SETS(A, B)

(A), (B)

2 (나열한 것만)

DEPT_ID

JOB

SAL

MGR

5000

CLERK

2000

MGR

4500

DEPT

JOB

SAL

MGR

5000

CLERK

2000

MGR

4500

DEPT

JOB

SUM

MGR

5000

CLERK

2000

NULL

7000

MGR

4500

NULL

4500

NULL

11500

-- CUBE: ROLLUP 6행 + 직무별 소계 2행 = 8행 SELECT DEPT_ID, JOB, SUM(SAL) FROM EMP GROUP BY CUBE(DEPT_ID, JOB); -- GROUPING SETS: 부서별, 직무별, 전체만 = 5행 SELECT DEPT_ID, JOB, SUM(SAL) FROM EMP GROUP BY GROUPING SETS((DEPT_ID), (JOB), ());

DEPT

JOB

SUM

MGR

5000

CLERK

2000

NULL

7000

MGR

4500

NULL

4500

NULL

11500

DEPT

JOB

SUM

NULL

MGR

9500

NULL

CLERK

2000

DEPT_ID

JOB

SUM(SAL)

NULL

7000

NULL

4500

NULL

MGR

9500

NULL

CLERK

2000

NULL

11500

-- 위 GROUPING SETS와 같은 결과 SELECT DEPT_ID, NULL, SUM(SAL) FROM EMP GROUP BY DEPT_ID UNION ALL SELECT NULL, JOB, SUM(SAL) FROM EMP GROUP BY JOB UNION ALL SELECT NULL, NULL, SUM(SAL) FROM EMP;

DEPT_ID

JOB

SUM_SAL

G_DEPT

G_JOB

MGR

5000

CLERK

2000

NULL

7000

MGR

4500

NULL

4500

NULL

11500