oracle提供的分析函数 cube(),rollup(),grouping sets()
1. Oracle的rollup、cube、grouping sets函数 收藏Oracle的group by除了基本用法以外，还有3种扩展用法，分别是rollup、cube、grouping sets。
1 rollup
假设有一个表test，有A、B、C、D、E5列。
如果使用group by rollup(A,B,C)，首先会对(A、B、C)进行GROUP BY，然后对(A、B)进行GROUP BY，然后是(A)进行GROUP BY，最后对全表进行GROUP BY操作。roll up的意思是“卷起”，这也可以帮助我们理解group by rollup就是对选择的列从右到左以一次少一列的方式进行grouping直到所有列都去掉后的grouping(也就是全表grouping)，对于n个参数的rollup，有n+1次的grouping。以下2个sql的结果集是一样的：
Select A,B,C,sum(E) from test group by rollup(A,B,C)
与
Select A,B,C,sum(E) from test group by A,B,C
union all
Select A,B,null,sum(E) from test group by A,B
union all
Select A,null,null,sum(E) from test group by A
union all
Select null,null,null,sum(E) from test
2 cube
cube的意思是立方，对cube的每个参数，都可以理解为取值为参与grouping和不参与grouping两个值的一个维度，然后所有维度取值组合的集合就是grouping的集合，对于n个参数的cube，有2^n次的grouping。如果使用group by cube(A,B,C),，则首先会对(A、B、C)进行GROUP BY，然后依次是(A、B)，(A、C)，(A)，(B、C)，(B)，(C)，最后对全表进行GROUP BY操作，一共是2^3=8次grouping。同rollup一样，也可以用基本的group by加上结果集的union all写出一个与group by cube结果集相同的sql：
Select A,B,C,sum(E) from test group by cube(A,B,C);
与
Select A,B,C,sum(E) from test group by A,B,C
union all
Select A,B,null,sum(E) from test group by A,B
union all
Select A,null,C,sum(E) from test group by A,C
union all
Select A,null,null,sum(E) from test group by A
union all
Select null,B,C,sum(E) from test group by B,C
union all
Select null,B,null,sum(E) from test group by B
union all
Select null,null,C,sum(E) from test group by C
union all
Select null,null,null,sum(E) from test;
3 grouping sets
grouping sets就是对参数中的每个参数做grouping，也就是有几个参数做几次grouping,例如使用group by grouping sets(A,B,C)，则对(A),(B),(C)进行group by，如果使用group by grouping sets((A,B),C),则对(A,B),(C)进行group by。甚至grouping by grouping set(A,A)都是语法允许的，也就是对(A)进行2次group by,grouping sets的参数允许重复
4 总结
rollup (N+1个分组方案)
cube (2^N个分组方案)
grouping sets (自定义罗列出分组方案)
5 注意点
5.1 机制不同
在rollup和cube的说明中分别给出了用基本group by加结果集union all给出了结果集相同的sql，但这只是为了理解的方便而给出的sql，并不说明rollup和cube与基本group by加结果集union all等价。实际上两者的内部机制是安全不一样的，前者除了写法简洁以外，运行时不需多次扫描表，效率远比后者高。
5.2 集合可运算
3种扩展用法的参数可以是源表中的某一个具体的列，也可以是若干列经过计算而形成的一个新列（比如说A+B，A||B），也可以是这两种列的一个集合（例如（A+B，C）），对于grouping set更是特殊，可以是空集合()，表示对全表进行group by。
5.3 group by 与 rollup, cube组合使用
3）Group by的基本用法以及这3种扩展用法可以组合使用，也就是说可以出现group by A,rollup(A,B)这样的用法，oracle将对出现在group by中的每种用法的grouping列集合做笛卡尔积然后对其中的每一个元素做group by。这话说起来挺绕口，举例说明吧，group by A, rollup(A,B)，基本用法的grouping集合是(A),rollup(A,B)的grouping集合是((A,B),(A),()),两个集合的笛卡尔积集合是((A,A,B),(A,A),(A))，所以会首先对(A,A,B)做group by，然后对(A,A)做group by，最后对(A)做group by。实际上对(A,A,B)做group by和对(A,B)做group by两者是完全等价的(group by A,A,B结果和group by A,B完全一样)，同理对(A,A)做group by和对(A)做group by也是等价的。简化后的结果就是首先对(A,B)做group by，然后对(A)做group by，最后再对(A)做group by。下面给出两个等价的sql以便理解：
Select A,B,sum(E) from test1 group by A, rollup(A,B);
与
Select A,B,sum(E) from test1 group by A,B
Union all
Select A,null,sum(E) from test1 group by A
Union all
Select A,null,sum(E) from test1 group by A;
6 grouping()、grouping_id()、group_id()
6.1 grouping()
参数只有一个，而且必须为group by中出现的某一列，表示结果集的一行是否对该列做了grouping。对于对该列做了grouping的行而言，grouping()=0，反之为1；
6.2 grouping_id()
参数可以是多个，但必须为group by中出现的列。Grouping_id()的返回值其实就是参数中的每列的grouping()值的二进制向量，例如如果grouping(A)=1，grouping(B)=0，则grouping_id(A,B)的返回值就是二进制的10，转成10进制就是2。
6.3 group_id()
无参数。见上面的说明3），group by对某些列的集合会进行重复的grouping，而实际上绝大多数情况下对结果集中的这些重复行是不需要的，那就必须有办法剔出这些重复grouping的行。当结果集中有n条重复grouping而形成的行时，每行的group_id()分别是0,1,…,n，这样我们在条件中加入一个group_id()<1就可以剔出这些重复grouping的行了。
7 示例
7.1 建表与数据
SQL> create table test(department_id number, a varchar2(20), b varchar2(20));
Table created
SQL> insert into test values(10, 'A', 'B');
1 row inserted
SQL> commit;
Commit complete
7.2 查询语句
select department_id,
a,
b,
grouping(department_id),
grouping(a),
grouping(b)
from test
group by rollup(department_id, a, b)
order by 4, 5, 6;
select department_id,
a,
b,
grouping(department_id),
grouping(a),
grouping(b)
from test
group by cube(department_id, a, b)
order by 4, 5, 6;
2. cube(),rollup(),grouping sets() 是oracle提供的分析函数,看看下面的例子就知道他们的作用了.
创建测试表 并准备数据
CREATE TABLE test_sales (
location_name VARCHAR2(20),
month_flag number,
sales NUMBER,
manager VARCHAR2(20),
create_stamp DATE);
/
insert into test_sales values('SH',5,1,'Kevin',sysdate);
/
insert into test_sales values('SH',6,9,'Kevin',sysdate);
/
insert into test_sales values('SH',7,9,'Kevin',sysdate);
/
insert into test_sales values('SH',5,1,'JT',sysdate);
/
insert into test_sales values('GZ',6,9,'JT',sysdate);
/
insert into test_sales values('SH',7,8,'JT',sysdate);
/
insert into test_sales values('GZ',5,1,'Miles',sysdate);
/
insert into test_sales values('GZ',6,9,'Miles',sysdate);
/
insert into test_sales values('GZ',7,8,'Miles',sysdate);
/
insert into test_sales values('SH',5,1,'Collion',sysdate);
/
insert into test_sales values('GZ',6,9,'Collion',sysdate);
/
insert into test_sales values('GZ',7,9,'Collion',sysdate);
/
1.查询总销售额，地区销售额和各经理销售情况
solution 1 -- cube()
select manager,location_name,sum(sales) from test_sales
group by cube(manager ,location_name)
结果如下：
MANAGER LOCATION_NAME SUM(SALES)
74 -- 各地区总销售额
GZ 45 -- GZ 地区销售额
SH 29 -- SH 地区销售额
JT 18 -- 经理JT销售额
JT GZ 9
JT SH 9
Kevin 19 -- 经理Kevin总销售额
Kevin SH 19
Miles 18 -- 经理Miles总销售额
Miles GZ 18
Collion 19 -- 经理Collion总销售额
Collion GZ 18
Collion SH 1
solution 2 -- rollup()
select manager,location_name,sum(sales) from test_sales
group by rollup(manager ,location_name)
结果同上，只不过是汇总信息的现实顺序不同：
MANAGER LOCATION_NAME SUM(SALES)
JT GZ 9
JT SH 9
JT 18
Kevin SH 19
Kevin 19
Miles GZ 18
Miles 18
Collion GZ 18
Collion SH 1
Collion 19
74
2. 查询各经理销售情况 即指定分组
solution 1-- grouping sets()
select manager,location_name,sum(sales) from test_sales
group by grouping sets((manager ,location_name),manager)
结果如下:
MANAGER LOCATION_NAME SUM(SALES)
JT GZ 9
JT SH 9
JT 18
Kevin SH 19
Kevin 19
Miles GZ 18
Miles 18
Collion GZ 18
Collion SH 1
Collion 19
自己看看下面script的运行结果
select manager,location_name,sum(sales) from test_sales
group by grouping sets((manager ,location_name),manager
,location_name,())
3. Oracle的group by聚合函数扩展cube rollup和grouping sets
<div class="postBody">
聚合函数是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 的扩展比较简单，但非常高效，对一个查询增加的开销非常少。
考虑如下查询。
<div style="border-right: medium none; padding-right: 0cm; border-top: medium none; padding-left: 0cm; padding-bottom: 1pt; margin-left: 18pt; border-left: medium none; margin-right: 0cm; padding-top: 0cm;">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]