oracle 中group by cube和rollup



  本文主要记录下个人对oracle group by函数的理解  

  即便是oracle新手，恐怕也都用过group by函数，再加个having可以对分组后的信息进行筛选。不再多说

  然后在分组之后，如果还要对每个或某个小组做统计，那么则需要用到rollup，cube或grouping sets，下面简述下group by 的这三个扩展函数，这四个函数可以组合使用，

  1：rollup：用法：group by rollup（a,b,c）

                    含义：rollup为“卷起”之意，即先以a，b，c组合进行分组，然后从右像左卷起，依次以a，b分组，a分组，最后以全表为整体分组统计，在这个过程中也便实现了对

                               部分分组（对a，b分组的小计以及对a分组的小计）的小计和对整个表的总计。举个例子：

                                sql：

                    select item,color,SUM(Quantity) from Inventory group by rollup(item,color);

                                 结果集：

ITEM        COLOR    SUM(QUANTITY)
-------     ------   -------------
Chair       Red                210
Chair       Blue               101
Chair                          311
Table       Red                223
Table       Blue               124
Table                          347
                               658

2：cube：用法：goup by cube(a,b,c)
                 含义：cube为“立方”之意，即对每种分组情况进行统计，对（a,b,c）,(a,b),(a,c),(b,c),(a),(b),(c)以及整个表进行统计，在这个过程中也实现了对每种分组情况的小计和

                            对整表的总计。举个例子：

                              sql：

                     select item,color,SUM(Quantity) from Inventory group by cube(item,color);

              结果集：

ITEM        COLOR    SUM(QUANTITY) 
-------     ------   ------------- 
                               658 
            Red                433 
            Blue               225 
Chair                          311 
Chair       Red                210 
Chair       Blue               101 
Table                          347 
Table       Red                223 
Table       Blue               124 

       cube查询出的个数=count( distinct item)+count(distinct color)+count(distinct (item || item))+1

       需要注意的是：cube效率远低于rollup

3：grouping sets:用法：group by grouping sets（(a,b),c）

                            含义：rollup和cube是特定情况下的小计，而grouping sets可以自定义对那些分组进行小计，比如此例中是只对（a，b）和（c）这俩个分组进行小计。

                                       grouping sets（）也可以为空，表示对全表进行group by

                                       不再举例说明。

4：在cube和rollup函数中也可以使用 grouping（）和grouping_id()、group_id()函数。

      grouping（）：只有一个参数，而且必须是group by中出现的列，当列值存在时返回0，不存在时返回1，一般结合decode（）或者case when使用。

                               对1中的sql稍作修改，

                select decode(grouping(item),'0',item,'1','合计'),decode(grouping(color),'0',color,'1','小计'),SUM(Quantity) from Inventory group by rollup(item,color);

                               返回结果集如下：

                

ITEM        COLOR    SUM(QUANTITY)
-------     ------   -------------
Chair       Red                210
Chair       Blue               101
Chair       小计               311
Table       Red                223
Table       Blue               124
Table       小计               347
     合计     小计                658

      grouping_id()：可以接受多个参数，必须是group by中出现的列，grouping_id()的返回值其实就是参数中的每列的grouping()值的二进制向量，例如如果grouping(A)=1，

                               grouping(B)=0，则grouping_id(A,B)的返回值就是二进制的10，转成10进制就是2。通常用于筛选小计结果。

      group_id():当group by 与cube，rollup组合使用时，有时会出现重复的数据，这时可以用group_id来去重。

                       比如：

select division_id,job_id,sum(salary) from employees group by division_id,rollup(division_id,job_id);

                       很明显会出现重复的数据，这时使用以下sql便可以去除重复数据。

select division_id,job_id,group_id(),sum(salary)
  from employees2
  group by division_id,rollup(division_id,job_id)
  having group_id()=0;