[SQL] 그룹 소계 함수 및 예제 (ROLLUP, CUBE)

안녕하세요.

오늘은 SQL 그룹 소계 함수의 종류와 예제에 대해 정리해보겠습니다.

 

 SQL 그룹 소계 함수와 그 예제

▣ SQL 그룹 소계 함수

- 소그룹 간의 소계를 계산하는 ROLLUP, GROUP BY 항목들간 다차원적인 소계를 계산하는 CUBE, 특정항목에 대한 소계를 계산하는 GROUPING SETS 함수
- GROUP BY 절과 함께 쓰이며 묶은 해당 컬럼들을 기준으로 그룹화함

 

※ 이외 그룹 집계 함수에 대하여 포스팅한 글이 있으니 관심 있으신 분들은 참고하시기 바랍니다.

[MySQL] 그룹 함수 다루기

 

: 예시 데이터 emp

empno	ename	job	sal	city
7902	FORD	ANALYST	3000	Seoul
7788	SCOTT	ANALYST	3000	Tokyo
7369	SMITH	CLERK	800	Seoul
7900	JAMES	CLERK	950	Tokyo
7876	ADAMS	CLERK	1100	Paris
7934	MILLER	CLERK	1300	Paris
7782	CLARK	MANAGER	2450	Rome
7698	BLAKE	MANAGER	2850	Seoul
7566	JONES	MANAGER	2975	Rome
7839	KING	PRESIDENT	5000	Tokyo
7654	MARTIN	SALESMAN	1250	Rome
7521	WARD	SALESMAN	1250	Seoul
7844	TURNER	SALESMAN	1500	Seoul
7499	ALLEN	SALESMAN	1600	Tokyo

 

1) ROLLUP

GROUP BY ROLLUP(COL1, COL2…) OR GROUP BY COL1, COL2… WITH ROLLUP

- 오른쪽 → 왼쪽 순으로 그룹을 묶어 소계를 계산해주는 함수

 

SELECT job, city, SUM(sal) AS sum_sal, AVG(sal) AS avg_sal
FROM emp
GROUP BY ROLLUP(city, job);

: 결과보기

job	city	sum_sal	avg_sal
CLERK	Paris	2400	1200
(null)	Paris	2400	1200
MANAGER	Rome	5425	2712
SALESMAN	Rome	1250	1250
(null)	Rome	6675	2225
ANALYST	Seoul	3000	3000
CLERK	Seoul	800	800
MANAGER	Seoul	2850	2850
SALESMAN	Seoul	2750	1375
(null)	Seoul	9400	1880
ANALYST	Tokyo	3000	3000
CLERK	Tokyo	950	950
PRESIDENT	Tokyo	5000	5000
SALESMAN	Tokyo	1600	1600
(null)	Tokyo	10550	2637
(null)	(null)	29025	2073

2) CUBE

GROUP BY CUBE(COL1, COL2…) OR GROUP BY COL1, COL2… WITH CUBE

- 모든 경우의 수로 그룹을 묶어 소계를 계산해주는 함수

SELECT job, city, SUM(sal) AS sum_sal, AVG(sal) AS avg_sal
FROM emp
GROUP BY CUBE(city, job);

 

: 결과보기

job	city	sum_sal	avg_sal
ANALYST	Seoul	3000	3000
ANALYST	Tokyo	3000	3000
ANALYST	(null)	6000	3000
CLERK	Paris	2400	1200
CLERK	Seoul	800	800
CLERK	Tokyo	950	950
CLERK	(null)	4150	1037
MANAGER	Rome	5425	2712
MANAGER	Seoul	2850	2850
MANAGER	(null)	8275	2758
PRESIDENT	Tokyo	5000	5000
PRESIDENT	(null)	5000	5000
SALESMAN	Rome	1250	1250
SALESMAN	Seoul	2750	1375
SALESMAN	Tokyo	1600	1600
SALESMAN	(null)	5600	1400
(null)	(null)	29025	2073
(null)	Paris	2400	1200
(null)	Rome	6675	2225
(null)	Seoul	9400	1880
(null)	Tokyo	10550	2637

3) GROUPING SETS

GROUP BY GROUPING SETS(COL1, COL2…)

- 개별 그룹으로 묶어 소계만 계산해주는 함수

SELECT job, city, SUM(sal) AS sum_sal, AVG(sal) AS avg_sal
FROM emp
GROUP BY GROUPING SETS(city, job);

 

: 결과보기

job	city	sum_sal	avg_sal
ANALYST	(null)	6000	3000
CLERK	(null)	4150	1037
MANAGER	(null)	8275	2758
PRESIDENT	(null)	5000	5000
SALESMAN	(null)	5600	1400
(null)	Paris	2400	1200
(null)	Rome	6675	2225
(null)	Seoul	9400	1880
(null)	Tokyo	10550	2637

4) GROUPING()
- 추가로 위의 예시들에서 NULL 값이 나오는게 싫다면 GROUPING() 함수*와 CASE WHEN 절을 섞으면 깔끔하게 조회할 수 있음
* ROLLUP 또는 CUBE 함께 사용되며 그룹화되면 0, 아니면 1 반환
- 쉽게 표현하면, NULL 부분에만 1 반환됨

 

SELECT CASE WHEN GROUPING(job) = 1 THEN '計' ELSE job END AS '직업_총계',
CASE WHEN GROUPING(city) = 1 THEN '計' ELSE city END AS '도시_총계',
SUM(sal) AS '급여_합계'
FROM emp
GROUP BY ROLLUP(job, city)

 

: 결과보기

직업_총계	도시_총계	급여_합계
ANALYST	Seoul	3000
ANALYST	Tokyo	3000
ANALYST	計	6000
CLERK	Paris	2400
CLERK	Seoul	800
CLERK	Tokyo	950
CLERK	計	4150
MANAGER	Rome	5425
MANAGER	Seoul	2850
MANAGER	計	8275
PRESIDENT	Tokyo	5000
PRESIDENT	計	5000
SALESMAN	Rome	1250
SALESMAN	Seoul	2750
SALESMAN	Tokyo	1600
SALESMAN	計	5600
計	計	29025