Oracle RULLUP和CUBE

来源：互联网发布：网络如何人肉搜索编辑：程序博客网时间：2024/04/30 02:26

RULLUP和CUBE 笔记，分享。

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
合计合计 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
0，0，0
0，0，1
0，1，1
1，1，1 （此处可以理解为有数字1的地方存在统计）
而对于cube(part,grade,ID),grouping_id(part,grade,ID)的值范围在0-7之间即
part,grade,ID
0，0，0
0，0，1
0，1，0
0，1，1
1，0，0
1，0，1
1，1，0
1，1，1 （此处可以理解为有数字1的地方存在统计）



总结：观察一下，CUBE与ROLLUP之间的细微差别
rollup(a,b) 统计列包含：(a,b)、(a)、()
rollup(a,b,c) 统计列包含：(a,b,c)、(a,b)、(a)、()
……以此类推ing……

cube(a,b) 统计列包含：(a,b)、(a)、(b)、()
cube(a,b,c) 统计列包含：(a,b,c)、(a,b)、(a,c)、(b,c)、(a)、(b)、(c)、()
……以此类推ing……

0 0