Oracle分组函数之CUBE魅力

发布时间:2014-10-22 13:25:58编辑:www.fx114.net 分享查询网我要评论
本篇文章主要介绍了"Oracle分组函数之CUBE魅力",主要涉及到Oracle分组函数之CUBE魅力方面的内容,对于Oracle分组函数之CUBE魅力感兴趣的同学可以参考一下。

Oracle的CUBE与ROLLUP功能很相似,也是在数据统计分析领域的一把好手。 先看一下ROLLUP的数据统计效果: 1)创建测试表group_test [email protected]> create table group_test (group_id int, job varchar2(10), name varchar2(10), salary int); 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,'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,'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,'Programmer','Susy',4000); insert into group_test values (40,'Architect', 'Tina',4000); insert into group_test values (40,'Director',  'Wendy',4000); [email protected]> set pages 100 [email protected]> select * from group_test; ---------- ---------- ---------- ----------         10 Coding     Bruce            1000         10 Programmer Clair            1000         10 Architect  Gideon           1000         10 Director   Hill             1000         20 Coding     Jason            2000         20 Programmer Joey             2000         20 Architect  Martin           2000         20 Director   Michael          2000         30 Coding     Rebecca          3000         30 Programmer Rex              3000         30 Architect  Richard          3000         30 Director   Sabrina          3000         40 Coding     Samuel           4000         40 Programmer Susy             4000         40 Architect  Tina             4000         40 Director   Wendy            4000 [email protected]> select group_id,job,grouping(GROUP_ID),grouping(JOB),sum(salary) from group_test group by rollup(group_id, job); ---------- ---------- ------------------ ------------- -----------         10 Coding                      0             0        1000         10 Director                    0             0        1000         10 Architect                   0             0        1000         10 Programmer                  0             0        1000         10                             0             1        4000         20 Coding                      0             0        2000         20 Director                    0             0        2000         20 Architect                   0             0        2000         20 Programmer                  0             0        2000         20                             0             1        8000         30 Coding                      0             0        3000         30 Director                    0             0        3000         30 Architect                   0             0        3000         30 Programmer                  0             0        3000         30                             0             1       12000         40 Coding                      0             0        4000         40 Director                    0             0        4000         40 Architect                   0             0        4000         40 Programmer                  0             0        4000         40                             0             1       16000                                        1             1       40000 [email protected]> select group_id,job,grouping(GROUP_ID),grouping(JOB),sum(salary) from group_test group by cube(group_id, job) order by 1; ---------- ---------- ------------------ ------------- -----------         10 Architect                   0             0        1000         10 Coding                      0             0        1000         10 Director                    0             0        1000         10 Programmer                  0             0        1000         10                             0             1        4000         20 Architect                   0             0        2000         20 Coding                      0             0        2000         20 Director                    0             0        2000         20 Programmer                  0             0        2000         20                             0             1        8000         30 Architect                   0             0        3000         30 Coding                      0             0        3000         30 Director                    0             0        3000         30 Programmer                  0             0        3000         30                             0             1       12000         40 Architect                   0             0        4000         40 Coding                      0             0        4000         40 Director                    0             0        4000         40 Programmer                  0             0        4000         40                             0             1       16000            Architect                   1             0       10000            Coding                      1             0       10000            Director                    1             0       10000            Programmer                  1             0       10000                                        1             1       40000   如果显示“1”表示CUBE函数对应的列(例如JOB字段)是由于CUBE函数所产生的空值对应的信息,即对此列进行汇总计算后的结果。   如果显示“0”表示此行对应的这列参未与ROLLUP函数分组汇总活动。   如果还是没有理解清楚,请参见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.” rollup(a,b)   统计列包含:(a,b)、(a)、() rollup(a,b,c) 统计列包含:(a,b,c)、(a,b)、(a)、() ……以此类推ing…… cube(a,b,c)   统计列包含:(a,b,c)、(a,b)、(a,c)、(b,c)、(a)、(b)、(c)、() ……以此类推ing……            Architect                   1             0       10000            Coding                      1             0       10000            Director                    1             0       10000 小结: CUBE在ROLLUP的基础上进一步从各种维度上给出细化的统计汇总结果。 本文来自互联网,早已不明出处..感谢作者,感谢伟的INTERNAT...


上一篇:python18 命名、变量、代码、函数
下一篇:features.conf配置文件参数配置

相关文章

相关评论

本站评论功能暂时取消,后续此功能例行通知。

一、不得利用本站危害国家安全、泄露国家秘密,不得侵犯国家社会集体的和公民的合法权益,不得利用本站制作、复制和传播不法有害信息!

二、互相尊重,对自己的言论和行为负责。

好贷网好贷款