Oracle Rollup 소개. 단계별 소계, 합계를 구할때 편리한 기능

예전에 대용량 데이터베이스 솔루션 같은 책을 보면,
"단계별로 소계, 합계를 구하를 Query를 만들면서,
단, 테이블을 2번 읽지 않고 한번만 읽고 작성하라."
라는 문제를 많이 접했습니다.

그래서 아래와 같은 Table에서

SELECT * FROM SCOTT.EMP;

각각의 부서별 JOB (CLERK, MANAGER, 기타의 3개로 나누어) 의 소계와 합계,
그리고 전체의 합계를 구하는 Query를 만들기 위해서는
일단...
UNION ALL을 이용해서 Table을 2번 읽어서는 쉽게 가능한데...

SELECT MAX(D.DNAME) AS DEPT,        SUM(DECODE(E.JOB, 'CLERK', E.SAL, NULL)) AS CLERK,        SUM(DECODE(E.JOB, 'MANAGER', E.SAL, NULL)) AS MANAGER,        SUM(DECODE(E.JOB, 'CLERK', NULL, 'MANAGER', NULL, SAL)) AS ETC,        SUM(E.SAL) AS SUM   FROM SCOTT.EMP E, SCOTT.DEPT D  WHERE E.DEPTNO = D.DEPTNO  GROUP BY E.DEPTNO  UNION ALL SELECT 'TOTAL',        SUM(DECODE(E.JOB, 'CLERK', E.SAL, NULL)) AS CLERK,        SUM(DECODE(E.JOB, 'MANAGER', E.SAL, NULL)) AS MANAGER,        SUM(DECODE(E.JOB, 'CLERK', NULL, 'MANAGER', NULL, SAL)) AS ETC,        SUM(E.SAL) AS SUM   FROM SCOTT.EMP E, SCOTT.DEPT D  WHERE E.DEPTNO = D.DEPTNO;

1번만 읽게 할려면...
묻지마 JOIN을 이용해서 같은 값을 2개로 복사한다음, Inline view로 결과를 만들어서,
그것을 여기서 이렇게 DECODE해서 읽고, 저기서 DECODE해서 읽고...
머리 아프게 작성해야만 했습니다.
하지면 요즘은 ROLLUP이라는 문법이 적용되어서 쉽게 작성이 가능합니다.
Oracle 9i 부터도 지원이 되는 기능이고, 정확히 몇 버전부터 적용되었는지는 모르겠습니다.

위 Query를 ROLLUP을 이용하여 수정하면 다음과 같습니다.

SELECT DECODE(GROUPING(E.DEPTNO), 1, 'TOTAL', MAX(D.DNAME)) AS DEPT,        SUM(DECODE(E.JOB, 'CLERK', E.SAL, NULL)) AS CLERK,        SUM(DECODE(E.JOB, 'MANAGER', E.SAL, NULL)) AS MANAGER,        SUM(DECODE(E.JOB, 'CLERK', NULL, 'MANAGER', NULL, SAL)) AS ETC,        SUM(E.SAL) AS SUM,        GROUPING(E.DEPTNO)   FROM SCOTT.EMP E, SCOTT.DEPT D  WHERE E.DEPTNO = D.DEPTNO  GROUP BY ROLLUP(E.DEPTNO);

GROUP BY 절 안에 ROLLUP( ) 안에 Column 들을 넣으면 해당 Column 들을 통합하지 않은 소계와 통합한 합계를 순서대로 같이 보여 줍니다.
GROUPING( ) 안에 Column을 넣어주면 집합계산에 참여하지 않으면 1, 참여하면 0을 반환해줍니다.
이왕 이만큼 본것 DEPTNO, JOB 별로 각각의 소계까지 같이 구해보겠습니다.

SELECT DEPTNO,        JOB,        SUM(SAL),        GROUPING(DEPTNO) A,        GROUPING(JOB) B,        GROUPING_ID(DEPTNO,JOB) C   FROM SCOTT.EMP  GROUP BY ROLLUP(DEPTNO, JOB);

위 Query를 현업에서 사용가능하도록, DEPT Table과 JOIN 하고, 총합에 해당되는 곳에는 'TOTAL' 이라고 적히도록 수정하였습니다.

SELECT DECODE(GROUPING(E.DEPTNO), 1, 'TOTAL',MAX(D.DNAME)) AS DEPT,        DECODE(GROUPING(E.JOB),    1, 'TOTAL',MAX(E.JOB))   AS JOB,        SUM(E.SAL) AS SUM,        GROUPING(E.DEPTNO),        GROUPING(E.JOB),        GROUPING_ID(E.DEPTNO, E.JOB)   FROM SCOTT.EMP E, SCOTT.DEPT D  WHERE E.DEPTNO = D.DEPTNO  GROUP BY ROLLUP(E.DEPTNO, E.JOB);

* 참고로 Query 결과창은 Warevalley의 Orange 라는 제품을 사용하였습니다.

[Oracle][2015 05-08] Oracle ROLLUP 소계, 합계를 고급지게 from Seok-joon Yun