phpcube运算技巧_数据统计分析详解Oracle分组函数之CUBE

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(GROUP_ID),grouping(JOB),sum(salary) from group_test group by rollup(group_id, job);

select group_id,job,grouping(GROUP_ID),grouping(JOB),sum(salary) from group_test group by cube(group_id, job) order by 1;

解释：

GROUPING函数返回值如果显示“1”表示CUBE函数对应的列（例如JOB字段）是由于CUBE函数所产生的空值对应的信息，即对此列进行汇总打算后的结果。

GROUPING函数返回值如果显示“0”表示此行对应的这列参未与ROLLUP函数分组汇总活动。

“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)、()

……以此类推……

cube(a,b) 统计列包含：(a,b)、(a)、(b)、()

cube(a,b,c) 统计列包含：(a,b,c)、(a,b)、(a,c)、(b,c)、(a)、(b)、(c)、()

……以此类推……

CUBE在ROLLUP的根本上进一步从各种维度上给出细化的统计汇总结果。

后面会分享更多devops和DBA方面的内容，感兴趣的朋友可以关注一下~