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