[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을 요약했습니다.