47. Grouping Function (Rollup, Cube, Grouping Set)에 대해 이해하고 있다.

Grouping Function

1. ROLLUP 함수

STEP 1. 일반적인 GROUP BY 절 사용

[예제] 부서명과 업무명을 기준으로 사원수와 급여 합을 집계한 일반적인 GROUP BY SQL 문장을 수행한다.

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

STEP 1-2. GROUP BY 절 + ORDER B Y 절 사용 

[예제] 부서명과 업무명을 기준으로 집계한 일반적인 GROUP BY SQL 문장에 ORDER BY 절을 사용함으로써 부서, 업무별로 정렬이 이루어진다.

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

STEP 2. ROLLUP 함수 사용 

[예제] 부서명과 업무명을 기준으로 집계한 일반적인 GROUP BY SQL 문장에 ROLLUP 함수를 사용한다.

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

실행 결과에서 2개의 GROUPING COLUMNS(DNAME, JOB)에 대하여 다음과 같은 추가 LEVEL의 집계가 생성된 것을 볼 수 있다.

L1 - GROUP BY 수행시 생성되는 표준 집계 (9건)
L2 - DNAME 별 모든 JOB의 SUBTOTAL (3건)
L3 - GRAND TOTAL (마지막 행, 1건)

추가로 ROLLUP의 경우 계층 간 집계에 대해서는 LEVEL 별 순서(L1→L2→L3)를 정렬하지만, 계층 내 GROUP BY 수행시 생성되는 표준 집계에는 별도의 정렬을 지원하지 않는다.

L1, L2, L3 계층 내 정렬을 위해서는 별도의 ORDER BY 절을 사용해야 한다.

STEP 2-2. ROLLUP 함수 + ORDER BY 절 사용 

[예제] 부서명과 업무명을 기준으로 집계한 일반적인 GROUP BY SQL 문장에 ROLLUP 함수를 사용한다. 추가로 ORDER BY 절을 사용해서 부서, 업무별로 정렬한다.

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

STEP 3. GROUPING 함수 사용 

ROLLUP, CUBE, GROUPING SETS 등 새로운 그룹 함수를 지원하기 위해 GROUPING 함수가 추가되었다.

ROLLUP 이나 CUBE에 의한 소계가 계산된 결과에는 GROUPING(EXRP) = 1 이 표시되고,

그 이외 결과에는 GROUPING(EXPR = 0)이 표시된다.

GROUPING 함수와 CASE/DECODE 를 이용해, 소계를 나타내는 필드에 원하는 문자열을 지정할 수 있어, 보고서 작성시 유용하게 사용할 수 있다.

[예제] ROLLUP 함수를 추가한 집계 보고서에서 집계 레코드를 구분할 수 있는 GROUPPING 함수가 추가된 SQL 문장이다.

[예제]
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 함수가 1을 리턴한 것을 확인할 수 있다.
그리고 전체 합계를 나타내는 결과 라인에서는 부서별 GROUPING함수와 업무별 GROUPING 함수가 둘 다 1인 것을 알 수 있다.

STEP 4. GROUPING 함수 + CASE 사용 

[예제] ROLLUP 함수를 추가한 집계 보고서에서 집계 레코드를 구분할 수 있는 GROUPPING 함수가 추가된 SQL 문장이다. 

[예제]
SELECT CASE GROUPING(DNAME) 
        WHEN 1 THEN 'All Departmants' ELSE DNAME END DNAME,
       CASE GROUPING(JOB)
        WHEN 1 THEN 'All Jobs' ELSE JOB END JOB,
       COUNT(*) "Total Empl",
       SUM(SAL) "Total Sal"
FROM   EMP, DEPT
WHERE  DEPT.DEPTNO = EMP.DEPTNO
GROUP BY ROLLUP(DNAME, JOB);

부서별과 전체 집계를 표시한 레코드에서 'ALL JOBS'와 'ALL DEPARTMENTS'라는 사용자 정의 텍스트를 확인할 수 있다. 일부 DBMS는 GROUPING_ID라는 비슷한 용도의 함수를 추가로 사용할 수도 있으므로 참조하기 바란다.

STEP 4-2. ROLLUP 함수 일부 사용

[예제] GROUP BY ROLLUP(DNAME, JOB) 조건에서 GROUP BY DNAME, ROLLUP(JOB) 조건으로 변경한 경우이다.

[예제]
SELECT CASE GROUPING(DNAME) 
        WHEN 1 THEN 'All Departmants' ELSE DNAME END DNAME,
       CASE GROUPING(JOB)
        WHEN 1 THEN 'All Jobs' ELSE JOB END JOB,
       COUNT(*) "Total Empl",
       SUM(SAL) "Total Sal"
FROM   EMP, DEPT
WHERE  DEPT.DEPTNO = EMP.DEPTNO
GROUP BY DNAME, ROLLUP(JOB);

결과는 마지막 ALL DEPARTMENTS & ALL JOBS 줄만 계산이 되지 않았다. ROLLUP이 JOB 칼럼에만 사용되었기 때문에 DNAME에 대한 집계는 필요하지 않기 때문이다.

STEP 4-2. ROLLUP 함수 결합 칼럼 사용

[예제] 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을 소계시 하나의 집합으로 간주하여 구분하지 않음

ROLLUP 함수 사용시 괄호로 묶은 JOB과 MGR의 경우 하나의 집합(JOB+MGR) 칼럼으로 간주하여 괄호 내 각 칼럼별 집계를 구하지 않는다.

2. CUBE 함수

ROLLUP에서는 단지 가능한 Subtotal만을 생성하였지만, CUBE는 결합 가능한 모든 값에 대하여 다차원 집계를 생성한다. CUBE를 사용할 경우에는 내부적으로는 Grouping Columns의 순서를 바꿔어서 또 한 번의 Query를 추가 수행해야 한다. 뿐만 아니라 Grand Total은 양쪽의 Query 에서 모두 생성이 되므로 한 번의 Query에서는 제거 되어야만 하므로 ROLLUP에 비해 시스템의 연산 대상이 많다.
이처럼 Grouping Columns이 가질 수 있는 모든 경우에 대하여 Subtotal을 생성해야 하는 경우에는 CUBE를 사용하는 것이 바람직하나, ROLLUP에 비해 시스템에 많은 부담을 주므로 사용에 주의해야 한다.
CUBE 함수의 경우 표시된 인수들에 대한 계층별 집계를 구할 수 있으며, 이때 표시된 인수들 간에는 계층구조인 ROLLUP과는 달리 평등한 관계이므로 인수의 순서가 바뀌는 경우 행간에 정렬 순서는 바뀔 수 있어도 데이터 결과는 같다.
그리고 CUBE도 결과에 대한 정렬이 필요한 경우 ORDER BY 절에 명시적으로 정렬 칼럼이 표시가 되어야 한다.

STEP 5. CUBE 함수 이용

[예제] GROUP BY ROLLUP (DNAME, JOB) 조건에서 GROUP BY CUBE (DNAME, JOB) 조건으로 변경해서 수행한다.

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

CUBE는 GROUPING COLUMNS이 가질 수 있는 모든 경우의 수에 대하여 Subtotal을 생성하므로 GROUPING COLUMNS의 수가 N이라고 가정하면, 2의 N승 LEVEL의 Subtotal을 생성하게 된다.
실행 결과에서 CUBE 함수 사용으로 ROLLUP 함수의 결과에다 업무별 집계까지 추가해서 출력할 수 있는데, ROLLUP 함수에 비해 업무별 집계를 표시한 5건의 레코드가 추가된 것을 확인할 수 있다.

3. GROUPING SETS 함수