Oracle的CUBE与ROLLUP功能很相似,也是在数据统计分析领域的一把好手。
@H_404_3@
关于ROLLUP的查询统计功能请参考文章《【ROLLUP】Oracle分组函数之ROLLUP魅力》(http://space.itpub.net/519536/viewspace-610995)。
@H_404_3@
@H_404_3@1.先看一下ROLLUP的数据统计效果
@H_404_3@1)创建测试表group_test
@H_404_3@SECOOLER@ora11g> create table group_test (group_id int,job varchar2(10),name varchar2(10),salary int);
@H_404_3@Table created.
@H_404_3@2)初始化数据
@H_404_3@insert into group_test values (10,'Coding','Bruce',1000);
insert into group_test values (20,'Jason',2000);
insert into group_test values (30,'Rebecca',3000);
insert into group_test values (40,'Samuel',4000);
commit; @H_404_3@3)初始化之后的数据情况如下: @H_404_3@SECOOLER@ora11g> set pages 100 @H_404_3@SECOOLER@ora11g> select * from group_test; @H_404_3@ GROUP_ID JOB NAME SALARY @H_404_3@---------- ---------- ---------- ---------- @H_404_3@ 10 Coding Bruce 1000 @H_404_3@ 10 Programmer Clair 1000 @H_404_3@ 10 Architect Gideon 1000 @H_404_3@ 10 Director Hill 1000 @H_404_3@ 20 Coding Jason 2000 @H_404_3@ 20 Programmer Joey 2000 @H_404_3@ 20 Architect Martin 2000 @H_404_3@ 20 Director Michael 2000 @H_404_3@ 30 Coding Rebecca 3000 @H_404_3@ 30 Programmer Rex 3000 @H_404_3@ 30 Architect Richard 3000 @H_404_3@ 30 Director Sabrina 3000 @H_404_3@ 40 Coding Samuel 4000 @H_404_3@ 40 Programmer Susy 4000 @H_404_3@ 40 Architect Tina 4000 @H_404_3@ 40 Director Wendy 4000 @H_404_3@16 rows selected. @H_404_3@4)ROLLUP的数据统计效果 @H_404_3@sec@ora10g> select group_id,job,grouping(GROUP_ID),grouping(JOB),sum(salary) from group_test group by rollup(group_id,job); @H_404_3@ GROUP_ID JOB GROUPING(GROUP_ID) GROUPING(JOB) SUM(SALARY) @H_404_3@---------- ---------- ------------------ ------------- ----------- @H_404_3@ 10 Coding 0 0 1000 @H_404_3@ 10 Director 0 0 1000 @H_404_3@ 10 Architect 0 0 1000 @H_404_3@ 10 Programmer 0 0 1000 @H_404_3@ 10 0 1 4000 @H_404_3@ 20 Coding 0 0 2000 @H_404_3@ 20 Director 0 0 2000 @H_404_3@ 20 Architect 0 0 2000 @H_404_3@ 20 Programmer 0 0 2000 @H_404_3@ 20 0 1 8000 @H_404_3@ 30 Coding 0 0 3000 @H_404_3@ 30 Director 0 0 3000 @H_404_3@ 30 Architect 0 0 3000 @H_404_3@ 30 Programmer 0 0 3000 @H_404_3@ 30 0 1 12000 @H_404_3@ 40 Coding 0 0 4000 @H_404_3@ 40 Director 0 0 4000 @H_404_3@ 40 Architect 0 0 4000 @H_404_3@ 40 Programmer 0 0 4000 @H_404_3@ 40 0 1 16000 @H_404_3@ 1 1 40000 @H_404_3@21 rows selected. @H_404_3@2.进一步体验CUBE的魅力
404_3@ Architect 1 0 10000 @H_404_3@ Coding 1 0 10000 @H_404_3@ Director 1 0 10000 @H_404_3@ Programmer 1 0 10000 @H_404_3@25 rows selected. @H_404_3@ 解释如上结果中GROUPING函数返回值“0”和“1”的含义。 @H_404_3@ 如果显示“1”表示CUBE函数对应的列(例如JOB字段)是由于CUBE函数所产生的空值对应的信息,即对此列进行汇总计算后的结果。 @H_404_3@ 如果显示“0”表示此行对应的这列参未与ROLLUP函数分组汇总活动。 @H_404_3@ 如果还是没有理解清楚,请参见Oracle官方文档中的描述内容:“Using a single column as its argument,GROUPINGreturns 1 when it encounters aNULLvalue created by aROLLUPorCUBEoperation. That is,if theNULLindicates the row is a subtotal,GROUPINGreturns a 1. Any other type of value,including a storedNULL,returns a 0.” @H_404_3@3.仔细观察一下,CUBE与ROLLUP之间的细微差别 @H_404_3@rollup(a,b) 统计列包含:(a,b)、(a)、()
……以此类推ing…… @H_404_3@cube(a,b)、(a)、(b)、()
404_3@So,上面例子中CUBE的结果比ROLLUP多了下面关于第一列GROUP_ID的统计信息: @H_404_3@4.小结 @H_404_3@ CUBE在ROLLUP的基础上进一步从各种维度上给出细化的统计汇总结果。 @H_404_3@ CUBE与GROUP BY的关系可以参考Oracle官方文档中的例子,链接如下:,链接如下:《CUBE Extension to GROUP BY》http://docs.oracle.com/cd/E11882_01/server.112/e25554/aggreg.htm#DWHSG8614 @H_404_3@Good luck. @H_404_3@secooler @H_404_3@12.04.23 @H_404_3@-- The End --
insert into group_test values (20,'Jason',2000);
insert into group_test values (30,'Rebecca',3000);
insert into group_test values (40,'Samuel',4000);
commit; @H_404_3@3)初始化之后的数据情况如下: @H_404_3@SECOOLER@ora11g> set pages 100 @H_404_3@SECOOLER@ora11g> select * from group_test; @H_404_3@ GROUP_ID JOB NAME SALARY @H_404_3@---------- ---------- ---------- ---------- @H_404_3@ 10 Coding Bruce 1000 @H_404_3@ 10 Programmer Clair 1000 @H_404_3@ 10 Architect Gideon 1000 @H_404_3@ 10 Director Hill 1000 @H_404_3@ 20 Coding Jason 2000 @H_404_3@ 20 Programmer Joey 2000 @H_404_3@ 20 Architect Martin 2000 @H_404_3@ 20 Director Michael 2000 @H_404_3@ 30 Coding Rebecca 3000 @H_404_3@ 30 Programmer Rex 3000 @H_404_3@ 30 Architect Richard 3000 @H_404_3@ 30 Director Sabrina 3000 @H_404_3@ 40 Coding Samuel 4000 @H_404_3@ 40 Programmer Susy 4000 @H_404_3@ 40 Architect Tina 4000 @H_404_3@ 40 Director Wendy 4000 @H_404_3@16 rows selected. @H_404_3@4)ROLLUP的数据统计效果 @H_404_3@sec@ora10g> select group_id,job,grouping(GROUP_ID),grouping(JOB),sum(salary) from group_test group by rollup(group_id,job); @H_404_3@ GROUP_ID JOB GROUPING(GROUP_ID) GROUPING(JOB) SUM(SALARY) @H_404_3@---------- ---------- ------------------ ------------- ----------- @H_404_3@ 10 Coding 0 0 1000 @H_404_3@ 10 Director 0 0 1000 @H_404_3@ 10 Architect 0 0 1000 @H_404_3@ 10 Programmer 0 0 1000 @H_404_3@ 10 0 1 4000 @H_404_3@ 20 Coding 0 0 2000 @H_404_3@ 20 Director 0 0 2000 @H_404_3@ 20 Architect 0 0 2000 @H_404_3@ 20 Programmer 0 0 2000 @H_404_3@ 20 0 1 8000 @H_404_3@ 30 Coding 0 0 3000 @H_404_3@ 30 Director 0 0 3000 @H_404_3@ 30 Architect 0 0 3000 @H_404_3@ 30 Programmer 0 0 3000 @H_404_3@ 30 0 1 12000 @H_404_3@ 40 Coding 0 0 4000 @H_404_3@ 40 Director 0 0 4000 @H_404_3@ 40 Architect 0 0 4000 @H_404_3@ 40 Programmer 0 0 4000 @H_404_3@ 40 0 1 16000 @H_404_3@ 1 1 40000 @H_404_3@21 rows selected. @H_404_3@2.进一步体验CUBE的魅力
404_3@ Architect 1 0 10000 @H_404_3@ Coding 1 0 10000 @H_404_3@ Director 1 0 10000 @H_404_3@ Programmer 1 0 10000 @H_404_3@25 rows selected. @H_404_3@ 解释如上结果中GROUPING函数返回值“0”和“1”的含义。 @H_404_3@ 如果显示“1”表示CUBE函数对应的列(例如JOB字段)是由于CUBE函数所产生的空值对应的信息,即对此列进行汇总计算后的结果。 @H_404_3@ 如果显示“0”表示此行对应的这列参未与ROLLUP函数分组汇总活动。 @H_404_3@ 如果还是没有理解清楚,请参见Oracle官方文档中的描述内容:“Using a single column as its argument,GROUPINGreturns 1 when it encounters aNULLvalue created by aROLLUPorCUBEoperation. That is,if theNULLindicates the row is a subtotal,GROUPINGreturns a 1. Any other type of value,including a storedNULL,returns a 0.” @H_404_3@3.仔细观察一下,CUBE与ROLLUP之间的细微差别 @H_404_3@rollup(a,b) 统计列包含:(a,b)、(a)、()
……以此类推ing…… @H_404_3@cube(a,b)、(a)、(b)、()
404_3@So,上面例子中CUBE的结果比ROLLUP多了下面关于第一列GROUP_ID的统计信息: @H_404_3@4.小结 @H_404_3@ CUBE在ROLLUP的基础上进一步从各种维度上给出细化的统计汇总结果。 @H_404_3@ CUBE与GROUP BY的关系可以参考Oracle官方文档中的例子,链接如下:,链接如下:《CUBE Extension to GROUP BY》http://docs.oracle.com/cd/E11882_01/server.112/e25554/aggreg.htm#DWHSG8614 @H_404_3@Good luck. @H_404_3@secooler @H_404_3@12.04.23 @H_404_3@-- The End --