그룹함수(Group Function)

그룹함수(Group Function) 란?

그룹 함수(집계 함수)는 특정 칼럼의 값들을 그룹화하여 하나의 결과를 반환하는 함수이다. 이들 함수는 주로 여러 행의 데이터에 대한 통계적 계산을 수행할 때 사용되며, GROUP BY 절과 함께 사용되어 여러 행의 그룹별로 결과를 제공한다. 주로 결산 개념의 업무를 가지는 원가나 판매 시스템의 경우에 소계, 중계, 합계, 총 합계 등의 여러 레벨의 결산 보고서를 만드는 업무를 할 때 유용하게 사용된다.

 

 

 

ROLLUP 함수

• 지정된 컬럼 목록에 대해 계층적인 서브토탈을 생성
• 일반적으로 주어진 컬럼의 모든 가능한 조합에 대한 서브토탈과 그랜드토탈을 계산하는데 사용

SELECT 	DNAME, JOB,
	COUNT(*) "Total Empl",
	SUM(SAL) "Total Sal"
FROM EMP, DEPT
WHERE DEPT.DEPTNO = EMP.DEPTNO
GROUP BY DNAME, JOB;

[실행 결과] 
DNAME 		JOB 		Total Empl 	Total Sal
----- 		---- 		-------- 	--------
SALES 		CLERK 		1 		950
SALES 		MANAGER 	1 		2850
SALES 		SALESMAN 	4 		5600
SALES 				6 		9400
RESEARCH 	CLERK 		2 		1900
RESEARCH 	ANALYST 	2 		6000
RESEARCH 	MANAGER 	1 		2975
RESEARCH 			5 		10875
ACCOUNTING 	CLERK 		1 		1300
ACCOUNTING 	MANAGER 	1 		2450
ACCOUNTING 	PRESIDENT 	1 		5000
ACCOUNTING 			3 		8750

 

 

 

GROUPING 함수

• ROLLUP이나 CUBE에 의한 소계가 계산된 결과에는 GROUPING(EXPR) = 1 이 표시되고, 그 외의 결과에는 GROUPING(EXPR) = 0 이 표시된다.

SELECT 	DNAME, GROUPING(DNAME),
	JOB, GROUPING(JOB),
	COUNT(*) "Total Empl",
	SUM(SAL) "Total Sal"
FROM EMP, DEPT
WHERE DEPT.DEPTNO = EMP.DEPTNO
GROUP BY ROLLUP (DNAME, JOB);

DNAME 	GROUPING(DNAME) JOB 	GROUPING(JOB) 	Total Empl 	Total Sal
------ 	-------------- 	--- 	----------- 	-------- 	------
SALES 		0 	CLERK 		0 	1 		950
SALES 		0 	MANAGER 	0 	1 		2850
SALES 		0 	SALESMAN 	0 	4 		5600
SALES 		0 			1 	6 		9400
RESEARCH 	0 	CLERK 		0 	2 		1900
RESEARCH 	0 	ANALYST 	0 	2 		6000
RESEARCH 	0 	MANAGER 	0 	1 		2975
RESEARCH 	0 			1 	5 		10875
ACCOUNTING 	0 	CLERK 		0 	1 		1300
ACCOUNTING 	0 	MANAGER 	0 	1 		2450
ACCOUNTING 	0 	PRESIDENT 	0 	1 		5000
ACCOUNTING 	0 			1 	3 		8750
		1 			1 	14 		29025

 

 

 

CUBE 함수

• ROLLUP에서는 단지 가능한 Subtotal만을 생성하였지만, CUBE는 결합 가능한 모든 값에 대하여 다차원 집계를 생성
• CUBE를 사용할 경우에는 내부적으로 Grouping Columns의 순서를 바꾸어서 또 한 번의 Query를 추가 수행
• 뿐만 아니라 Grand Total은 양쪽의 쿼리에서 모두 생성이 되므로 한 번의 쿼리에서는 제거되어야만 하므로 ROLLUP에 비해 시스템의 연산 대상이 많음

SELECT 	CASE GROUPING(DNAME) WHEN 1 THEN 'All Departments' ELSE DNAME END AS DNAME,
	CASE GROUPING(JOB) WHEN 1 THEN 'All Jobs' ELSE JOB END AS JOB,
COUNT(*) "Total Empl",
SUM(SAL) "Total Sal"
FROM EMP, DEPT
WHERE DEPT.DEPTNO = EMP.DEPTNO
GROUP BY CUBE (DNAME, JOB) ;

DNAME 			JOB Total 	Empl Total 	Sal
------------- 		--------- 	--------- 	--------
All Departments 	All Jobs 	14 		29025
All Departments 	CLERK 		4 		4150
All Departments 	ANALYST 	2 		6000
All Departments 	MANAGER 	3 		8275
All Departments 	SALESMAN 	4 		5600
All Departments 	PRESIDENT 	1 		5000
SALES 			All Jobs 	6 		9400
SALES 			CLERK 		1 		950
SALES 			MANAGER 	1 		2850
SALES 			SALESMAN 	4 		5600
RESEARCH 		All Jobs 	5 		10875
RESEARCH 		CLERK 		2 		1900
RESEARCH 		ANALYST 	2 		6000
RESEARCH 		MANAGER 	1 		2975
ACCOUNTING 		All Jobs 	3 		8750
ACCOUNTING 		CLERK 		1 		1300
ACCOUNTING 		MANAGER 	1 		2450
ACCOUNTING 		PRESIDENT 	1 		5000

 

 

 

 

GROUPING SETS 함수

• GROUPING SETS는 특정 그룹화 조합만을 명시적으로 지정할 때 사용
• GROUP BY 문장을 여러 번 반복하지 않아도 원하는 결과를 쉽게 얻을 수 있음.

SELECT 	DECODE(GROUPING(DNAME), 1, 'All Departments', DNAME) AS DNAME,
	DECODE(GROUPING(JOB), 1, 'All Jobs', JOB) AS JOB,
COUNT(*) "Total Empl",
SUM(SAL) "Total Sal"
FROM EMP, DEPT
WHERE DEPT.DEPTNO = EMP.DEPTNO
GROUP BY GROUPING SETS (DNAME, JOB);

DNAME 		JOB Total 	Empl Total 	Sal
------------ 	--------- 	------- 	------
All Departments CLERK 		4 		4150
All Departments SALESMAN 	4 		5600
All Departments PRESIDENT 	1 		5000
All Departments MANAGER 	3 		8275
All Departments ANALYST 	2 		6000
ACCOUNTING 	All Jobs 	3 		8750
RESEARCH 	All Jobs 	5 		10875
SALES 		All Jobs 	6 		9400