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操作。
Rollup(a,b) 统计列包含:(a,b)、(a)、()
Rollup(a,b,c) 统计列包含:(a,b,c)、(a,b)、(a)、()
如果是GROUP BY CUBE(A, B, C),则首先会对(A、B、C)进行GROUP BY,然后依次是(A、B),(A、C),(A),(B、C),(B),(C),最后对全表进行GROUP BY操作。
Cube(a,b) 统计列包含:(a,b)、(a)、(b)、()
Cube(a,b,c) 统计列包含:(a,b,c)、(a,b)、(a,c)、(b,c)、(a)、(b)、(c)、()
也可以 Group by Rollup(A,(B,C)) ,Group by A Rollup(B,C),…… 这样任意按自己想要的形式结合统计数据,非常方便。
grouping_id()可以美化效果。除了使用GROUPING函数,还可以使用GROUPING_ID来标识GROUP BY的结果。
2、实例
1)创建测试表group_test
create table group_test (group_id int, job varchar2(10), name varchar2(10), salary int);
2)初始化数据
insert into group_test values (10,'Coding', 'Bruce',1000);
insert into group_test values (10,'Programmer','Clair',1000);
insert into group_test values (10,'Architect', 'Gideon',1000);
insert into group_test values (10,'Director', 'Hill',1000);
insert into group_test values (20,'Coding', 'Jason',2000);
insert into group_test values (20,'Programmer','Joey',2000);
insert into group_test values (20,'Architect', 'Martin',2000);
insert into group_test values (20,'Director', 'Michael',2000);
insert into group_test values (30,'Coding', 'Rebecca',3000);
insert into group_test values (30,'Programmer','Rex',3000);
insert into group_test values (30,'Architect', 'Richard',3000);
insert into group_test values (30,'Director', 'Sabrina',3000);
insert into group_test values (40,'Coding', 'Samuel',4000);
insert into group_test values (40,'Programmer','Susy',4000);
insert into group_test values (40,'Architect', 'Tina',4000);
insert into group_test values (40,'Director', 'Wendy',4000);
commit;
---------------------
3)ROLLUP的数据统计效果
select group_id,
job,
grouping_id(group_id, job),
grouping(GROUP_ID),
grouping(JOB),
sum(salary)
from group_test
group by rollup(group_id, job);
4)CUBE的数据统计效果
select group_id,
job,
grouping_id(group_id, job),
grouping(GROUP_ID),
grouping(JOB),
sum(salary)
from group_test
group by cube(group_id, job)
order by 1;
解释如上结果中GROUPING函数返回值“0”和“1”的含义。
如果显示“1”表示CUBE函数对应的列(例如JOB字段)是由于CUBE函数所产生的空值对应的信息,即对此列进行汇总计算后的结果。
如果显示“0”表示此行对应的这列未参与ROLLUP函数分组汇总活动。
grouping_id其实就是所统计各列二进制的和。
3、小结
CUBE在ROLLUP的基础上进一步从各种维度上给出细化的统计汇总结果。