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

People who often write SQL statements should know that the main use of the group BY statement is to do subtotals, the following is one of its most common use methods (according to Department, the position of the respective statistical performance):

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, suppose someone comes to you and says, "In addition to the above data, I want the overall performance of each department and the performance of all the departments together, this time you will probably think of such as 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 operation 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, assuming you know the rollup extension of group BY, this requirement is only 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 29025rollup operation plan generated by writing---------------------------------------------------------------------------- -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 |-----------------------------------------------------------------------------

Can be found that this method is not only SQL writing convenience, performance can also be improved.

At this time, suppose 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 can be, for example, see below:

--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 rollup's presentation.

At this time, suppose another person came running to say: He does not need so thin data, only need to summarize the data, 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

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