1)ROLLUP
Rollup은 Group by 의 확장된 형태로 사용하기 쉬우며 병렬로 수행이 가능하기 때문에 매우 효과적일뿐 아니라 시간 및 지역처럼 계층적 분류를 포함하고 있는 데이터의 집계에 적합하도록 되어 있다
SELECT /* SCJP10QC : 부서/직업별 평균임금 조회 */
decode(GROUPING(a.deptno), 1, 'All Departments', a.deptno) deptno,
decode(GROUPING(a.job), 1, 'All Jobs', a.job) job,
COUNT(*) count,
AVG(sal) * 12 avg_sal
FROM emp a
GROUP BY ROLLUP(a.deptno, a.job);
DEPTNO JOB count Avg_Sal
------------------------- --------- ---------- ----------
10 CLERK 1 15600
MANAGER 1 29400
PRESIDENT 1 60000
All Jobs 3 35000
30 CLERK 1 11400
MANAGER 1 34200
SALESMAN 4 16800
All Jobs 6 18800
90 USR_001 1 108000
USR_010 1 108120
All Jobs 2 108060
All Departments All Jobs 11 39447.2727
2) CUBE
SELECT decode(GROUPING(a.deptno), 1, 'All dept', a.deptno) deptno,
decode(GROUPING(a.job), 1, 'All Jobs', a.job) job,
COUNT(*) count,
AVG(a.sal) * 12 avg_sal
FROM emp a
GROUP BY CUBE(a.deptno, a.job);
DEPTNO JOB count avg_sal
------------------------ --------- ---------- ----------
All dept All Jobs 11 39447.2727
CLERK 2 13500
MANAGER 2 31800
USR_001 1 108000
USR_010 1 108120
SALESMAN 4 16800
PRESIDENT 1 60000
10 All Jobs 3 35000
CLERK 1 15600
MANAGER 1 29400
PRESIDENT 1 60000
30 All Jobs 6 18800
CLERK 1 11400
MANAGER 1 34200
SALESMAN 4 16800
90 All Jobs 2 108060
USR_001 1 108000
USR_010 1 108120