[SQL] 그룹 함수 Group Function (ROLLUP, CUBE, GROUPING SETS)
분석 함수
SQL 표준은 데이터 분석을 위해 다음 세 가지 함수를 정의하고 있다.
- AGGREGATE FUNCTION
- GROUP FUNCTION
- WINDOW FUNCTION
AGGREGATE FUNCTION
GROUP AGGREGATE FUNCTION 라고도 부르는 그룹 함수의 한 부류이다.
COUNT, SUM, AVG, MAX, MIN 등이 포함되어 있다.
GROUP FUNCTION
리포트를 작성할 때 사용하기 좋은 함수이다.
소계, 중계, 합계, 총 합계 등을 구할 수 있다.
소그룹 간의 소계를 계산하는 ROLLUP, GROUP BY, 다차원적 소계를 할 수 있는 CUBE, 특정 항목의 소계를 할 수 있는 GROUPING SETS 함수가 있다.
WINDOW FUNCTION
분석 함수 (ANALYTIC FUNCTION)나 순위 함수 (RANK FUNCTION) 로도 알려져 있다.
ROLLUP
ROLLUP 에 지정된 그룹핑 컬럼이 N개 라고 하면 N+1 의 subtotal 이 생성된다.
계층구조라서 인수 순서가 바뀌면 결과도 바뀌게 되므로 주의!
SELECT DNAME, JOB, COUNT(*) "TOTAL EMPL", SUM(SAL) "TOTAL SAL"
FROM EMP, DEPT
WHERE DEPT.DEPTNO = EMP.DEPTNO
GROUP BY ROLLUP (DNAME, JOB)
그룹핑 컬럼인 DNAME, JOB에 대해서 다음과 같은 LEVEL의 추가 집계가 생성된다.
- L1 - 그룹 바이 수행시 성생되는 표준 집계
- L2 - DNAME 별 모든 JOB의 SUBTOTAL
- L3 - GRAND TOTAL
롤업의 경우 계층 간 집계에 대해서는 레벨별 순서를 정렬하지만,
계층 내 그룹 바이 수행 시 생성되는 표준 집계에 대해서는 별도 정렬을 지원하지 않는다.
정렬을 원하면 ORDER BY를 사용하자.
GROUPING
ROLLUP, CUBE, GROUPING SETS와 함께 사용할 수 있는 함수.
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)
GROUPING(DNAME), GROUPING(JOB) <- 요 컬럼에 0이면 집계값이 아니다, 1이면 집계값이다를 표시해준다.
JOB과 MGR는 하나의 집합으로 간주하고, 부서별, JOB & MGR 에 대한 ROLLUP 결과를 출력하는 예제
SELECT DNAME,
JOB,
MGR,
SUM(SAL) "TOTAL SAL"
FROM EMP, DEPT
WHERE DEPT.DEPTNO = EMP.DEPTNO
GROUP BY ROLLUP (DNAME, (JOB, MGR)) ;
-- JOB, MGR을 소계 시 하나의집합으로 간주함.
CUBE
CUBE는 결합 가능한 모든 값에 대해서 다차원 집계를 생성한다.
그룹핑 컬럼의 수가 N 개 라면 2의 N승만큼 subtotal을 생성한다.
ROLLUP에 비해 연산 대상이 많으므로 주의하자.
GROUP BY ROLLUP 처럼 GROUP BY CUBE 로 사용하면 된다.
GROUPING SETS
GROUPING SETS는 표시된 인수들에 대한 개별 집계를 구할 수 있다.
kdata 한국데이터진흥원에서 출간한 SQL 전문가 가이드 2013 Edition을 요약했습니다.