【数据统计分析】详解Oracle分组函数之CUBE

01

看一下ROLLUP的数据统计效果

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

 

 

---

02

CUBE函数

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.”

---

03

CUBE与ROLLUP之间的细微差别

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的基础上进一步从各种维度上给出细化的统计汇总结果。