ROLLUP, CUBE, GROUPING SET 의 원리_작성중

SELECT DEPTNO, JOB, SUM(SAL)

FROM EMP

GROUP BY ROLLUP(DEPTNO,JOB)

ORDER BY 1,2;

는 기본적으로 다음과 같은 쿼리이다.

SELECT DEPTNO, JOB, SUM(SAL)

FROM EMP

GROUP BY DEPTNO, JOB

UNION ALL

SELECT DEPTNO, NULL JOB, SUM(SAL)

FROM EMP

GROUP BY DEPTNO

UNION ALL

SELECT NULL DEPTNO, NULL JOB, SUM(SAL)

FROM EMP

GROUP BY NULL

ORDER BY 1,2

;

즉,  

1. ROLLUP 안 전체 GROUP BY 

2. ROLLUP 안의 첫번째 컬럼부터 빠짐

그렇다면 다음과 같은 부분 ROLLUP은?

SELECT A, B, C, MAX(D)

FROM   TAB1

GROUP BY C, ROLLUP(A,B)

SELECT A, B, C, MAX(D)

FROM   TAB1

GROUP BY C, A, B

UNION ALL

SELECT A, NULL B, C, MAX(D)

FROM   TAB1

GROUP BY C, A

UNION ALL

SELECT NULL A, NULL B, C, MAX(D)

FROM   TAB1

GROUP BY C, NULL

즉 아까와 같다 . ROLLUP 밖의 컬럼과 ROLLUP 안의 컬럼포함 전부 GROUP BY 한 후 ROLLUP안의 컬럼이 순차적으로 빠진다.

CUBE는 가능한 모든 집합을 만들어낸다.

SELECT DEPTNO, JOB, SUM(SAL)

FROM EMP

GROUP BY CUBE(DEPTNO,JOB)

ORDER BY 1,2;

는 아래와 같은 쿼리가 된다.

SELECT DEPTNO, JOB, SUM(SAL)

FROM   EMP

GROUP BY DEPTNO, JOB

UNION ALL

SELECT DEPTNO, NULL JOB, SUM(SAL)

FROM   EMP

GROUP BY DEPTNO

UNION ALL

SELECT NULL DEPTNO, JOB, SUM(SAL)

FROM   EMP

GROUP BY JOB

UNION ALL

SELECT NULL DEPTNO, NULL JOB, SUM(SAL)

FROM   EMP

GROUP BY NULL

ORDER BY 1,2;