[ORACLE] Group by, Rollup, Cube의 차이점

◈ ROLLUP operator    - ROLLUP구문은 GROUP BY절과 같이 사용 되며, GROUP BY절에 의해서 그룹 지어진     집합결과에 대해서 좀 더 상세한 정보를 반환하는 기능을 수행합니다.   - SELECT절에 ROLLUP을 사용함으로써 보통의 select된 데이터와 그 데이터의     총계를 구할 수 있습니다. ※ 우선 아주 간단한 예제부터 살펴 보겠습니다.    (scott유저의 emp테이블을 가지고 테스트 했습니다.) -- Group By를 사용해서 직업별로 급여 합계를 구하는 예제 입니다. SELECT job, SUM(sal) FROM emp GROUP BY job  JOB          SUM(SAL) ---------- ---------- ANALYST          600 CLERK              3200 MANAGER        33925 PRESIDENT      5000 SALESMAN      4000 -- 아래 SQL문은 위의 예제에 ROLLUP구문을 사용해서 직업별로 급여 합계를 구하고    총계를 구하는 예제 입니다. SELECT job, SUM(sal) FROM emp GROUP BY ROLLUP(job)   JOB          SUM(SAL ---------- ---------- ANALYST          6000 CLERK              3200 MANAGER        33925 PRESIDENT       5000 SALESMAN       4000                        52125   --> 급여 합계에 대한 총계가 추가 되었습니다. 우선 간단하게 ROLLUP  Operator의 예제를 살펴보았습니다. 조금더 복잡한(?) 예제를 하나더 해보면은요.. -- 부서의 직업별로 인원수와 급여 합계를 구하는 예제를 하나더 해보겠습니다. -- 일반적인 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  Operator를 적용해서 구현을 하면은 부서별 급여합계와 사원 총수를    쉽게 볼 수 있습니다... 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 Operator을 일반적인 누적에 대한 총계를 구할때 사용하면 아주 편리하게 사용 할 수 있습니다.

-  SELECT NVL(DECODE(B.NO, '1', DNAME), '총계') DNAME, SUM(CLERK) CLERK, SUM(MANAGER) MANAGER, SUM(ETC) ETC, SUM(DEPT_SAL) DEPT_SAL FROM ( SELECT B.DNAME, A.CLERK, A.MANAGER, A.ETC, A.CLERK + A.MANA GER + A.ETC DEPT_SAL FROM ( SELECT DEPTNO, SUM(DECODE(JOB,'CLERK',SAL)) CLERK, SUM(DECODE(JOB,'MANAGER',SAL)) MANAGER, SUM(DECODE(JOB,'MANAGER',0,'CLERK',0,SAL)) ETC FROM EMP GROUP BY DEPTNO ) A, DEPT B WHERE A.DEPTNO = B.DEPTNO ) A, (SELECT '1' NO FROM DUAL UNION ALL SELECT '2' FROM DUAL) B GROUP BY DECODE(B.NO, '1',A.DNAME); 위 처럼 오라클에서 지원하는 함수를 이용하지 않고 만들어 봤어요.. 참조 하세요. -  SELECT b.dname, a.job, SUM(a.sal) sal, COUNT(a.empno) emp_co unt FROM emp a, dept b WHERE a.deptno = b.deptno GROUP BY b.dname, a.job 분석해 볼까여 1.select; 2.from 3.where 4.group 2.테이블 두개가 메모리에 적제가 되겠죠 3.조건에 의해 deptno 을 기준으로 하나의 테이블로 정리 되겠죠 그다음은 4. 가 계산되는데 여기서 4.가 없다고 가정하면 1.로 가서 결국 하나의 행이 결과값으로 도출될 것입니다. 그룹함수에 의해서 총합과 행의 총갯수가 반환되겠죠 그럼 다시 되돌아가서 4.group by 절이 계산을 하게 되는데 우선 b.dname만 있다면 몇개의 레코드가 반환될까여 부서명이 4개라면.. 4개의 레코드가 반환되겠죠(단 4개의 부서가 데이타에 존재한다면) 그 다음에 a.job 까지(직업은 3개라고 가정하면) 두개의 조건이 주어졌습니다. 그럼 몇개의 레코드가 반환될까여 12개의 레코드가 반환될까여???? 이번에는 문장을 분석해서 SQL을 작성해 볼까여 (위 강의가 기준) 부서의 직업별로 인원수와 급여 합계를 구하라 키포인트 인원수와 급여합계입니다. 여기서 그룹함수가 사용된다는 것을 알수 있고 GROUP BY절을 사용해야 된다는 것도 알수가 있습니다. 형식은 1.SELECT 2.TABLE 3.WHERE 4.GROUP BY 로 형식이 가추어 지고 1. 컬럼은 4개가 되고 그룹함수 SUM()가 COUNT()가 들어가고여 2. 테이블은 위 강의가 기준이니까 2개이고여 3. 자연스럽게 JOIN 이 일어나겠네여 무슨 조인일까여???? 4. GROUP BY절에서 부서와 직업이 GROUP으로 묶여지겠네여... SQL을 작성하면 위와 같겠지여

 

 

 

◈ CUBE operator 


 ※ CUBE강좌를 보시기 전에 바로 위에 있는 ROLLUP강좌를 꼭 봐주세요..
  
 ROLLUP 강좌예제 중에서 아래 SQL문 예제를 가지고 CUBE강좌를 진행 하려고 합니다.

====================  ROLLUP 강좌의 예제입니다.  ======================= 

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 ->  전체 급여 합계와 전체 사원수

=========================================================================


위의 SQL의 결과를 보면 부서별로 각 직업에 해당하는 급여와 사원수를 볼 수 있습니다.

하지만 부서별로 각 직업의 급여와 사원수, 그리고 또 각 직업별로 급여 합계와 사원수를
보기 위해서는 두개의 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)
UNION
SELECT ' ', job, SUM(sal) sal, COUNT(empno) emp_count
FROM emp
GROUP BY ROLLUP(job)
 
DNAME      JOB               SAL  EMP_COUNT
---------- ---------- ---------- ----------
ACCOUNTING CLERK               1300          1
ACCOUNTING MANAGER          2450          1
ACCOUNTING PRESIDENT        5000          1
ACCOUNTING                         8750          3
RESEARCH    ANALYST           6000          2
RESEARCH    CLERK               1900          2
RESEARCH    MANAGER          2975          1
RESEARCH                           10875          5
SALES          MANAGER         28500          1
SALES          SALESMAN         4000          3
SALES                                 32500          4   => 요기 까지는 첫 번째 ROLLUP를 이용해서 구하고요
                   ANALYST          6000          2
                   CLERK              3200          3
                   MANAGER         33925          3
                   PRESIDENT        5000          1
                   SALESMAN         4000          3
                                           52125         12  => 요 부분은 두 번째 ROLLUP을 이용해서 구했습니다.
 


CUBE Operator를 사용하면 편하게 하나의 SQL문으로 위의 결과를 얻을 수 있습니다.
직접 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 Operator는 Cross-Tab에 대한 Summary를 추출하는데 사용 됩니다
즉 ROLLUP에 의해 나타내어지는 Item Total값과 Column Total값을 나타 낼 수 있습니다.

너무 어렵게 설명했나요... 응용해서 테스트 해보세요..

◈ GROUPING() 함수 GROUPING Function은 ROLLUP, CUBE Operator에 모두 사용할 수 있습니다. GROUPING Function는 해당 Row가 GROUP BY에 의해서 산출된 Row인 경우에는 0을 반환하고, ROLLUP이나 CUBE에 의해서 산출된 Row인 경우에는 1을 반환하게 됩니다. 따라서 해당 Row가 결과집합에 의해 산출된 Data인지, ROLLUP이나 CUBE에 의해서 산출된 Data인지를 알 수 있도록 지원하는 함수입니다. 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

출처 : http://huzii.egloos.com/3638265#