Oracle的group by聚合函数扩展cube rollup和grouping sets

转载 2013年12月02日 14:49:05

Oracle的group by聚合函数扩展cube rollup和grouping sets

聚合函数是oracle数据仓库的基础。为了提高距合的性能,oracle提供了group by条款的扩展。

ü         cube

ü         rollup

ü         grouping

ü         grouping sets

这几个对sql的扩展使得查询和报告都变得简单和迅速。用户通过使用这几个扩展功能,可以1,简化代码编程;2,快速有效的查询处理;3,减少客户端和网络负载。本文以实例的方式深入解析这几种扩展的具体含义和使用环境。

考虑如下关系表。

create table mytest(

subcompany_id varchar2(10),

subcompany_name varchar2(40),

branch_id varchar2(10),

branch_name varchar2(40),

region_id varchar2(10),

region_name varchar2(40),

customer_id varchar2(10),

customer_name varchar2(40),

market_id varchar2(10),

market_name varchar2(49),

sales_count numeric(10,3)

);

 

comment on table mytest is '测试表';

comment on column mytest.subcompany_id is '分公司编号';

comment on column mytest.subcompany_name is '分公司名称';

comment on column mytest.branch_id is '经营部编号';

comment on column mytest.branch_name is '经营部名称';

comment on column mytest.region_id is '片区编号';

comment on column mytest.region_name is '片区名称';

comment on column mytest.customer_id is '客户编号';

comment on column mytest.customer_name is '客户名称';

comment on column mytest.market_id is '所属市场级别';

comment on column mytest.market_name is '市场级别名称';

comment on column mytest.sales_count is '销售数量';

insert into mytest(subcompany_id,subcompany_name,branch_id,branch_name,region_id,region_name,customer_id,customer_name,market_id,market_name,sales_count)

values('01','分公司1','0101','经营部1','010101','片区1','01010101','客户1','01','片区1',1);

insert into mytest(subcompany_id,subcompany_name,branch_id,branch_name,region_id,region_name,customer_id,customer_name,market_id,market_name,sales_count)

values('01','分公司1','0101','经营部1','010101','片区1','01010102','客户2','02','片区2',1);

insert into mytest(subcompany_id,subcompany_name,branch_id,branch_name,region_id,region_name,customer_id,customer_name,market_id,market_name,sales_count)

values('01','分公司1','0101','经营部1','010102','片区2','01010201','客户1','01','片区1',1);

insert into mytest(subcompany_id,subcompany_name,branch_id,branch_name,region_id,region_name,customer_id,customer_name,market_id,market_name,sales_count)

values('01','分公司1','0101','经营部1','010102','片区2','01010202','客户2','02','片区2',1);

insert into mytest(subcompany_id,subcompany_name,branch_id,branch_name,region_id,region_name,customer_id,customer_name,market_id,market_name,sales_count)

values('01','分公司1','0102','经营部1','010201','片区1','01020101','客户1','01','片区1',1);

insert into mytest(subcompany_id,subcompany_name,branch_id,branch_name,region_id,region_name,customer_id,customer_name,market_id,market_name,sales_count)

values('01','分公司1','0102','经营部1','010202','片区2','01020202','客户2','02','片区2',1);

 

rollup

rollup的行为非常直接,它根据grouping list的rollup条款创建合计:

首先,它计算grouping条款的标准聚合。

然后,它按照grouping list列从右到左进行更高层的聚合。

最后,创建n+1层的总计。

例如: group by rollup(A,B,C),则oracle最后得到的聚合结果为(A,B,C), (A,B), (A),()

Rollup对group by 的扩展比较简单,但非常高效,对一个查询增加的开销非常少。

考虑如下查询。

select subcompany_name,branch_name,region_name,customer_name,sum(sales_count) 
from mytest
group by rollup(subcompany_name,branch_name,region_name,customer_name);

分公司1  经营部1 片区1     客户1     3  

分公司1  经营部1 片区1     客户2     2

分公司1  经营部1 片区1            5

分公司1  经营部1 片区2     客户1     2

分公司1  经营部1 片区2     客户2     3

分公司1  经营部1 片区2            5

分公司1  经营部1               10

分公司1  经营部2 片区1     客户1     1

分公司1  经营部2 片区1            1

分公司1  经营部2 片区2     客户2     1

分公司1  经营部2 片区2            1

分公司1  经营部2               2

分公司1                       12

                      12

上面的查询返回如下结果行:

1,  基于subcompany_name,branch_name,region_name,customer_name的聚合。

2,  基于subcompany_name,branch_name,region_name的聚合。

3,  基于subcompany_name,branch_name的聚合。

4,  基于subcompany_name的聚合。

5,  总计

用户还可以使用rollup包含有限的几个小计,语法如下:

Group by expr1,rollup(expr2,expr3)这种情况下,group by条款创建2+1层小计。层次为(expr1,expr2,expr3)(expr1,expr2)(expr1)

cube

    Cube进行grouping列规定的grouping,创建所有可能的聚合,例如规定了cube(customer_name,market_name)那么返回的结果将是2的2次方个。分别为(customer_name,market_name)(market_name)(customer_name)()

    Cube扩展在计算交叉报表时非常有用,当然我们可以使用select union all替代rollup或cube,但这需要很多的select union all语句。这样做不仅不够高效,而且加长了sql语句,不方便维护和扩充。

考虑如下查询:

select subcompany_name,branch_name,region_name,
customer_name,market_name,sum(sales_count) 
from mytest
group by subcompany_name,branch_name,region_name,cube(customer_name,market_name) ;

分公司1 经营部1 片区1                   5

分公司1 经营部1 片区1            片区1     3

分公司1 经营部1 片区1            片区2     2

分公司1 经营部1 片区1     客户1            3

分公司1 经营部1 片区1     客户1     片区1     3

分公司1 经营部1 片区1     客户2            2

分公司1 经营部1 片区1     客户2     片区2     2

分公司1 经营部1 片区2                   5

分公司1 经营部1 片区2            片区1     2

分公司1 经营部1 片区2            片区2     3

分公司1 经营部1 片区2     客户1            2

分公司1 经营部1 片区2     客户1     片区1     2

分公司1 经营部1 片区2     客户2            3

分公司1 经营部1 片区2     客户2     片区2     3

分公司1 经营部2 片区1                   1

分公司1 经营部2 片区1            片区1     1

分公司1 经营部2 片区1     客户1            1

分公司1 经营部2 片区1     客户1     片区1     1

分公司1 经营部2 片区2                   1

分公司1 经营部2 片区2            片区2     1

分公司1 经营部2 片区2     客户2            1

分公司1 经营部2 片区2     客户2     片区2     1

 

 

联合使用rollupcube来解决特殊查询需求

rollup 和cube带来的一个问题是,在返会的结果中如何能准确区分出那些是小计,哪些是汇总数据呢。这点可以使用grouping和grouping_id函数解决。

另外,我们还可以通过having过虑掉我们不希望在结果中出现的数据。

考虑查询:给出所有机构的小计,并在此基础上给出各市场级别的小计。

方案1:

使用cube计算所有的结果,然后用having过虑得出符合条件的结果集合。

select 
grouping(subcompany_name),
grouping(branch_name),
grouping(region_name),
grouping(customer_name),
grouping(market_name),
subcompany_name,branch_name,region_name,
customer_name,market_name,sum(sales_count) 
from mytest
group by cube(subcompany_name,branch_name,region_name,customer_name,market_name) 
having(grouping(subcompany_name) <= grouping(branch_name) and 
grouping
(branch_name) <= grouping(region_name) and 
grouping
(region_name) <= grouping(customer_name));

这种方案的缺点在于,首先使用cube计算所有可能的汇总结果需要花费相当长的时间;其次由于结果给出了所有可能的汇总,而我们需要的只是其中很小一部分,这种情况下使用having过虑结果集也是一件很麻烦的事情。

方案2

使用嵌套查询,先得出rollup的结果,然后再利用现有结果跟market进行cube的group by计算。


select subcompany_name,branch_name,region_name,
customer_name,market_name,sum(sales_count) 
from (
select 
subcompany_name,branch_name,region_name,
customer_name,market_name,sum(sales_count) sales_count
from mytest
group by rollup(subcompany_name,branch_name,region_name,customer_name) ,market_name
)
group by subcompany_name,branch_name,region_name,customer_name,cube(market_name)
这种方式多执行了一次查询,代码长度增加,可读性也不够强。另外还要主意过虑掉在里层查询中已经汇总的结果。

   方案3

    联合rollup和cube。

   
select 
subcompany_name,branch_name,region_name,
customer_name,market_name,sum(sales_count) sales_count
from mytest
group by rollup(subcompany_name,branch_name,region_name,customer_name) , cube(market_name)
order by grouping(subcompany_name),
grouping(branch_name),
grouping(region_name),
grouping(customer_name),
grouping(market_name);

grouping sets

grouping sets提供了指定汇总集合条件的功能。例如在上面的查询中,我们可以通过为select group by语句制定汇总条件()

select 
subcompany_name,branch_name,region_name,
customer_name,market_name,sum(sales_count) sales_count
from mytest
group by grouping sets((subcompany_name,branch_name,region_name,customer_name) ,
                       (subcompany_name,branch_name,region_name) ,
                       (subcompany_name,branch_name) ,
                       (subcompany_name) ,
                       (market_name) ,
                       (subcompany_name,branch_name,region_name,customer_name,market_name) ,
                       (subcompany_name,branch_name,region_name,market_name) ,
                       (subcompany_name,branch_name,market_name) ,
                       (subcompany_name,market_name) ,
                       () )

Oracle中group by 的扩展函数rollup、cube、grouping sets

Oracle的group by除了基本用法以外,还有3种扩展用法,分别是rollup、cube、grouping sets,分别介绍如下: 1、rollup 对数据库表emp,假设其中两个字段名为...

[Oracle] Group By 语句的扩展 - Rollup、Cube和Grouping Sets

经常写SQL语句的人应该知道Group by语句的主要用法是进行分类汇总,下面是一种它最常见的用法(根据部门、职位分别统计业绩): SELECT a.dname,b.job,SUM(b.sal) s...

Hive_6. 数据聚合 -- Group By & Grouping_SETS & RollUp & CUBE & Having

先完善列表结构,会后续填充内容
  • Mike_H
  • Mike_H
  • 2015年12月03日 15:24
  • 2028

Oracle group by高级用法对比效果(ROLLUP、GROUPING SETS、CUBE)

Oracle group by高级用法对比效果(ROLLUP、GROUPING SETS、CUBE),今天主要跟大家演示一下,在同一组数据的情况下,每个SQL的执行结果。      一:普通的grou...

GROUP BY中ROLLUP/CUBE/GROUPING/GROUPING SETS使用示例

GROUP BY中ROLLUP/CUBE/GROUPING/GROUPING SETS使用示例 2013-11-01 10:16:49     我来说两句    来源:还不算晕的专栏   收藏  ...
  • ahhbgxy
  • ahhbgxy
  • 2014年11月03日 15:20
  • 167

oracle提供的分析函数 cube(),rollup(),grouping sets()

1.   Oracle的rollup、cube、grouping sets函数 收藏 Oracle的group by除了基本用法以外,还有3种扩展用法,分别是rollup、cube、groupi...

Oracle的rollup、cube、grouping sets函数

Oracle的rollup、cube、grouping sets函数Oracle的group by除了基本用法以外,还有3种扩展用法,分别是rollup、cube、grouping sets。1 ro...

Jarno详解Oracle高级分组函数(ROLLUP, CUBE, GROUPING SETS)

本文主要讲解 ROLLUP, CUBE, GROUPING SETS的主要用法,这些函数可以理解为Group By分组函数封装后的精简用法,相当于多个union all 的组合显示效果,但是要比 多个...
内容举报
返回顶部
收藏助手
不良信息举报
您举报文章:Oracle的group by聚合函数扩展cube rollup和grouping sets
举报原因:
原因补充:

(最多只允许输入30个字)