Oracle group by 扩展函数详解（grouping sets、rollup、cube）_oracle group by grouping sets

1 概述

1. 一般求和函数：聚合函数 | 列 + group by 列
   (1) 聚合函数: count(1)、sum()、avg()、max()、min() 

2. 高级分组函数：相当于多次 group by + union all，但效率更高 -- 若有 N 列
   (1) grouping sets：单独分组，group by N 次
   (2) rollup		：累计累加，group by N + 1 次，有默认排序
   (3) cube			：交叉列表，group by 2^N 次，无默认排序，rollup 的扩展

3. 扩展列
   (1) grouping     : 判断列值是否为空（0：非空，1：空）
   (2) grouping_id  ：非空列合计，grouping 的扩展（二进制，注意顺序）
   (3) group_id()   ：标识重复组（0：第一次分组，1：重复分区，无入参）
1
2
3
4
5
6
7
8
9
10
11
12

2 分组函数

基础数据准备：

-- 人员信息表
create table person_info (
   person_no     number(5),
   person_name   varchar2(30),
   sex           varchar2(3),
   money         number(8),
   work_location varchar2(10)
);

-- 测试数据
insert into person_info (person_no, person_name, sex, money, work_location)
values (1, '瑶瑶', '女', 100000, '深圳');

insert into person_info (person_no, person_name, sex, money, work_location)
values (2, '倩倩', '女', 200000, '深圳');

insert into person_info (person_no, person_name, sex, money, work_location)
values (3, '优优', '男', 300000, '深圳');

insert into person_info (person_no, person_name, sex, money, work_location)
values (4, '丽丽', '女', 200000, '武汉');

insert into person_info (person_no, person_name, sex, money, work_location)
values (5, '萌萌', '女', 100000, '武汉');
commit;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25

2.1 group by：一起分组（1）

1. group by：最基础的分组函数

2. 注意：不能对 'clob' 列分组 
1
2
3

例1：查询各地区的人数

select pi.work_location 地区, 
       count(1) 人数
  from person_info pi
 group by pi.work_location;
1
2
3
4

查询结果：

地区		人数
深圳		3
武汉		2
1
2
3

例2：不能对 clob 列分组（知晓即可，实际开发中，咱也不会这么操作）

-- 不能对 'clob' 类型项目进行 'group by'
with t_person_info as
 (select to_clob(pi.work_location) work_location_clob 
    from person_info pi)
select t.work_location_clob 地区,
       count(1) 人数
  from t_person_info t 
 group by t.work_location_clob;
1
2
3
4
5
6
7
8

查询结果：

2.2 grouping sets：单独分组（N）

1. group by (a, b)
   (1) 对 a, b '一起' 进行分组

2. group by grouping sets(a, b)
   (1) 对 a, b '单独' 进行分组
   (2) 等同于：
	   group by a
	   union all
	   group by b
1
2
3
4
5
6
7
8
9

测试截图：

等效 sql ：

select pi.work_location 地区,  
       '' 性别,
       count(1) 人数
  from person_info pi
 group by pi.work_location
 
 union all
select '' 地区,  
       pi.sex 性别,
       count(1) 人数
  from person_info pi
 group by pi.sex;
1
2
3
4
5
6
7
8
9
10
11
12

2.3 rollup：累计累加（N + 1）

1. group by rollup(a, b)
   (1) 首先对 'a, b' 一起 group by
   (2) 然后对 'a' group by
   (3) 最后计算 '聚合函数'

2. 若有 N 列，则 group by N + 1 次（1：group by null, null, 聚合函数）

3. 有默认排序：order by a, b 
1
2
3
4
5
6
7
8

测试截图：

等效 sql：

select * from (
  select a, b, null, count(1) from table_name group by(a, b, null)
  union all
  select a, null, null, count(1) from table_name group by (a, null, null)
  union all
  select null, null, null, count(1) from table_name group by (null, null, null)
) order by a, b
1
2
3
4
5
6
7

2.4 cube：交叉列表（2^N）

1. group by cube(a, b)
   (1) 首先对 'a, b' 一起 group by
   (2) 然后对 'a' group by 
   (3) 然后对 'b' group by -- 比 rollup 多此一项
   (4) 最后计算 '聚合函数'

2. cube 是 rollup 的扩展，组合类型更多
   若有 N 列，则 group by 2^N 次
   
3. 没有默认排序，这点和 rollup 不同
   (1) cube  : 只要列名一样，顺序无所谓，结果是都一样的
   (2) rollup: 列的顺序不同，结果不同
1
2
3
4
5
6
7
8
9
10
11
12

测试截图：

3 扩展列

3.1 grouping：列是否为空

1. 作用：'判断列值是否为空'
   (1) grouping = 0 => '非空'
   (2) grouping = 1 => '空'

2. 常用 grouping set, cube, rollup 一起使用
1
2
3
4
5

测试截图：

3.2 grouping_id：非空列合计

1. grouping_id 是 grouping 的扩展
   (1) grouping   : 区分列是否为空
   (2) grouping_id: 区分 grouping 列 '合计'（注意顺序）

2. 返回的 '二进制数'，显示的是 '十进制数'
   (1) 1 + 1 = 11（2进制） = 3（10进制）
   (2) 1 + 0 = 10（2进制） = 2（10进制）
1
2
3
4
5
6
7

测试截图：

3.3 group_id：标识重复组

1. group_id(): 标识重复组（执行多次）
   (1) 无参数
   (2) 若返回值为 0：第一次分组
       若返回值为 1：重复分组
1
2
3
4

测试截图：