oracle 分组学习

1.rollup   

   rollup 的语法如下： select 。。。。group by rollup(colunm1,column2。。。);

   rollup 会实现以下功能：

         实现标准分组，然后实现column1的分组的小记，实现column1下column2的分组小记，最后实现所有合计。。

   例子：使用scott用户

   

SQL> select deptno,job,sum(sal) from emp group by rollup(deptno,job);

    DEPTNO JOB         SUM(SAL)

---------- --------- ----------

        10 CLERK                1300

        10 MANAGER         2450

        10 PRESIDENT        5000

        10                            8750

        20 CLERK                 1900

        20 ANALYST            6000

        20 MANAGER          2975

        20                            10875

        30 CLERK                  950

        30 MANAGER          2850

        30 SALESMAN         5600

    DEPTNO JOB         SUM(SAL)

---------- --------- ----------

        30                         9400

                                   29025

这条语句相当于

SQL> select deptno,job,sum(sal) from emp group by deptno,job   -- 计算计算每个deptno中job的小计

  2  union all

  3  select deptno,null as job,sum(sal) from emp group by deptno -- 计算每个deptno下的小计

  4  union all

  5  select null as deptno,null as job ,sum(sal) from emp  -- 计算合计

  6  order by deptno,job;

    DEPTNO JOB         SUM(SAL)

---------- --------- ----------

        10 CLERK           1300

        10 MANAGER         2450

        10 PRESIDENT       5000

        10                 8750

        20 ANALYST         6000

        20 CLERK           1900

        20 MANAGER         2975

        20                10875

        30 CLERK            950

        30 MANAGER         2850

        30 SALESMAN        5600

    DEPTNO JOB         SUM(SAL)

---------- --------- ----------

        30                 9400

                          29025

 

2.cube

    cube的语法如下： select 。。。。group by cube(colunm1,column2。。。);

    cube实现的功能：先实现合计，然后实现colunm2的合计，在实现column1的合计，实现column1下column2的分组小记,在cube中，都是没有顺序的

例子：

   

SQL> select deptno,job,sum(sal) from emp group by cube(deptno,job);

    DEPTNO JOB         SUM(SAL)

---------- --------- ----------

                               29025            --

           CLERK           4150

           ANALYST         6000

           MANAGER         8275

           SALESMAN        5600

           PRESIDENT       5000

        10                 8750

        10 CLERK           1300

        10 MANAGER         2450

        10 PRESIDENT       5000

        20                10875

    DEPTNO JOB         SUM(SAL)

---------- --------- ----------

        20 CLERK           1900

        20 ANALYST         6000

        20 MANAGER         2975

        30                 9400

        30 CLERK            950

        30 MANAGER         2850

        30 SALESMAN        5600

执行计划

----------------------------------------------------------

Plan hash value: 3627207636

-----------------------------------------------------------------------------

| Id  | Operation            | Name | Rows  | Bytes | Cost (%CPU)| Time     |

-----------------------------------------------------------------------------

|   0 | SELECT STATEMENT     |      |    11 |   165 |     4  (25)| 00:00:01 |

|   1 |  SORT GROUP BY       |      |    11 |   165 |     4  (25)| 00:00:01 |

|   2 |     GENERATE CUBE      |      |    11 |   165 |     4  (25)| 00:00:01 |   红色的是rollup中没有的

|   3 |    SORT GROUP BY     |      |    11 |   165 |     4  (25)| 00:00:01 |

|   4 |     TABLE ACCESS FULL| EMP  |    14 |   210 |     3   (0)| 00:00:01 |

-----------------------------------------------------------------------------

可以看到cube比rollu批更加细，所分的组更加多，当然，也可以用普通的union all来完成

假入我们现在不要cube中job的合计，我们可以通过以下的方式实现：

SQL> select deptno,job,sum(sal) from emp group by deptno, cube(job);

    DEPTNO JOB         SUM(SAL)

---------- --------- ----------

        10                 8750

        10 CLERK           1300

        10 MANAGER         2450

        10 PRESIDENT       5000

        20                10875

        20 CLERK           1900

        20 ANALYST         6000

        20 MANAGER         2975

        30                 9400

        30 CLERK            950

        30 MANAGER         2850

    DEPTNO JOB         SUM(SAL)

---------- --------- ----------

        30 SALESMAN        5600

3.grouping sets

  

     grouping sets 的语法如下： select 。。。。group by   grouping sets (colunm1,column2。。。);

   看例子就明白grouping sets 的用途

SQL> select deptno,job,sum(sal) from emp group by grouping sets(deptno,job);

    DEPTNO JOB         SUM(SAL)

---------- --------- ----------

           CLERK           4150

           SALESMAN        5600

           PRESIDENT       5000

           MANAGER         8275

           ANALYST         6000

        30                 9400

        20                10875

        10                 8750

已选择8行。

执行计划

----------------------------------------------------------

Plan hash value: 3810863559

--------------------------------------------------------------------------------

------------------------

| Id  | Operation                  | Name                      | Rows  | Bytes |

 Cost (%CPU)| Time     |

--------------------------------------------------------------------------------

------------------------

|   0 | SELECT STATEMENT           |                           |    11 |   352 |

    11  (19)| 00:00:01 |

|   1 |  TEMP TABLE TRANSFORMATION |                           |       |       |

            |          |

|   2 |   LOAD AS SELECT           | SYS_TEMP_0FD9D6617_26CB0A |       |       |

            |          |

|   3 |    TABLE ACCESS FULL       | EMP                       |    14 |   210 |

     3   (0)| 00:00:01 |

|   4 |   LOAD AS SELECT           | SYS_TEMP_0FD9D6618_26CB0A |       |       |

            |          |

|   5 |    HASH GROUP BY           |                           |     1 |    19 |

     3  (34)| 00:00:01 |

|   6 |     TABLE ACCESS FULL      | SYS_TEMP_0FD9D6617_26CB0A |     1 |    19 |

     2   (0)| 00:00:01 |

|   7 |   LOAD AS SELECT           | SYS_TEMP_0FD9D6618_26CB0A |       |       |

            |          |

|   8 |    HASH GROUP BY           |                           |     1 |    26 |

     3  (34)| 00:00:01 |

|   9 |     TABLE ACCESS FULL      | SYS_TEMP_0FD9D6617_26CB0A |     1 |    26 |

     2   (0)| 00:00:01 |

|  10 |   VIEW                     |                           |     1 |    32 |

     2   (0)| 00:00:01 |

|  11 |    TABLE ACCESS FULL       | SYS_TEMP_0FD9D6618_26CB0A |     1 |    32 |

     2   (0)| 00:00:01 |

--------------------------------------------------------------------------------

------------------------

   

  可以看出grouping sets 对deptno，job分别求了合计，并且执行计划看还生成了一个temp表