[转]详解Oracle高级分组函数(ROLLUP, CUBE, GROUPING SETS)

原文地址:http://blog.csdn.net/u014558001/article/details/42387929

本文主要讲解 ROLLUP, CUBE, GROUPING SETS的主要用法，这些函数可以理解为GroupBy分组函数封装后的精简用法，相当于多个union all 的组合显示效果，但是要比多个union all的效率要高。

其实这些函数在时间的程序开发中应用的并不多，至少在我工作的多年时间中没用过几次，因为现在的各种开发工具/平台都自带了这些高级分组统计功能，使用的方便性及美观性都比这些要好。但如果临时查下数据，用这些函数还是不错的。

创建测试环境

1. 创建表

[sql] view plain copy

createtable EMP2
(
ID NUMBER, — 员工编号
NAME VARCHAR2(20), –姓名
SEX VARCHAR2(2), –性别
HIREDATE DATE, –入职日期
BASE VARCHAR2(20), –工作母地
DEPT VARCHAR2(20), –所在部门
SAL NUMBER –月工资
);

2. 插入测试数据

[sql] view plain copy

insert into emp2 (ID, NAME, SEX, HIREDATE,BASE, DEPT, SAL)
values (107, ‘小月’, ‘女’, to_date(’01-09-2013′, ‘dd-mm-yyyy’), ‘北京’,‘营运’, 9000);
insert into emp2 (ID, NAME, SEX, HIREDATE,BASE, DEPT, SAL)
values (108, ‘小美’, ‘女’, to_date(’01-06-2011′, ‘dd-mm-yyyy’), ‘上海’,‘营运’, 11000);
insert into emp2 (ID, NAME, SEX, HIREDATE,BASE, DEPT, SAL)
values (101, ‘张三’, ‘男’, to_date(’01-01-2011′, ‘dd-mm-yyyy’), ‘北京’,‘财务’, 8000);
insert into emp2 (ID, NAME, SEX, HIREDATE,BASE, DEPT, SAL)
values (102, ‘李四’, ‘男’, to_date(’01-01-2012′, ‘dd-mm-yyyy’), ‘北京’,‘营运’, 15000);
insert into emp2 (ID, NAME, SEX, HIREDATE,BASE, DEPT, SAL)
values (103, ‘王五’, ‘男’, to_date(’01-01-2013′, ‘dd-mm-yyyy’), ‘上海’,‘营运’, 6000);
insert into emp2 (ID, NAME, SEX, HIREDATE,BASE, DEPT, SAL)
values (104, ‘赵六’, ‘男’, to_date(’01-01-2014′, ‘dd-mm-yyyy’), ‘上海’,‘财务’, 10000);
insert into emp2 (ID, NAME, SEX, HIREDATE,BASE, DEPT, SAL)
values (105, ‘小花’, ‘女’, to_date(’01-08-2014′, ‘dd-mm-yyyy’), ‘上海’,‘财务’, 4000);
insert into emp2 (ID, NAME, SEX, HIREDATE,BASE, DEPT, SAL)
values (106, ‘小静’, ‘女’, to_date(’01-01-2015′, ‘dd-mm-yyyy’), ‘北京’,‘财务’, 6000);
commit;

3. 查看一下刚才插入的数据

[sql] view plain copy

select * from emp2;

4. 先看下普通分组的效果

按照地区统计每个部门的总工资

[sql] view plain copy

select base,dept ,sum(sal) from emp2
group by base,dept;

查看结果如下：

ROLLUP（累计累加）

ROLLUP是对group by的扩展，因此，它只能出现在group by子句中，依赖于分组的列，对每个分组会生成汇总数据, rollup和group by联合一起使用，达到了按group by列顺序分组，并且实现小计和合计的功能。rollup分组还是有序的，先全部分组，然后对每个分组小计，最后合计。

rollup中列的顺序不同，则统计的结果不同。因为它是按列从右递减分组的。

比如 Group by ROLLUP(A, B, C)，首先会对(A、B、C)进行GROUP BY，然后对(A、B)进行GROUP BY，然后是(A)进行GROUP BY，最后对全表进行GROUP BY操作

按照地区统计每个部门的总工资，按工作母地汇总，再合计

[sql] view plain copy

select base,dept,sum(sal) from emp2
groupbyrollup(base,dept);

结果相当于

[sql] view plain copy

select base,dept,sum(sal) from emp2
group by base,dept
unionall
select base,null,sum(sal) from emp2
group by base,null
unionall
selectnull,null,sum(sal) from emp2
group by null,null
order by 1,2

如果颠倒下rollup顺序则结果如下：

[sql] view plain copy

select base,dept,sum(sal) from emp2
group by rollup(dept,base);

如果在实际查询中，有的小计或合计我们不需要，那么就要使用局部rollup,局部rollup就是将需要固定统计的列放在group by中，而不是放在rollup中。

[sql] view plain copy

select base,dept,sum(sal) from emp2
group by dept,rollup(base);

与group by rollup(dept,base)相比：去掉了最后一行的汇总，因为每次汇总要么是dept,base，要么是dept,null ，dept是固定的。

如果只希望看到合计则可以这样写：

[sql] view plain copy

select base,dept ,sum(sal) from emp2
group by rollup((base,dept));

CUBE(交叉列表)

CUBE也是对group by运算的一种扩展，它比rollup扩展更加精细，组合类型更多，rollup是按组合的列从右到左递减分组计算，而CUBE则是对所有可能的组合情况进行分组，这样分组的情况更多，覆盖所有的可能分组，并计算所有可能的分组的小计。

对于CUBE来说，列的名字只要一样，那么顺序无所谓，结果都是一样的，因为cube是各种可能情况的组合,只不过统计的结果顺序不同而已。但是对于rollup来说，列的顺序不同，则结果不同。

比如对工作母地和部门的交叉统计

[sql] view plain copy

select base,dept,sum(sal) from emp2
group by cube(base,dept)
order by 1,2;

部分CUBE和部分ROLLUP类似，把需要固定统计的列放到group by中，不放到cube中就可以了。

如果cube中只有一个列，那么和rollup的结果一致

[sql] view plain copy

select base,dept,sum(sal) from emp2
group by dept,cube(base)
order by1,2;

rollup和cube区别：

如果是ROLLUP(A,B, C)的话，GROUP BY顺序

(A、B、C)

(A、B)

(A)

最后对全表进行GROUPBY操作。

如果是GROUP BY CUBE(A, B, C)，GROUP BY顺序
(A、B、C)

(A、B)

(A、C)

(A)，

(B、C)

(B)

(C)，

最后对全表进行GROUPBY操作。

GROUPING SETS

对group by的另一个扩展，专门对分组列分别进行小计计算，不包括合计。使用方式和rollup和cube一样，都是放在group by中。

比如需要分别统计工作母地与部门的合计：

[sql] view plain copy

select base,dept,sum(sal) from emp2
group by grouping sets(base,dept);

结果为：

等价于

[sql] view plain copy

select base,null,sum(sal) from emp2
group by base,null
unionall
select null,dept,sum(sal) from emp2
group by null,dept;

理解了groupingsets的原理我们用他实现rollup的功能也是可以的：

[sql] view plain copy

select base,dept,sum(sal) from emp2
group by grouping sets ((base,dept),dept,null);

效果如下：

grouping函数

在以上例子中，是用rollup和cube函数都会对结果集产生null，这时候可用grouping函数来确认该记录是由哪个字段得出来的

grouping函数用法，带一个参数，参数为字段名，结果是根据该字段得出来的就返回1，反之返回0

例如：

[sql] view plain copy

select decode(grouping(base),1,‘所有地区’,base) base,
decode(grouping(dept),1,‘所有部门’,dept)dept ,sum(sal) from emp2
group by rollup(dept,base);

posted on 2019-02-18 10:45 xc川阅读(…) 评论(…) 编辑收藏

刷新评论刷新页面返回顶部

本文链接：https://www.cnblogs.com/sycxc/p/10394290.html

[转]详解Oracle高级分组函数(ROLLUP, CUBE, GROUPING SETS)