oracle分组_一文学会Oracle数据库分组函数之CUBE

最新推荐文章于 2023-04-13 20:07:32 发布

辛载阳

最新推荐文章于 2023-04-13 20:07:32 发布

阅读量148

点赞数

文章标签： oracle分组

本文链接：https://blog.csdn.net/weixin_36293915/article/details/113309907

版权

概述

前面已经介绍了rollup的用法，而Oracle的CUBE与ROLLUP功能很相似，也是在数据统计分析领域经常用到的一个函数，下面一起来看看吧。

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