Oracle 高级分组函数
valten Lv4
 2020-09-25 17:58:43    

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

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

创建测试环境

在线数据库 http://sqlfiddle.com/

1. 创建表

1
2
3
4
5
6
7
8
9
10
create table EMP2
(
  ID       NUMBER,  /**员工编号*/
  NAME     VARCHAR2(20), /**姓名*/
  SEX     VARCHAR2(3),  /**性别*/
  HIREDATE DATE,         /**入职日期*/
  BASE    VARCHAR2(20), /**工作母地*/
  DEPT    VARCHAR2(20), /**所在部门*/
  SAL     NUMBER        /**月工资*/
);

2. 插入测试数据

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
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. 查看一下刚才插入的数据

1
select * from emp2;

结果如下：

1
2
3
4
5
6
7
8
9
10
|  ID | NAME | SEX |             HIREDATE | BASE | DEPT |   SAL |
|-----|------|-----|----------------------|------|------|-------|
| 107 |   小月 |   女 | 2013-09-01T00:00:00Z |   北京 |   营运 |  9000 |
| 108 |   小美 |   女 | 2011-06-01T00:00:00Z |   上海 |   营运 | 11000 |
| 101 |   张三 |   男 | 2011-01-01T00:00:00Z |   北京 |   财务 |  8000 |
| 102 |   李四 |   男 | 2012-01-01T00:00:00Z |   北京 |   营运 | 15000 |
| 103 |   王五 |   男 | 2013-01-01T00:00:00Z |   上海 |   营运 |  6000 |
| 104 |   赵六 |   男 | 2014-01-01T00:00:00Z |   上海 |   财务 | 10000 |
| 105 |   小花 |   女 | 2014-08-01T00:00:00Z |   上海 |   财务 |  4000 |
| 106 |   小静 |   女 | 2015-01-01T00:00:00Z |   北京 |   财务 |  6000 |

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

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

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

结果如下：

1
2
3
4
5
6
| BASE | DEPT | SUM(SAL) |
|------|------|----------|
|   北京 |   营运 |    24000 |
|   上海 |   营运 |    17000 |
|   北京 |   财务 |    14000 |
|   上海 |   财务 |    14000 |

ROLLUP(累计累加)

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

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

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

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

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

结果如下：

1
2
3
4
5
6
7
8
9
|   BASE |   DEPT | SUM(SAL) |
|--------|--------|----------|
|     上海 |     营运 |    17000 |
|     上海 |     财务 |    14000 |
|     上海 | (null) |    31000 |
|     北京 |     营运 |    24000 |
|     北京 |     财务 |    14000 |
|     北京 | (null) |    38000 |
| (null) | (null) |    69000 |

结果相当于

1
2
3
4
5
6
7
8
9
10
11
12
select base, dept, sum(sal)
  from emp2
 group by base, dept
union all
select base, null, sum(sal)
  from emp2
 group by base, null 
union all 
select null, null, sum(sal)
  from emp2
 group by null, null
 order by 1, 2;

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

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

结果如下：

1
2
3
4
5
6
7
8
9
|   BASE |   DEPT | SUM(SAL) |
|--------|--------|----------|
|     上海 |     营运 |    17000 |
|     北京 |     营运 |    24000 |
| (null) |     营运 |    41000 |
|     上海 |     财务 |    14000 |
|     北京 |     财务 |    14000 |
| (null) |     财务 |    28000 |
| (null) | (null) |    69000 |

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

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

结果如下：

1
2
3
4
5
6
7
8
|   BASE | DEPT | SUM(SAL) |
|--------|------|----------|
|     上海 |   营运 |    17000 |
|     北京 |   营运 |    24000 |
| (null) |   营运 |    41000 |
|     上海 |   财务 |    14000 |
|     北京 |   财务 |    14000 |
| (null) |   财务 |    28000 |

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

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

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

结果如下：

1
2
3
4
5
6
7
|   BASE |   DEPT | SUM(SAL) |
|--------|--------|----------|
|     上海 |     营运 |    17000 |
|     上海 |     财务 |    14000 |
|     北京 |     营运 |    24000 |
|     北京 |     财务 |    14000 |
| (null) | (null) |    69000 |

CUBE(交叉列表)

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

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

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

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

结果如下：

1
2
3
4
5
6
7
8
9
10
11
|   BASE |   DEPT | SUM(SAL) |
|--------|--------|----------|
|     上海 |     营运 |    17000 |
|     上海 |     财务 |    14000 |
|     上海 | (null) |    31000 |
|     北京 |     营运 |    24000 |
|     北京 |     财务 |    14000 |
|     北京 | (null) |    38000 |
| (null) |     营运 |    41000 |
| (null) |     财务 |    28000 |
| (null) | (null) |    69000 |

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

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

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

结果如下：

1
2
3
4
5
6
7
8
|   BASE | DEPT | SUM(SAL) |
|--------|------|----------|
|     上海 |   营运 |    17000 |
|     上海 |   财务 |    14000 |
|     北京 |   营运 |    24000 |
|     北京 |   财务 |    14000 |
| (null) |   营运 |    41000 |
| (null) |   财务 |    28000 |

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中。

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

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

结果如下：

1
2
3
4
5
6
|   BASE |   DEPT | SUM(SAL) |
|--------|--------|----------|
|     上海 | (null) |    31000 |
|     北京 | (null) |    38000 |
| (null) |     营运 |    41000 |
| (null) |     财务 |    28000 |

等价于

1
2
3
4
5
6
7
select base, null, sum(sal)
  from emp2
 group by base, null 
union all 
select null, dept, sum(sal)
  from emp2
 group by null, dept;

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

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

结果如下：

1
2
3
4
5
6
7
8
9
|   BASE |   DEPT | SUM(SAL) |
|--------|--------|----------|
|     上海 |     营运 |    17000 |
|     北京 |     营运 |    24000 |
| (null) |     营运 |    41000 |
|     上海 |     财务 |    14000 |
|     北京 |     财务 |    14000 |
| (null) |     财务 |    28000 |
| (null) | (null) |    69000 |

GROUPING

在以上例子中，是用rollup和cube函数都会对结果集产生null，这时候可用grouping函数来确认该记录是由哪个字段得出来的grouping函数用法，带一个参数，参数为字段名，结果是根据该字段得出来的就返回1，反之返回0

例如：

1
2
3
4
5
select decode(grouping(base), 1, '所有地区', base) base,
       decode(grouping(dept), 1, '所有部门', dept) dept,
       sum(sal)
  from emp2
 group by rollup(dept, base);

结果如下：

1
2
3
4
5
6
7
8
9
| BASE | DEPT | SUM(SAL) |
|------|------|----------|
|   上海 |   营运 |    17000 |
|   北京 |   营运 |    24000 |
| 所有地区 |   营运 |    41000 |
|   上海 |   财务 |    14000 |
|   北京 |   财务 |    14000 |
| 所有地区 |   财务 |    28000 |
| 所有地区 | 所有部门 |    69000 |

参考：

https://blog.csdn.net/u014558001/article/details/42387929#

  • 本文标题：Oracle 高级分组函数
  • 本文作者：valten
  • 创建时间：2020-09-25 17:58:43
  • 本文链接：https://valtenhyl.github.io/数据库/Oracle/oracle-highlevel-group-func/
  • 版权声明：本博客所有文章除特别声明外，均采用 BY-NC-SA 许可协议。转载请注明出处！
 评论