Oracle的groupby聚合函数扩展cuberollup和groupingsets

聚合函数是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','片区