진화를 꿈꾸다..

############################ ROLLUP and CUBE ############################

## ROLLUP은 GROUP BY 절의 그룹 조건에 따라 전체 행을 그룹화 하고, 각 그룹에 대해 부분합을 구하는 연산자이다.
## CUBE는 ROLLUP에 의한 그룹 결과와 GROUP BY 절에 기술된 조건에 따라 그룹 조합을 만드는 연산자이다.
   즉, ROLLUP 연산자를 수행한 결과에 더해 GROUP BY 절에 기술된 조건에 따라 모든 가능한 그룹핑 조합에 대한 결과를 출력한다.
## ROLLUP구문은 GROUP BY절과 같이 사용 되며, GROUP BY절에 의해서 그룹 지어진 집합결과에 대해서
   좀 더 상세한 정보를 반환하는 기능을 수행합니다.
## SELECT절에 ROLLUP을 사용함으로써 보통의 select된 데이터와 그 데이터의 총계를 구할 수 있습니다.

rollup(a,b,c) = ( (a,b,c), (a,b), (a), ( ) )
cube(a,b,c)  = ( (a,b,c), (a,b), (a,c), (b,c), (a), (b), (c), ( ) )

즉, rollup은 n+1개의 grouping이 되고
  cube는 2의 n제곱개의 grouping이 나온다.
【형식】
        SELECT   컬럼명, 그룹함수(컬럼명)
        FROM     테이블명
        WHERE    조건
        GROUP BY [ROLLUP | CUBE]그룹핑하고자하는 컬럼명,...
        HAVING   그룹조건
        ORDER BY 컬럼명 또는 위치번호

-- 실습 1
SQL> select department_id, job_id, sum(salary)
  2  from emp
  3  where department_id <60
  4* group by rollup(department_id, job_id)

DEPARTMENT_ID JOB_ID     SUM(SALARY)
------------- ---------- -----------
           10 AD_ASST            4400    <~ depatment_id+job_id
           10                      4400    <~ department_id
           20 MK_MAN            13000
           20 MK_REP             6000
           20                     19000
           30 PU_MAN            11000
           30 PU_CLERK          13900
           30                     24900
           40 HR_REP             6500
           40                      6500
           50 ST_MAN            36400
           50 SH_CLERK          64300
           50 ST_CLERK          55700
           50                    156400
                                 211200   <~ total

-- 실습2
SQL> select department_id, job_id, sum(salary)
  2  from emp
  3  where department_id <60
  4* group by cube(department_id, job_id)

DEPARTMENT_ID JOB_ID     SUM(SALARY)
------------- ---------- -----------
                                    211200   <~ total
                HR_REP            6500    <~ job_id
                MK_MAN           13000
                MK_REP            6000
                PU_MAN           11000
                ST_MAN           36400
                AD_ASST           4400
                PU_CLERK         13900
                SH_CLERK         64300
                ST_CLERK         55700
           10                       4400    <~ department_id
           10   AD_ASST           4400    <~ department_id+job_id
           20                      19000
           20   MK_MAN           13000
           20   MK_REP            6000
           30                      24900
           30   PU_MAN           11000
           30   PU_CLERK         13900
           40                       6500
           40   HR_REP            6500
           50                     156400
           50   ST_MAN           36400
           50   SH_CLERK         64300
           50   ST_CLERK         55700
       
       
-- 실습3(grouping 함수를 이용)

SQL> select department_id DEPTID, job_id JOB, sum(salary),
  2           grouping(department_id) GRP_DEPT,
  3           grouping(job_id) GRP_JOB
  4  from emp
  5  where department_id<50
  6  group by rollup(department_id,job_id);

    DEPTID   JOB        SUM(SALARY)   GRP_DEPT    GRP_JOB
---------- ----------  -----------  ----------   ----------
        10    AD_ASST           4400          0            0  <~ department_id+job_id
        10                       4400          0            1  <~ department_id
        20    MK_MAN           13000          0           0
        20    MK_REP            6000          0            0
        20                       19000          0            1
        30    PU_MAN           11000          0            0
        30    PU_CLERK         13900          0            0
        30                       24900          0            1
        40    HR_REP            6500           0            0
        40                       6500          0            1
                                54800          1             1  <~ total

** group으로 사용되면 0으로, 사용되지 않았다면 1로 표시해준다.
       
SQL> select decode(grouping(d.department_name),1,'TOTAL',d.department_name) DEPT_NAME,
  2  decode(grouping(e.job_id),1,'TOTAL',e.job_id) JOB_ID, sum(e.salary) SAL,
  3  count(e.employee_id) EMP_COUNT,
  4  grouping(d.department_name) DNAME_NO, grouping(e.job_id) JOB_NO
  5  from emp e, dept d
  6  where e.department_id=e.department_id
  7* group by rollup(d.department_name, e.job_id)

DEPT_NAME                         JOB_ID            SAL  EMP_COUNT   DNAME_NO     JOB_NO                                  
------------------------------ ---------- ---------- ---------- ---------- ----------                                  
IT                                  AD_VP           34000            2          0          0                                  
IT                                  AC_MGR          12000            1          0          0                                  
IT                                  FI_MGR          12000            1          0          0                                  
                .
                .
IT                                  TOTAL          684400          106          0          1    
Finance                            AD_VP           34000            2          0          0                                  
Finance                            AC_MGR          12000            1          0          0                                  
Finance                            FI_MGR          12000            1          0          0                                  
Finance                            HR_REP           6500            1          0          0   
                .
                .                            
Finance                            TOTAL          684400          106          0          1        
                .
                .
TOTAL                              TOTAL        18478800         2862          1          1 
 

-- 실습4 (grouping set을 이용하여 내마음대로 group을 지정)
SQL> l
  1  select department_id, job_id, manager_id, avg(salary)
  2  from emp
  3  group by GROUPING SETS
  4* ( (department_id,job_id), (job_id,manager_id))
SQL> /

DEPARTMENT_ID JOB_ID     MANAGER_ID AVG(SALARY)
------------- ---------- ---------- -----------
                AC_MGR             101       12000   <~ job_id+manager_id
                SH_CLERK          122        3200
                SH_CLERK          124        2825
                MK_MAN            100       13000
         .
         .
         .
          110   AC_ACCOUNT                   8300   <~ department_id+job_id
           90   AD_VP                         17000
           50   ST_CLERK                      2785
           80   SA_REP                    8396.55172
          110   AC_MGR                       12000
                .
                .
                .