[ORACLE 강좌] 오라클 함수 - ROLLUP, CUBE, GROUPING

[출처] 오라클클럽 SQL강좌

ROLLUP operator

ROLLUP구문은 GROUP BY 절과 같이 사용 되며, GROUP BY절에 의해서 그룹 지어진 집합 결과에 대해서 좀 더 상세한 정보를 반환하는 기능을 수행 한다.

SELECT절에 ROLLUP을 사용함으로써 보통의 SELECT된 데이터와 그 데이터의 총계를 구할 수 있다.

 

간단 예제

-- 먼저 GROUP BY를 사용해서 직업별로 급여 합계를 구하는 예제이다.
SQL> SELECT job, SUM(sal)
     FROM emp
     GROUP BY job; 

JOB          SUM(SAL)
---------- ----------
ANALYST         600
CLERK          3200
MANAGER       33925
PRESIDENT      5000
SALESMAN       4000


-- ROLLUP을 사용해서 직업별로 급여 합계와  총계를 구하는 예제이다.
SQL> SELECT job, SUM(sal)
     FROM emp
     GROUP BY ROLLUP(job);
 

JOB          SUM(SAL
---------- ----------
ANALYST         6000
CLERK           3200
MANAGER        33925
PRESIDENT       5000
SALESMAN        4000
               52125   --> 급여 합계에 대한 총계가 추가 되었다.
    

좀 더 복잡한 예제

부서별로 인원수와 급여 합계를 구하는 예제

 
-- GROUP BY절을 사용 한 결과
SQL> SELECT b.dname, a.job, SUM(a.sal) sal, 
            COUNT(a.empno) emp_count
     FROM emp a, dept b
     WHERE a.deptno = b.deptno
     GROUP BY b.dname, a.job

DNAME       JOB               SAL  EMP_COUNT
----------  ---------- ---------- ----------
ACCOUNTING  CLERK            1300          1
ACCOUNTING  MANAGER          2450          1
ACCOUNTING  PRESIDENT        5000          1
RESEARCH    ANALYST          6000          2
RESEARCH    CLERK            1900          2
RESEARCH    MANAGER          2975          1
SALES       MANAGER         28500          1
SALES       SALESMAN         4000          3


-- 부서별로 인원과, 급여합계가 한 눈에 보이지 않는다.
-- 일일이 부서에 해당하는 직업별 급여와 사원수를 일일이 더해야 한다.

-- 이런 경우 ROLLUP을 사용하여 쉽게 조회 할 수 있다. 
SQL> SELECT b.dname, a.job, SUM(a.sal) sal, 
            COUNT(a.empno) emp_count
     FROM emp a, dept b
     WHERE a.deptno = b.deptno
     GROUP BY ROLLUP(b.dname, a.job)


DNAME      JOB               SAL  EMP_COUNT
---------- ---------- ---------- ----------
ACCOUNTING CLERK            1300          1
ACCOUNTING MANAGER          2450          1
ACCOUNTING PRESIDENT        5000          1
ACCOUNTING                  8750          3  -->  ACCOUNTING 부서의 급여 합계와 전체 사원 수
RESEARCH   ANALYST          6000          2
RESEARCH   CLERK            1900          2
RESEARCH   MANAGER          2975          1
RESEARCH                   10875          5 -->  RESEARCH 부서의 급여 합계와 전체 사원 수
SALES      MANAGER         28500          1
SALES      SALESMAN         4000          3
SALES                      32500          4 -->  SALES부서의 급여 합계와 전체 사원 수
                               52125         12 ->  전체 급여 합계와 전체 사원 수
 

-- 위와 같이ROLLUP은 일반적인 누적에 대한 총계를 구할 때 아주 편리하게 사용 할 수 있다.
  
 
CUBE operator
  
SQL> SELECT b.dname, a.job, SUM(a.sal) sal, COUNT(a.empno) emp_count
     FROM emp a, dept b
     WHERE a.deptno = b.deptno
     GROUP BY CUBE(b.dname, a.job)

DNAME      JOB               SAL  EMP_COUNT
---------- ---------- ---------- ----------
ACCOUNTING  CLERK            1300          1
ACCOUNTING  MANAGER          2450          1
ACCOUNTING  PRESIDENT        5000          1
ACCOUNTING                   8750          3 --> ACCOUNTING 부서의 직업별 급여의 총계와 사원 수.
RESEARCH    ANALYST          6000          2
RESEARCH    CLERK            1900          2
RESEARCH    MANAGER          2975          1
RESEARCH                    10875          5 --> RESEARCH 부서의 직업별 급여의 총계와 사원 수.
SALES       MANAGER         28500          1
SALES       SALESMAN         4000          3
SALES                       32500          4 --> SALES 부서의 직업별 급여 총계와 사원 수.
            ANALYST          6000          2
            CLERK            3200          3
            MANAGER         33925          3
            PRESIDENT        5000          1
            SALESMAN         4000          3   
                            52125         12  --> 직업별로 급여의  총계와 사원 수.

-- CUBE를 어느 경우에 사용 할 수 있는지 이해 되셨죠..
-- CUBE는 Cross-Tab에 대한 Summary를 추출하는데 사용 된다
-- 즉 ROLLUP에 의해 나타 내어지는 Item Total값과 Column Total값을 나타 낼 수 있다.    
 
Grouping 함수 

: pseudo column (의사컬럼 : 실제로는 존재하지 않으나 특수목적으로 사용)

 

GROUPING 함수는 ROLLUP, CUBE에 모두 사용할 수 있다.

GROUPING 함수는 해당 Row가 GROUP BY에 의해서 산출된 Row인 경우에는 0을 반환하고, ROLLUP이나 CUBE에 의해서 산출된 Row인 경우에는 1을 반환하게 된다.

따라서 해당 Row가 결과집합에 의해 산출된 Data 인지, ROLLUP이나 CUBE에 의해서 산출된 Data 인지를 알 수 있도록 지원하는 함수이다.

 
SQL> SELECT b.dname, a.job, SUM(a.sal) sal, COUNT(a.empno) emp_count,
            GROUPING(b.dname) "D", GROUPING(a.job) "S"
     FROM emp a, dept b
     WHERE a.deptno = b.deptno
     GROUP BY CUBE(b.dname, a.job)

DNAME        JOB               SAL EMP_COUNT    D    S
----------   ----------    ------- --------- ---- ----
ACCOUNTING  CLERK            1300          1    0    0
ACCOUNTING  MANAGER          2450          1    0    0
ACCOUNTING  PRESIDENT        5000          1    0    0
ACCOUNTING                   8750          3    0    1
RESEARCH    ANALYST          6000          2    0    0
RESEARCH    CLERK            1900          2    0    0
RESEARCH    MANAGER          2975          1    0    0
RESEARCH                    10875          5    0    1
SALES       MANAGER         28500          1    0    0
SALES       SALESMAN         4000          3    0    0
SALES                       32500          4    0    1
            ANALYST          6000          2    1    0
            CLERK            3200          3    1    0
            MANAGER         33925          3    1    0
            PRESIDENT        5000          1    1    0
            SALESMAN         4000          3    1    0
                            52125         12    1    1