ROLLUP, CUBE 의 단계적 이해

항상 헛갈리는 RULLUP과 CUBE 이젠 잊지 말자...

1.RULLUP 의 단계적 이해

a. 전체 합
SELECT  SUM(salary)
FROM  employees
WHERE  department_id < 50;

SUM(SALARY)
-----------
      54800

b. 부서별/직업별 합
SELECT  department_id, job_id, SUM(salary)
FROM  employees
WHERE  department_id < 50
GROUP BY department_id, job_id;

DEPARTMENT_ID JOB_ID     SUM(SALARY)
------------- ---------- -----------
           10 AD_ASST           4400
           20 MK_MAN           13000
           20 MK_REP            6000
           30 PU_MAN           11000
           30 PU_CLERK         13900
           40 HR_REP            6500

c. 부서별 합
SELECT  department_id, SUM(salary)
FROM  employees
WHERE  department_id < 50
GROUP BY department_id;

DEPARTMENT_ID SUM(SALARY)
------------- -----------
           10        4400
           20       19000
           30       24900
           40        6500

d. a,b,c를 union all 한 결과와 다음의 쿼리 실행결과가 일치.
SELECT department_id, job_id, SUM(salary)
FROM employees
WHERE department_id < 50
GROUP BY ROLLUP(department_id, job_id);

DEPARTMENT_ID   JOB_ID              SUM(SALARY)
-------------        ----------         -----------
           10             AD_ASST           4400       -> b
           10                                     4400       -> c
           20             MK_MAN           13000      -> b
           20             MK_REP            6000       -> b
           20                                     19000     -> c
           30             PU_MAN            11000     -> b
           30             PU_CLERK         13900     -> b
           30                                     24900     -> c
           40             HR_REP             6500      -> b
           40                                      6500       -> c
                                                    54800     -> a

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

2. ROLLUP 시에 불필요한 집합결과 제거 하기
a. GROUPING() 함수의 이해
    - 해당 컬럼이 집합에 참여하지 않으면 1을 반환, 집합계산에 참여하면 0을 반환
   SELECT department_id, job_id, SUM(salary), GROUPING(department_id), GROUPING(job_id)
   FROM employees
   WHERE department_id < 50
   GROUP BY ROLLUP(department_id, job_id);

DEPARTMENT_ID JOB_ID  SUM(SALARY) GROUPING(DEPARTMENT_ID) GROUPING(JOB_ID)
------------- ---------- ----------- ----------------------- ----------------
           10 AD_ASST           4400                       0                0
           10                          4400                       0                1
           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

3. CUBE의 단계적 이해
    - 가능한 모든 요소의 집합을 고려.
SELECT department_id, job_id, SUM(salary), GROUPING(department_id), GROUPING(job_id)
   FROM employees
 WHERE department_id < 50
 GROUP BY CUBE(department_id, job_id);

DEPARTMENT_ID JOB_ID     SUM(SALARY) GROUPING(DEPARTMENT_ID) GROUPING(JOB_ID)
------------- ---------- ----------- ----------------------- ----------------
                                     54800                       1                1
              HR_REP            6500                        1                 0           -> 추가
              MK_MAN           13000                       1                0          -> 추가
              MK_REP            6000                        1                 0          -> 추가
              PU_MAN           11000                       1                0          -> 추가
              AD_ASST           4400                       1                 0          -> 추가
              PU_CLERK         13900                       1                0        
           10                         4400                       0                1
           10 AD_ASST           4400                       0                0
           20                         19000                      0                1
           20 MK_MAN           13000                      0                0
           20 MK_REP            6000                       0                0
           30                         24900                     0                1
           30 PU_MAN           11000                      0                0
           30 PU_CLERK         13900                     0                0
           40                          6500                      0                1
           40 HR_REP            6500                       0                0

4. 이해가 쉬운 GROUPING SETS 의 사용
    ROLLUP, CUBE에서 사용하는 집합을 직접적으로 명시.

SELECT department_id, job_id, SUM(salary)
FROM employees
WHERE department_id < 50
GROUP BY GROUPING SETS((department_id, job_id), (department_id), (job_id),())
ORDER BY department_id

DEPARTMENT_ID JOB_ID     SUM(SALARY)
------------- ---------- -----------
           10 AD_ASST           4400
           10                         4400
           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
              AD_ASST           4400
              HR_REP            6500
              MK_MAN           13000
              MK_REP            6000
              PU_CLERK         13900
              PU_MAN           11000
                                      54800