[SQLD][책없이 공부]2과목 02. SQL 활용_04 그룹함수

1. 그룹함수

- 집계함수를 제외하고 ROLLUP, CUBE, GROUPING SETS

2. ROLLUP

- 소그룹간의 소계 계산. GROUP BY의 확장된 형태 

- GROUP BY 의 칼럼에 대하여 subTotal 만들어줌

- ROLLUP에 지정된 그룹핑 칼럼의 리스트는 소계를 생성하기 위해 사용

- 그룹 잡을 칼럼의 수가 N일 때 SubTotal은 N+1 LEVEL로 생성됨

- ROLLUP 인수는 계층구조! -> 인수의 순서 바뀌면 결과도 바뀌게 됨(먼저나오는 인수가 상위 계층)

SELECT DNAME, JOB COUNT(*) SUM(SAL) FROM EMP, DEPT WHERE DEPT.DEPTNO = EMP.DEPTNO

GROUP BY ROLLUP (DNAME, JOB);

LEVEL 1 : 표준 집계. DNAME(첫번째 인자)으로 나눔

LEVEL 2 : DNAME(첫 인자) 별 JOB(두번째 인자) SubTotal 구함

LEVEL 3 : 총 합계

	LEVEL 2 DNAME별 JOB : 차장 3명 9000만원
	LEVEL 2 DNAME별 JOB : 대리 4명 9000만원
LEVEL1 DNAME : 영업 7명 18000만원
	LEVEL 2 DNAME별 JOB : 차장 5명 20000만원
	LEVEL 2 DNAME별 JOB : 대리 4명 12000만원
LEVEL1 DNAME : 생산 9명 32000만원
		LEVEL 3 전부 합계 16명 50000만원

- 계층 내 정렬을 하려면 ORDER BY로 정렬

3. CUBE

- GROUP BY 항목들 간 다차원적인 소계 계산 (결합가능한 모든 값에 대하 집계 생성) 그러나 부하가 많음

- 표시된 인수들 간에는 평등한 관계라 인수의 순서는 중요하지 않음

- 결과에 대한 정렬이 필요할 시 ORDER BY 절에서 칼럼 명시해야 함

- 그룹 잡을 칼럼의 수가 N일 때 SubTotal은 2^N LEVEL로 생성됨

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

2^2레벨 -> 레벨 4까지의 SUBTOTAL 나온다는 것

LEVEL 1 전체 합계 11000만원
	LEVEL 2-2 JOB별 합계 차장 6000만원
	LEVEL 2-2 JOB별 합계 대리 5000만원
	LEVEL 2-1 DEPTNO별 합계 영업부 11000만원
		LEVEL 4  DEPTNO별 JOB별 합계 영업_차장 3000만원
		LEVEL 4  DEPTNO별 JOB별 합계 영업_대리 2500만원
		LEVEL 4  DEPTNO별 JOB별 합계 생산_차장 3000만원
		LEVEL 4  DEPTNO별 JOB별 합계 생산_대리 2500만원

4. GROUPING SETS

- 특정항목에 대한 소계 계산-> 원하는 부분의 소계만 추출 가능

- GROUP BY에 나오는 칼럼의 순서와 상관없이 다양한 소계 생성

- 인자간 평등한 관계이며, 소계를 모두 개별적으로 처리

- UNION ALL을 사용한 일반 그룹함수 사용 SQL과 같은 결과가 나옴

SELECT DEPNO, JOB, SUM(SAL) FROM EMP GROUP BY GROUPING SETS(DEPTNO, JOB);

LEVEL 1 JOB별 합계 차장 6000만원

LEVEL 1 JOB별 합계 대리 5000만원

LEVEL 1 DEPTNO별 합계 영업 6000만원

LEVEL 1 DEPTNO별 합계 생산 5000만원

5. GROUPING

- ROLLUP CUBE GROUPING SET에서 생성되는 합계 값을 구분하기 위해 만들어진 함수

- ROLLUP 이나 CUBE 등으로 소계가 계산된 결과에는 GROUPING(칼럼명) = 1이 반환, 그 외의 결과는 0 반환

- GROUPING의 반환 값을 DECODE나 CASE 문으로 식별해서 SELECT문으로 필드에 원하는 문자열 지정할 수 있음

SELECT 

CASE GROUPING(DNAME) WHEN 1 THEN 'DEPARTMENT TATAL' ELSE DNAEM END AS DNAME, 

CASE GROUPING(JOB) WHEN 1 THEN 'JOB TOTAL' ELSE JOB END AS JOB, COUNT(*), SUM(SAL)

FROM EMP, DEPT WHERE EMP.DEPTNO = DEPT.DEPTNO

GROUP BY ROLLUP(DNAME, JOB);

SELECT

 DECODE(GROUPING(DNAME), 1, 'DEPARTMENT TOTAL', DNAME) AS DNAME,

 DECODE(GROUPIN(JOB), 1, 'JOB TOTAL', JOB) AS JOB, COUNT(*), SUM(SAL)

FROM EMP, DEPT WHERE EMP.DEPTNO = DEPT.DEPTNO

GROUP BY GROUPING SETS (DNAME, JOB);