[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    
    

다른 카테고리의 글 목록

Dev. 데이터베이스/Oracle 관련 카테고리의 포스트를 톺아봅니다