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
  • 1521

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     我来说两句    来源:还不算晕的专栏   收藏 &...

Oracle的rollup、cube、grouping sets函数

Oracle的group by除了基本用法以外,还有3种扩展用法,分别是rollup、cube、grouping sets。 1 rollup 假设有一个表test,有A、B、C、D、E5列。 如果使...

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

本文主要讲解 ROLLUP, CUBE, GROUPING SETS的主要用法,这些函数可以理解为Group By分组函数封装后的精简用法,相当于多个union all 的组合显示效果,但是要比 多个...

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

1.   Oracle的rollup、cube、grouping sets函数 收藏 Oracle的group by除了基本用法以外,还有3种扩展用法,分别是rollup、cube、groupi...
内容举报
返回顶部
收藏助手
不良信息举报
您举报文章:深度学习:神经网络中的前向传播和反向传播算法推导
举报原因:
原因补充:

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