ITPub博客

首页 > 数据库 > Oracle > 【oracle】分析函数之Rollup&Cube

【oracle】分析函数之Rollup&Cube

Oracle 作者:386772809 时间:2013-11-26 18:16:51 0 删除 编辑

  今天看存储过程的时候发现了两个函数Rollup()和Cube(),感觉很陌生,于是网上一通查,当想做笔记的时候,发现笔记本上曾近有这两个函数,【oracle】分析函数之Rollup&Cube,记忆力减退的不是一星半点啊。或许是我之前一直没有好好理解这两个函数,在工作中也很少用到,之前做采集的时候都是使用的ODI工具,很少使用存储过程。而且最经也将oracle放置了好久,今天起开始一天天的拾起来。

 

Rollup():分组函数可以理解为group by的精简模式,具体分组模式如下:

Rollup(a,b,c): (a,b,c),(a,b),(a),(全表)

Cube():分组函数也是以group by为基础,具体分组模式如下:

cube(a,b,c):(a,b,c),(a,b),(a,c),(b,c),(a),(b),(c),(全表)

 

下面准备数据比较一下两个函数的不同:

1、准备数据:

【oracle】分析函数之Rollup&Cube

2、使用rollup函数查询

select group_id,job,name,sum(salary) from GROUP_TEST group by rollup(group_id,job,name);

【oracle】分析函数之Rollup&Cube

【oracle】分析函数之Rollup&Cube


3、使用cube函数:

select group_id,job,name,sum(salary) from GROUP_TEST group by cube(group_id,job,name)

【oracle】分析函数之Rollup&Cube
【oracle】分析函数之Rollup&Cube
【oracle】分析函数之Rollup&Cube
【oracle】分析函数之Rollup&Cube
【oracle】分析函数之Rollup&Cube

4、对比:从最后查询出来的数据条数就差了好多,下面看一下将两个函数从转化成对应的group函数语句:

 

rollup函数:

select group_id,job,name,sum(salary) from GROUP_TEST group by rollup(group_id,job,name);

等价于:

select group_id,job,name,sum(salary) from GROUP_TEST group by group_id,job,name
union all
select group_id,job,null,sum(salary) from GROUP_TEST group by group_id,job
union all
select group_id,null,null,sum(salary) from GROUP_TEST group by group_id
union all
select null,null,null,sum(salary) from GROUP_TEST

 

cube函数:

select group_id,job,name,sum(salary) from GROUP_TEST group by cube(group_id,job,name) ;

等价于:

select group_id,job,name,sum(salary) from GROUP_TEST group by group_id,job,name
union all
select group_id,job,null,sum(salary) from GROUP_TEST group by group_id,job
union all
select group_id,null,name,sum(salary) from GROUP_TEST group by group_id,name
union all
select group_id,null,null,sum(salary) from GROUP_TEST group by group_id
union all
select null,job,name,sum(salary) from GROUP_TEST group by job,name
union all
select null,job,null,sum(salary) from GROUP_TEST group by job
union all
select null,null,name,sum(salary) from GROUP_TEST group by name
union all
select null,null,null,sum(salary) from GROUP_TEST

 

5、由此可见两个函数对于汇总统计来说要比普通函数好用的多,另外还有一个配套使用的函数

grouping(**):当**字段为null的时候值为1,当字段**非null的时候值为0;

select grouping(group_id),job,name,sum(salary) from GROUP_TEST group by rollup(group_id,job,name);

 

6、添加一列用来直观的显示所有的汇总字段:

select group_id,job,name,
 case when name is null and nvl(group_id,0)=0 and job is null   then '全表聚合'
   when name is null and nvl(group_id,0)=0 and job is not null then 'JOB聚合'
   when name is null and  grouping(group_id)=0 and job is null then 'GROUPID聚合'
   when name is not null and nvl(group_id,0)=0 and job is null   then 'Name聚合'
   when name is not null and grouping(group_id)=0 and job is null   then 'GROPName聚合'
   when name is not null and grouping(group_id)=1 and job is not null   then 'JOBName聚合'
   when name is  null and grouping(group_id)=0 and job is not null   then 'GROUPJOB聚合'
    else
 '三列汇总' end ,
sum(salary) from GROUP_TEST group by cube(group_id,job,name) ;

<!-- 正文结束 -->

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/23556106/viewspace-1116565/,如需转载,请注明出处,否则将追究法律责任。

上一篇: 没有了~
下一篇: 没有了~
请登录后发表评论 登录
全部评论

注册时间:2010-03-20