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、GROUPING SETS、CUBE)

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

group by cube,rollup, grouping set的用法

我们平时用的最多的就是  select sum(数量) from  表名 group by 部门,姓名 with ur; 其实db2关于group by  还有几个其他用法,x现将学习笔记贴出来: ...
  • xing_sky
  • xing_sky
  • 2012年09月15日 10:17
  • 5285

SQL SERVER中GROUPING SETS,CUBE,ROLLUP

用实例解释SQL SERVER中GROUPING SETS,CUBE,ROLLUP
  • lihui_830501
  • lihui_830501
  • 2014年03月25日 00:42
  • 1808

Oracle分组ROLLUP、GROUP BY、GROUPING、GROUPING SETS区别和作用

 1.ROLLUP   ROLLUP的作用相当于 SQL> set autotrace on SQL> select department_id,job_id,count(*)  ...
  • cuiyan1982
  • cuiyan1982
  • 2015年06月03日 13:08
  • 259

hive grouping sets 和 cube 用法

grouping sets 和cube基本知识。基础知识可参考 http://blog.csdn.net/mashroomxl/article/details/22578471grouping set...
  • u010670689
  • u010670689
  • 2015年06月29日 18:09
  • 3407

HIVE分析窗口函数: GROUPING SETS,GROUPING__ID,CUBE,ROLLUP

本文转载自:lxw的大数据田地 » Hive分析窗口函数(五) GROUPING SETS,GROUPING__ID,CUBE,ROLLUP 这几个分析函数通常用于OLAP中,不能累加,而且...
  • doegoo
  • doegoo
  • 2015年10月19日 14:10
  • 506

group by rollup 和grouping的使用实例

GROUPING函数可以接受一列,返回0或者1。如果列值为空,那么GROUPING()返回1;如果列值非空,那么返回0。GROUPING只能在使用ROLLUP或CUBE的查询中使用。当需要在返回空值的...
  • u010302650
  • u010302650
  • 2013年06月18日 10:49
  • 1146

Hive中with cube、with rollup、grouping sets用法

利用with cube、with rollup、grouping sets等进行分组查询
  • suiyingli39
  • suiyingli39
  • 2016年12月09日 16:38
  • 1561

Oracle ROLLUP和CUBE、grouping_id() 用法

Oracle的GROUP BY语句除了最基本的语法外,还支持ROLLUP和CUBE语句。如果是ROLLUP(A, B, C)的话,首先会对(A、B、C)进行GROUP BY,然后对(A、B)进行GRO...
  • cunxiyuan108
  • cunxiyuan108
  • 2014年03月20日 13:38
  • 736

group by(rollup ,cube ,grouping sets) and grouping

1、创建测试数据 create table TEST (ID    INTEGER,  SORT  CHAR(10),  COLOR CHAR(10),  NUM   INTEGER); ...
  • duqiangatom
  • duqiangatom
  • 2012年12月17日 19:43
  • 111
内容举报
返回顶部
收藏助手
不良信息举报
您举报文章:Oracle的group by聚合函数扩展cube rollup和grouping sets
举报原因:
原因补充:

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