[Oracle] Extension of the Group by statement-Rollup, Cube, and grouping sets

People who write SQL statements often should know that the main use of the group by statement is to subtotal, and here is one of its most common uses (statistics based on department, position, respectively):

SELECT  a.dname,b.job,sum (b.sal) sum_salfrom Dept a,emp bwhere A.deptno = B.deptnogroup by  a.dname,b.job;dname< C2/>job          sum_sal---------------------------------SALES          MANAGER         2850SALES          clerk            950SALES          salesman        5600ACCOUNTING     MANAGER         2450ACCOUNTING     President       5000ACCOUNTING     Clerk           1300RESEARCH       MANAGER         2975RESEARCH       ANALYST         6000RESEARCH       Clerk           1900

At this point, if someone comes to you and says, "I want the overall performance of each department and the performance of all departments in addition to the above data, you will probably think of the following stupid method (union All):

SELECT * FROM (select A.dname,b.job,sum (b.sal) sum_salfrom Dept a,emp bwhere A.deptno = B.deptnogroup by A.dname,b.jobun ION all--implements the department's subtotal select A.dname,null, SUM (b.sal) sum_salfrom Dept a,emp bwhere A.deptno = B.deptnogroup by a.dnameunion          all--implements total totals for all departments select Null,null, SUM (b.sal) sum_salfrom Dept a,emp bwhere A.deptno = b.deptno) Order by Dname;dname         JOB sum_sal---------------------------------ACCOUNTING clerk 1300ACCOUNTING MANAGER       2450ACCOUNTING President 5000ACCOUNTING 8750RESEARCH Clerk 1900RESEARCH            MANAGER 2975RESEARCH ANALYST 6000RESEARCH 10875SALES Clerk                              950SALES MANAGER 2850SALES salesman 5600SALES 9400 29025union all combined with the execution plan generated by the stupid method-------------------------------------------------------------------------- -----Plan Hash value:2979078843-------------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU) |   Time |-------------------------------------------------------------------------------| 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 need is just a small case:

SELECT A.dname,b.job, SUM (b.sal) sum_salfrom dept a,emp b WHERE a.deptno = B.deptnogroup by ROLLUP (a.dname,b.job);D name         JOB sum_sal---------------------------------SALES clerk 950SALES MANAGER 2850SALES salesman 5600SALES 9400RESEARCH clerk 1900RESEA           RCH ANALYST 6000RESEARCH MANAGER 2975RESEARCH 10875ACCOUNTING Clerk                              1300ACCOUNTING MANAGER 2450ACCOUNTING President 5000ACCOUNTING 8750 The execution plan generated by the 29025rollup notation---------------------------------------------------------------------------- -plan Hash value:1037965942-----------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU) |   Time |-----------------------------------------------------------------------------| 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 is not only convenient for SQL writing, but also can improve performance.

At this time, if another person came running to say: In addition to the above data, he also needs the overall performance of each position, you just have to change the rollup to a cube, as follows:

--Cube Group Select  a.dname,b.job, SUM (b.sal) sum_salfrom dept a,emp b WHERE a.deptno = B.deptnogroup by  CUBE (a.dname , b.job);D name          job          sum_sal---------------------------------                              29025               clerk           4150               ANALYST         6000               MANAGER         8275               salesman        5600               President       5000SALES                          9400SALES          Clerk            950SALES          MANAGER         2850SALES          salesman        5600RESEARCH                      10875RESEARCH       Clerk           1900RESEARCH       ANALYST         6000RESEARCH       MANAGER         2975ACCOUNTING                     8750ACCOUNTING     Clerk           1300ACCOUNTING     MANAGER         2450ACCOUNTING     President       5000

As can be seen from the above: cube is more granular than the rollup's presentation.

At this time, if another person came running to say: He does not need so thin data, only need to summarize the data, you can use grouping sets:

---GROUPING sets Group select To_char (b.hiredate, ' yyyy ') hire_year,a.dname,b.job, SUM (SAL) sum_salfrom dept a,emp b WHERE A.deptno = B.deptnogroup by GROUPING sets (To_char (b.hiredate, ' yyyy '), a.dname,b.job); HIRE dname          JOB          sum_sal-------------------------------------1987                                41001980                                 8001982                                13001981                               22825     ACCOUNTING                     8750                      10875     SALES                          9400 clerk 4150                    salesman        5600                    President                    MANAGER         8275                    ANALYST         6000