그룹함수 II (요약값, GROUP BY 함수 - 분석함수)

다음과 같은 쿼리문을 살펴보자.

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

위의 쿼리는 부서번호 별 급여의 합을 구하는 쿼리이다. 이때의 총합을 구하기 위해 GROUP BY 절에 CUBE를 사용할 수도 있다.
CUBE : 주어진 COLUMN들에 대한 모든 부분집합들을 기준으로 그룹함수를 적용한다.

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

단, 위와 같은 경우, NULL값이 2개가 되므로 어느것이 총합이고, 어느것이 NULL인 값에 의한 합인지 명확하지 않다. CUBE를 좀 더 자세히 살펴보기 위해 다음의 쿼리문을 살펴보자

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

위의 결과와 같이 DEPTNO와 JOB에 의한 합, DEPTNO에 의한 합, JOB에 의한 합, 전체에 의한 합이 나옴을 알 수 있다. 하지만 이 결과는 NULL에 의한 명시가 불확실하여 보기에 불편한 단점이 있음을 알 수 있다.

다음 쿼리문은 부서번호와 직위 별 급여의 합을 나타낸다.

SELECT DEPTNO, JOB, SUM(SAL)
FROM EMP
GROUP BY DEPTNO, JOB
ORDER BY 1,2;

이 쿼리문의 GROUP BY절을 ROLLUP(DEPTNO,JOB) 하게 되면, 부서번호와 직위에 의한 합, 그리고 부서번호에 의한 합을 나타내게 된다.
ROLLUP : 입력된 COLUMN들을 좌측을 기준으로 우측에서부터 축소시키며 그룹함수를 적용한다.

SELECT DEPTNO, JOB, SUM(SAL)
FROM EMP
GROUP BY ROLLUP(DEPTNO, JOB)
ORDER BY 1,2;

위의 쿼리문은 결과적으로 DEPTNO와 JOB에 의한 합을 구하고, - 우측항인 JOB을 제외한 - DEPTNO에 의한 합을 구하게 되며, - DEPTNO 역시 제거되게 되므로 - 최종적으로는 전체에 의한 합을 구하게 된다.
GROUPING SETS : 입력받은 COLUMN의 조합을 기준으로만 GROUP BY하여 그룹함수를 적용한다. 즉, 사용자에 의해 입력된 조합들을 순차적으로 GROUP BY하여 결과를 UNION한다고 볼 수 있다.

SELECT DEPTNO, JOB, SUM(SAL)
FROM EMP
GROUP BY GROUPING SETS ((DEPTNO, JOB))
ORDER BY 1,2;

위의 쿼리문의 경우는 GROUPING SETS 을 (DEPTNO,JOB)만 하였기 때문에 결과적으로는 GROUP BY DEPTNO, JOB 인 경우와 결과가 같다. 하지만 이제 2개 이상의 SETS를 입력한 경우를 살펴보자.

SELECT DEPTNO, JOB, SUM(SAL)
FROM EMP
GROUP BY GROUPING SETS ((DEPTNO, JOB),(DEPTNO))
ORDER BY 1,2;

위의 쿼리문의 경우는 SETS에 (DEPTNO)을 입력하였으므로, DEPTNO를 기준으로 GROUP BY 한 결과인 값을 반환한다. 위의 그림에서 JOB의 값이 NULL인 경우가 그 경우이다. 그렇다면 전체를 나타내는 방법은 어떠할까 살펴보자.

SELECT DEPTNO, JOB, SUM(SAL)
FROM EMP
GROUP BY GROUPING SETS ((DEPTNO, JOB),(DEPTNO),())
ORDER BY 1,2;

위의 쿼리문의 SETS에 () 인 조건, 즉, 전체를 대상으로 하는 그룹함수 적용 결과를 보여주게 된다. 하지만, 위의 경우 NULL값에 대한 합으로써의 NULL과 -15번째 RECORD 와 같이 - 전체를 기준으로 한 합인 NULL - 16번째 RECORD - 의 구분이 명확하지 않다. 이는 사람이 볼때에는 직관적으로 판단할 수 는 있으나, 추후 이를 기반으로 한 데이터 처리가 필요할 때에 불편할 수 있으므로, 명확하게 구분할 필요가 있다.
GROUPING : GROUP BY 절에 의해 그룹 지어졌을 때, SELECT 문에서 COLUMN을 받아 사용하여 요약값일 경우 1을, 아닐 경우 0을 반환한다.

SELECT DEPTNO, JOB, SUM(SAL), GROUPING(DEPTNO),GROUPING(JOB)
FROM EMP
GROUP BY GROUPING SETS ((DEPTNO, JOB),(DEPTNO),())
ORDER BY 1,2;

위의 결과로 볼 수있듯이 요약값을 표현하는 값은 1로 나오므로, 이를 조건 비교하여 그때에 따라 NVL을 이용하여 값을 할당하면 손쉽게 처리할 수 있다.

SELECT CASE
    WHEN GROUPING(DEPTNO) = 0 THEN NVL(TO_CHAR(DEPTNO),'인턴')
    ELSE '모든부서' END AS "부서번호" ,
    CASE
    WHEN GROUPING(JOB) = 0 THEN JOB
    ELSE '모든직종' END AS "직종명" ,
    SUM(SAL) "급여 합"
FROM EMP
GROUP BY GROUPING SETS ((DEPTNO, JOB),(DEPTNO),())
ORDER BY 1,2;

GROUPING SETS의 장점으로는 실제 필요한 요약값을 지정하여 입력할 수 있어 불필요한 연산을 하지 않는다. 단, 입력시 주의해야 하는 번거로움은 피할 수 없다.
GROUPING SETS는 직접 지정할 수 있으므로 이전에 살펴본 ROLLUP과 CUBE를 GROUPING SETS로 바꾸어 표현할 수 있다. 

ROLLUP(A,B,C) = GROUPING  SETS ( (A,B,C),(A,B),(A),())
CUBE(A,B,C) =  GROUPING SETS((A,B,C),(A,B),(A,C),(B,C),(A),(B),(C),())

이제 처음 CUBE(DEPTNO, JOB) 로 GROUP BY 할 때의 값을 GROUPING을 이용하여 정리해보자.

SELECT
DECODE(GROUPING(DEPTNO),1,'직업 총계',NVL(TO_CHAR(DEPTNO),'인턴')) "부서NO",
DECODE(GROUPING(JOB),1,'부서 총계',TO_CHAR(JOB)) "직분",
SUM(SAL) "급여"
FROM EMP
GROUP BY CUBE(DEPTNO, JOB)
ORDER BY 1,2;