rollup和cube区别及grouping用法???

Question

CREATE TEST TABLE AND INSERT TEST DATA.
                create table students
                (id number(15,0),
                area varchar2(10),
                stu_type varchar2(2),
                score number(20,2));
                
                insert into students values(1, '111', 'g', 80 );
                insert into students values(1, '111', 'j', 80 );
                insert into students values(1, '222', 'g', 89 );
                insert into students values(1, '222', 'g', 68 );
                insert into students values(2, '111', 'g', 80 );
                insert into students values(2, '111', 'j', 70 );
                insert into students values(2, '222', 'g', 60 );
                insert into students values(2, '222', 'j', 65 );
                insert into students values(3, '111', 'g', 75 );
                insert into students values(3, '111', 'j', 58 );
                insert into students values(3, '222', 'g', 58 );
                insert into students values(3, '222', 'j', 90 );
                insert into students values(4, '111', 'g', 89 );
                insert into students values(4, '111', 'j', 90 );
                insert into students values(4, '222', 'g', 90 );
                insert into students values(4, '222', 'j', 89 );
                commit;
        
                col score format 999999999999.99
ROLLUP
        
                select id,area,stu_type,sum(score) score 
                from students
                group by rollup(id,area,stu_type)
                order by id,area,stu_type;
                
                /*--------理解rollup
                select a, b, c, sum( d )
                from t
                group by rollup(a, b, c);
                
                等效于
                
                select * from (
                 select a, b, c, sum( d ) from t group by a, b, c 
                 union all
                 select a, b, null, sum( d ) from t group by a, b
                 union all
                 select a, null, null, sum( d ) from t group by a
                 union all
                 select null, null, null, sum( d ) from t
                )
                */
        
CUBE
        
                select id,area,stu_type,sum(score) score 
                from students
                group by cube(id,area,stu_type)
                order by id,area,stu_type;
                
                /*--------理解cube
                select a, b, c, sum( d ) from t
                group by cube( a, b, c)
                
                等效于
                
                select a, b, c, sum( d ) from t
                group by grouping sets( 
                     ( a, b, c ), 
                     ( a, b ), ( a ), ( b, c ), 
                     ( b ), ( a, c ), ( c ), 
                     () )
                )
                */ GROUPING
        
                从上面的结果中我们很容易发现,每个统计数据所对应的行都会出现null,
                如何来区分到底是根据那个字段做的汇总呢,grouping函数判断是否合计列!
                
                select decode(grouping(id),1,'all id',id) id,
                        decode(grouping(area),1,'all area',to_char(area)) area,
                        decode(grouping(stu_type),1,'all_stu_type',stu_type) stu_type,
                        sum(score) score
                from students
                group by cube(id,area,stu_type)
                order by id,area,stu_type;

Answer 1

rollup(参数1)，ROLLUP的参数只应该有1个，就算你写了3个参数，起作用的也只有一个，CUBE（参数1，参数2），允许有3个参数，至于楼上的3个参数，从来没用过。

Answer 2

呵呵，楼上没真正弄明白rollup的用法，括号里字段个数跟分组字段个数和需求有关，没有具体限制，leah(芳) 说的比较明白了，grouping方法比较适合用在单个字段分组的情况，对于多个字段分组用grouping_id(col1,col2..)会方便很多，另外与rollup和cube相关的用法还有group_id(),在这里我之前也解决过很多这方面的例子，可以搜索下，或者网上搜，相关资料很多的

Answer 3

rollup(col1, col2,...) 和 cube(col1, col2,...) 用法区别在 cube 在 rollup 汇总的记录集上，还会增加对 col2 等字段的汇总；
grouping() 是用来判断是对那个字段进行的汇总

Answer 4

呵呵，XIAOXIAO的说法是正确的，我的意思也是那样，ROLLUP只对第一个参数进行汇总，CUBE可以依次汇总，所以ROLLUP中参数个数只有一个会起作用，写在多也是枉然。不知道我的理解对吗？

Answer 5

rollup只有第一个参数起作用也是理解不正确的，先看看例子吧：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
合计       合计               117 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          合计                86 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                   66 rows selectedSQL> 
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
总计 总计       总计               2113 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
1，1，1