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

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

 

 

联合使用rollup和cube来解决特殊查询需求

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) ,
                       () )