[Oracle] GroupBy statement extensions-Rollup, Cube, and GroupingSets

Those who frequently write SQL statements should know that the main usage of Group by statements is to classify and summarize them. The following is the most common usage of Group by statements (statistics of performance by department and position ):

SELECT  a.dname,b.job,SUM(b.sal) sum_salFROM dept a,emp bWHERE a.deptno = b.deptnoGROUP  BY a.dname,b.job;DNAME          JOB          SUM_SAL-------------- --------- ----------SALES          MANAGER         2850SALES          CLERK            950SALES          SALESMAN        5600ACCOUNTING     MANAGER         2450ACCOUNTING     PRESIDENT       5000ACCOUNTING     CLERK           1300RESEARCH       MANAGER         2975RESEARCH       ANALYST         6000RESEARCH       CLERK           1900

At this time, if someone tells you: In addition to the above data, I also need the overall performance of each department and the performance of all departments, at this time, you may think of the following stupid method (union all ):

Select * from (SELECT. dname, B. job, SUM (B. sal) sum_salFROM dept a, emp bWHERE. deptno = B. deptnoGROUP BY. dname, B. jobUNION ALL -- implements the subtotal SELECT. dname, NULL, SUM (B. sal) sum_salFROM dept a, emp bWHERE. deptno = B. deptnoGROUP BY. dnameUNION ALL -- implements the total select null, NULL, SUM (B. sal) sum_salFROM dept a, emp bWHERE. deptno = B. deptno) order by dname; dname job SUM_SAL ------------ --------- ---------- accounting clerk 1300 accounting manager 2450 accounting president 5000 ACCOUNTING 8750 research clerk 1900 research manager 2975 research analyst 6000 RESEARCH 10875 sales clerk 950 sales manager 2850 sales salesman 5600 SALES 9400 29025 the execution Plan generated by the union all merge method ------------------------------------------------------------------------------- Plan hash value: 2979078843 Bytes | Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time | Bytes | 0 | select statement | 29 | 812 | 23 (22) | 00:00:01 | 1 | sort order by | 29 | 812 | 23 (22) | 00:00:01 | 2 | VIEW | 29 | 812 | 22 (19) | 00:00:01 | 3 | UNION-ALL | 4 | hash group by | 14 | 756 | 8 (25) | 00:00:01 | * 5 | hash join | 14 | 756 | 7 (15) | 00:00:01 | 6 | table access full | DEPT | 4 | 88 | 3 (0) | 00:00:01 | 7 | table access full | EMP | 14 | 448 | 3 (0) | 00:00:01 | 8 | hash group by | 14 | 672 | 8 (25) | 00:00:01 | * 9 | hash join | 14 | 672 | 7 (15) | 00:00:01 | 10 | table access full | DEPT | 4 | 88 | 3 (0) | 00:00:01 | 11 | table access full | EMP | 14 | 364 | 3 (0) | 00:00:01 | 12 | sort aggregate | 1 | 39 | * 13 | hash join | 14 | 546 | 7 (15) | 00:00:01 | 14 | table access full | DEPT | 4 | 52 | 3 (0) | 00:00:01 | 15 | table access full | EMP | 14 | 364 | 3 (0) | 00:00:01 | -------------------------------------------------------------------------------

In fact, if you know the Rollup extension of Group By, this requirement is only a small case:

SELECT. dname, B. job, SUM (B. sal) sum_salFROM dept a, emp B WHERE. deptno = B. deptnoGROUP by rollup (. dname, B. job ); dname job SUM_SAL ------------ --------- ---------- sales clerk 950 sales manager 2850 sales salesman 5600 SALES 9400 research clerk 1900 research analyst 6000 research manager 2975 RESEARCH 10875 accounting clerk 1300 accounting manager 2450 accounting president 5000 ACCOUNTING 8750 29025rollup execution Plan ------------------------------------------------------------------------------------- Plan hash value: 1037965942 Bytes | Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time | Bytes | 0 | select statement | 14 | 756 | 8 (25) | 00:00:01 | 1 | sort group by rollup | 14 | 756 | 8 (25) | 00:00:01 | * 2 | hash join | 14 | 756 | 7 (15) | 00:00:01 | 3 | table access full | DEPT | 4 | 88 | 3 (0) | 00:00:01 | 4 | table access full | EMP | 14 | 448 | 3 (0) | 00:00:01 | -----------------------------------------------------------------------------

It can be found that this method not only facilitates SQL writing, but also improves the performance.

At this time, if another person says: In addition to the above data, he also needs the overall performance of each position. You only need to change rollup to cube, as shown below:

-- CUBE grouping SELECT. dname, B. job, SUM (B. sal) sum_salFROM dept a, emp B WHERE. deptno = B. deptnoGROUP by cube (. dname, B. job ); dname job SUM_SAL ------------ --------- ---------- 29025 CLERK 4150 ANALYST 6000 MANAGER 8275 SALESMAN 5600 PRESIDENT 5000 SALES 9400 sales clerk 950 sales manager 2850 sales manager 5600 SALES man 10875 RESEARCH cler1900 research analyst 6000 research manager 2975 ACCOUNTING 8750 accounting clerk 1300 accounting manager 2450 accounting president 5000

From the above, we can see that the cube is finer than the rollup display granularity.

At this time, if another person ran and said: He does not need such fine data, but only needs to summarize the data, you can use Grouping Sets:

--- Grouping sets grouping select to_char (B. hiredate, 'yyyy') hire_year,. dname, B. job, SUM (sal) sum_salFROM dept a, emp B WHERE. deptno = B. deptnoGROUP by grouping sets (to_char (B. hiredate, 'yyyy'),. dname, B. job); hire dname job SUM_SAL ---- --------------- ---------- 1987 41001980 8001982 13001981 22825 ACCOUNTING 8750 RESEARCH 10875 SALES 9400 CLERK 4150 SALESMAN 5600 PRESIDENT 5000 MANAGER 8275 ANALYST 6000