2-2-3. 그룹 함수

1. ROLLUP

1️⃣ ROLLUP 함수의 기초 개념

1) ROLLUP 함수

• SQL에서 그룹화된 데이터의 다차원 집계를 생성하는 데 사용됨
• 그룹화된 데이터의 다차원 집계(Multidimensional Aggregation)
• 다양한 수준의 데이터 요약을 한 번의 쿼리로 계산하는 방법
• 데이터의 특정 그루봐 기준에 따라 요약(집계)하는 작업을 여러 차원에서 수행하며,
주로 데이터 분석과 비즈니스 인텔리전스(BI)에서 사용됨

• GROUP BY 절과 함께 사용되며, 지정된 칼럼에 대한 소계와 합계를 계산하여 계층 구조 형성
→ 다양한 수준에서의 집계 결과를 한 번의 쿼리로 얻을 수 있음
계층 구조의 각 단계에서 소계(subtotal)와 전체 합계(grand total)계산

• 다차원 분석(Multi-dimensional Analysis)과 데이터 요약에 유용

2️⃣ ROLLUP 함수의 형식과 원리

1) ROLLUP 함수의 형식

• GROUP BY 뒤에 간단히 ROLLUP(칼럼명#1, 칼럼명#2, 칼럼명#3, …) 을 적으면 됨

2) ROLLUP 함수의 예

SELECT DEPTNO, JOB, SUM(SAL) AS TOTAL_SALARY
FROM EMP
GROUP BY ROLLUP(DEPTNO, JOB)
ORDER BY DEPTNO;

• ROLLUP 적용 전 : GROUP BY 적용

SELECT DEPTNO, JOB, SUM(SAL) AS TOTAL_SALARY
FROM EMP
GROUP BY DEPTNO, JOB
ORDER BY DEPTNO;

DEPTNO	JOB	TOTAL_SALARY
10	CLERK	1300
10	MANAGER	2450
10	PRESIDENT	5000
20	ANALYST	6000
20	CLERK	1900
20	MANAGER	2975
30	CLERK	950
30	MANAGER	2850
30	SLAESMAN	5600

• ROLLUP 적용 후 : GROUP BY ROLLUP 적용

DEPTNO	JOB	TOTAL_SALARY
10	CLERK	1300
10	MANAGER	2450
10	PRESIDENT	5000
10	NULL	8750
20	ANALYST	6000
20	CLERK	1900
20	MANAGER	2975
20	NULL	10875
30	CLERK	950
30	MANAGER	2850
30	SLAESMAN	5600
30	NULL	9400
NULL	NULL	29025

• DEPTNO가 10인 부서의 JOB 소계 8750
+ DEPTNO가 20인 부서의 JOB 소계 10875
+ DEPTNO가 30인 부서의 JOB 소계 9400
= DEPTNO 전체의 SAL 합계 29025

2. CUBE

1️⃣ CUBE 함수의 기초 개념

1) CUBE 함수

• SQL에서 다차원 집계 생성하는 데 사용됨

• GROUP BY 절과 함께 사용되며, 지정된 칼럼에 대한 모든 가능한 조합의 소계와 합계 계산

• ROLLUP 함수보다 소계 및 합계를 집계하는 경우의 수가 훨씬 다양함

• 다차원 데이터 분석과 복잡한 집계를 쉽게 수행할 수 있음

※ ROLLUP 함수 vs CUBE 함수

2️⃣ CUBE 함수의 원리

1) CUBE 함수의 형식

• GROUP BY 뒤에 간단히 CUBE(칼럼명#1, 칼럼명#2, 칼럼명#3, …) 을 적으면 됨

2) CUBE 함수의 예

SELECT DEPTNO, JOB, SUM(SAL) AS TOTAL_SALARY
FROM EMP
GROUP BY CUBE(DEPTNO, JOB)
ORDER BY DEPTNO;

• CUBE 적용 후 : GROUP BY CUBE 적용

DEPTNO	JOB	TOTAL_SALARY
10	CLERK	1300
10	MANAGER	2450
10	PRESIDENT	5000
10	NULL	8750
20	ANALYST	6000
20	CLERK	1900
20	MANAGER	2975
20	NULL	10875
30	CLERK	950
30	MANAGER	2850
30	SLAESMAN	5600
30	NULL	9400
NULL	ANALYST	6000
NULL	CLERK	4150
NULL	MANAGER	8275
NULL	PRESIDENT	5000
NULL	SALESMAN	5600
NULL	NULL	29025

• JOB별 SAL 값을 전체 집계한 값도 출력

3. GROUPING SETS

1️⃣ GROUPING SETS 함수의 기초 개념

1) GROUPING SETS 함수

• 여러 그룹화 집합을 개별적으로 지정하여 그룹화된 데이터를 다차원적으로 집계하는 데 사용됨

• GROUP BY 절에서 특정 칼럼의 조합으로 데이터를 그룹화하여 다양한 집계 결과 생성

• ROLLUP과 CUBE의 일부 기능을 제공하면서 더 많은 제어 가능

• ROLLUP이나 CUBE처럼 정해진 조합만 사용하는 것이 아니라, 사용자가 임의로 그룹화셋을 만들어 지정할 수 있음

2️⃣ GROUPING SETS 함수

1) GROUPING SETS 함수의 형식

• GROUP BY 절 다음에 GROUPING SETS를 지정하는 형식

• 괄호 안에 또 다른 괄호의 묶음을 지정하는데, 이 괄호의 묶음이 하나의 그룹을 의미

SELECT 칼럼1, 칼럼2, ..., 집계 함수(칼럼)
FROM 테이블
GROUP BY GROUPING SETS (
											  (칼럼1, 칼럼2,),   // 칼럼1과 칼럼2 (하나의 그룹별 합계)
											  (칼럼1),           // 칼럼1별 합계
											  (칼럼2),           // 칼럼2별 합계
											  ()                 // 전체 합계
										   );

2) GROUPING SETS 함수의 예

SELECT DEPTNO, JOB, SUM(SAL) AS TOTAL_SALARY
FROM EMP
GROUP BY GROUPING SETS (
												(DEPTNO, JOB),
												(DEPTNO),
												(JOB),
												()
												)
ORDER BY DEPTNO, JOB;

4. GROUPING

1️⃣ GROUPING 함수의 기초 개념

1) GROUPING 함수

• GROUP BY 절에서 ROLLUP, CUBE, GROUPING SETS와 함께 사용되어 각 행이 소계 또는 총계에 속하는지 여부 나타냄

• ROLLUP, CUBE, CROUPING SETS 등의 함수는 집계 칼럼 값이 NULL로 출력 → 무엇이 소계이고, 무엇이 합계인지 알 수 없음 → 해당 함수 사용 시 그룹화한 칼럼을 SELECT 절에 GROUPING 함수로 짖ㅇ하여 그룹화 기준을 알 수 있게 함

• SQL에서 데이터 그룹화할 때 사용되며, 주로 GROUP BY 절과 함께 사용됨

• 각 그룹 수준에서 0 또는 1 반환
• 0 : 집계 수준 의미
• 1 : 해당 수준이 그룹화되지 않았음을 의미

2️⃣ GROUPING 함수 형식

1) GROUPING 함수의 형식

• SELECT 절에서 사용됨

• 괄호로 칼럼명 묶어줌

2) GROUPING 함수의 예

SELECT DEPTNO, JOB, SUM(SAL) AS TOTAL_SALARY,
			 GROUPING(DEPTNO) AS DEPT_GROUPING, GROUPING(JOB) AS JOB_GROUPING
FROM EMP
GROUP BY GROUPING SETS (
												(DEPTNO, JOB),
												(DEPTNO),
												(JOB),
												()
												)
ORDER BY DEPTNO, JOB;

5. 그룹 함수와 CASE 문 사용

1️⃣ 그룹 함수에서 결과 가독성 높이기

1) 그룹 함수의 단점

• SQL에서 계층적 집계를 생성하는 데 사용되며, 이를 통해 집계 레코드를 포함한 다양한 집계 수준을 동시에 얻을 수 있음

• 하지만, 집계 시 NULL로 표시되어 가독성이 좋지 않음

• GROUPING(칼럼명) 함수를 사용하면 어떤 칼럼의 집계값인지 알 수 있지만, 0과 1로 표시되기 때문에 한눈에 알아보기 어려움

• 임의의 텍스트 칼럼명을 지정하여 무엇에 대한 집계값인지 출력하면, 결과를 훨씬 쉽계 알아볼 수 있음

2) 단점 보완

• 그룹 함수에서 임의의 텍스트 지정하려면 CASE 문 사용

• 집계값을 구분하는 칼럼은 GROUPING(칼럼명) 함수의 결과 칼럼
• 0과 1로 출력되는 GROUPING(칼럼명) 함수를 CASE 조건으로 사용하여 무엇에 대한 집계값인지 출력

2️⃣ 그룹 함수와 CASE 문 사용

1) ROLLUP, GROUPING, CASE 함수를 함께 사용한 예

SELECT DEPTNO, JOB, SUM(SAL) AS TOTAL_SALARY
			 GROUPING(DEPTNO) DEPTNO_GROUP, GROUPING(JOB) JOB_GROUP
			 CASE
				 WHEN GROUPING(DEPTNO) = 1 AND GROUPING(JOB) = 1 THEN 'TOTAL_SUM'
				 WHEN GROUPING(DEPTNO) = 0 AND GROUPING(JOB) = 1 THEN 'DEPT_GROUPING'
				 WHEN GROUPING(DEPTNO) = 0 AND GROUPING(JOB) = 0 THEN 'DEPT_JOB_GROUPING'
			 END AS GROUPING_LEVEL
FROM EMP
GROUP BY ROLLUP(DEPTNO, JOB)
ORDER BY DEPTNO, JOB;

3️⃣ 그룹 함수와 DECODE 함수 사용

1) DECODE 함수 (오라클)

• 조건에 따라 값을 반환하는 함수

• 그룹 함수뿐만 아니라 모든 쿼리문에서 CASE 문과 같은 역할을 하며 다양하게 활용됨

2) DECODE 함수 형식

DECODE(expression, search1, result1, search2, result2, ..., default)

• expression : 평가할 값

• search : 비교할 값(조건)

• result : search 값과 expression이 일치할 때 반환할 값

• default : 모든 search 값과 일치하지 않을 때 반환할 기본 값(생략 가능)

3) DECODE 함수의 예

• CASE 문

CASE
		WHEN GROUPING(DEPTNO) = 1 AND GROUPING(JOB) = 1 THEN 'TOTAL_SUM'
		WHEN GROUPING(DEPTNO) = 0 AND GROUPING(JOB) = 1 THEN 'DEPT_GROUPING'
		WHEN GROUPING(DEPTNO) = 0 AND GROUPING(JOB) = 0 THEN 'DEPT_JOB_GROUPING'
END AS GROUPING_LEVEL

• DECODE 함수

DECODE (
				GROUPING(DEPTNO) + GROUPING(JOB),
				2, 'TOTAL_SUM',
				1, DECODE(GROUPING(DEPTNO), 1, 'JOB_GROUPING', 'DEPT_GROUPING'),
				0, 'DEPT_JOB_GROUPING'
				) AS GROUPING_LEVEL

• 코드를 간결하게 작성할 수 있음