oracle rollup cube 用法

oracle rollup cube 用法

1、Oracle ROLLUP和CUBE 用法    
      Oracle的GROUP BY语句除了最基本的语法外，还支持ROLLUP和CUBE语句。

       如果是Group by  ROLLUP(A, B, C)的话，首先会对(A、B、C)进行GROUP BY，然后对(A、B)进行GROUP BY，然后是(A)进行GROUP BY，最后对全表进行GROUP BY操作。

       如果是GROUP BY CUBE(A, B, C)，则首先会对(A、B、C)进行GROUP BY，然后依次是(A、B)，(A、C)，(A)，(B、C)，(B)，(C)，最后对全表进行GROUP BY操作。 grouping_id()可以美化效果。除了使用GROUPING函数，还可以使用GROUPING_ID来标识GROUP BY的结果。

       也可以 Group by Rollup(A,(B,C)) ，Group by A Rollup(B,C)，…… 这样任意按自己想要的形式结合统计数据，非常方便。

 

2、实例

SQL>   select   grade,id,num   from   a;  
   
  GRADE             ID                                 NUM  
  ----------   ----------   ----------  
  a                     1                                       1  
  a                     2                                       2  
  b                     3                                       4  
  b                     4                                       4  
   
  对grade字段进行rollup：  
   
  SQL>   select   grade,sum(num)   from   a   group   by   rollup(grade);  
   
  GRADE                 SUM(NUM)  
  ----------   ----------  
  a                                       3  
  b                                       8  
                                        11  
  同时对grade和id字段进行rollup  
  SQL>   SELECT   decode(grouping_id(grade,ID),2,'小计',3,'合计',grade)   grade,  
      2                   decode(grouping_id(grade,ID),1,'小计',3,'合计',ID)   ID,  
      3                   SUM(num)  
      4     FROM   a   GROUP   BY   ROLLUP(grade,ID)  
      5     /  
   
  GRADE             ID                       SUM(NUM)  
  ----------   ----------   ----------  
  a                     1                                       1  
  a                     2                                       2  
  a                     小计                                 3  
  b                     3                                       4  
  b                     4                                       4  
  b                     小计                                 8  
  合计               合计                               11  
   
  7   rows   selected  
   
  再看看先对grade分组，再对id进行rollup的情况：  
   
  SQL>   SELECT   grade,  
      2                   decode(GROUPING(ID),1,'合计',ID)   ID,  
      3                   SUM(num)  
      4     FROM   a   GROUP   BY   grade,rollup(ID)  
      5     /  
   
  GRADE             ID                       SUM(NUM)  
  ----------   ----------   ----------  
  a                     1                                       1  
  a                     2                                       2  
  a                     合计                                 3  
  b                     3                                       4  
  b                     4                                       4  
  b                     合计                                 8  
   
  6   rows   selected  
   
  这里GROUP   BY   grade,rollup(ID)跟你的理解应该很相近了，而且可以看出GROUP   BY   grade,rollup(ID)结果跟ROLLUP(grade,ID)很类似，只是少了最后1行总合计，但是也可以就看出rollup多个字段时并不是只有1个字段起作用的  
   
  可以认为你理解的是只对第一个字段的累计，跟GROUP   BY   grade,rollup(ID)的结果很接近，再看rollup3个字段的情况：  
  SQL>   select   part,grade,id,num   from   a;  
   
  PART   GRADE             ID                                 NUM  
  ----   ----------   ----------   ----------  
  p1       a                     1                                       1  
  p1       a                     2                                       2  
  p1       b                     3                                       3  
  p1       b                     4                                       4  
  p2       c                     5                                       5  
  p2       d                     6                                       6  
   
  6   rows   selected  
   
  SQL>    
  SQL>   SELECT   decode(grouping_id(part,grade,ID),7,'总计',part)   part,  
      2                   decode(grouping_id(part,grade,ID),3,'小计',7,'总计',grade)   grade,  
      3                   decode(grouping_id(part,grade,ID),1,'小计',3,'小计',7,'总计',ID)   ID,  
      4                   SUM(num)  
      5     FROM   a   GROUP   BY   ROLLUP(part,grade,ID)  
      6     /  
   
  PART   GRADE             ID                       SUM(NUM)  
  ----   ----------   ----------   ----------  
  p1       a                     1                                       1  
  p1       a                     2                                       2  
  p1       a                     小计                                 3  
  p1       b                     3                                       3  
  p1       b                     4                                       4  
  p1       b                     小计                                 7  
  p1       小计               小计                               10  
  p2       c                     5                                       5  
  p2       c                     小计                                 5  
  p2       d                     6                                       6  
  p2       d                     小计                                 6  
  p2       小计               小计                               11  
  总计   总计               总计                               21  
   
  13   rows   selected  
   
  这里不光只对第一个字段做了累计，先按(part,grade,ID)分组累计,然后按(part,grade)分组累计，再按(part)分组累计，最后累计全部  
  再看看rollup   和   cube的区别：  
  对于ROLLUP(part,grade,ID),grouping_id(part,grade,ID)的值范围在（0，1，3，7）间即  
  part,grade,ID（作为合计时计为1）  
  0，0，0  
  0，0，1  
  0，1，1  
  1，1，1  
  而对于cube(part,grade,ID),grouping_id(part,grade,ID)的值范围在0-7之间即  
  part,grade,ID（作为合计时计为1）  
  0，0，0  
  0，0，1  
  0，1，0  
  0，1，1  
  1，0，0  
  1，0，1  
  1，1，0