Oracle笔记之 Oracle高级分组函数

2023-02-14 20:32:12

高级分组函数

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

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操作
例如：

select deptno,
       job,
       sum(nvl(sal, 0)),
       sum(nvl(comm, 0)),
       sum(nvl(sal, 0) + nvl(comm, 0))
  from emp
 group by rollup(deptno, job)
# 结果
1	10	CLERK	1300	0	1300
2	10	MANAGER	2450	0	2450
3	10	PRESIDENT	5000	0	5000
4	10		8750	0	8750
5	20	CLERK	1900	0	1900
6	20	ANALYST	6000	0	6000
7	20	MANAGER	2975	0	2975
8	20		10875	0	10875
9	30	CLERK	950	0	950
10	30	MANAGER	2850	0	2850
11	30	SALESMAN	5600	2200	7800
12	30		9400	2200	11600
13			29025	2200	31225

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

select deptno,
       job,
       sum(nvl(sal, 0)),
       sum(nvl(comm, 0)),
       sum(nvl(sal, 0) + nvl(comm, 0))
  from emp
 group by deptno, rollup(job)
# 结果
1	10	CLERK	1300	0	1300
2	10	MANAGER	2450	0	2450
3	10	PRESIDENT	5000	0	5000
4	10		8750	0	8750
5	20	CLERK	1900	0	1900
6	20	ANALYST	6000	0	6000
7	20	MANAGER	2975	0	2975
8	20		10875	0	10875
9	30	CLERK	950	0	950
10	30	MANAGER	2850	0	2850
11	30	SALESMAN	5600	2200	7800
12	30		9400	2200	11600

⑥，如果只看合计部分，需要将分组列使用（）包括。
例如：

select deptno,
       job,
       sum(nvl(sal, 0)),
       sum(nvl(comm, 0)),
       sum(nvl(sal, 0) + nvl(comm, 0))
  from emp
 group by rollup((deptno, job))
# 结果
1	10	CLERK	1300	0	1300
2	10	MANAGER	2450	0	2450
3	10	PRESIDENT	5000	0	5000
4	20	CLERK	1900	0	1900
5	20	ANALYST	6000	0	6000
6	20	MANAGER	2975	0	2975
7	30	CLERK	950	0	950
8	30	MANAGER	2850	0	2850
9	30	SALESMAN	5600	2200	7800
10			29025	2200	31225

CUBE(交叉列表)

①，CUBE也是对group by运算的一种扩展，它比rollup扩展更加精细，组合类型更多。
②，rollup是按组合的列从右到左递减分组，CUBE则是对所有可能的组合情况进行分组。
cube是各种可能情况的组合,只不过统计的结果顺序不同而已，rollup按组合的列从右到左递减分组，列的顺序不同，则结果不同。
注意：要想结果符合浏览习惯最好在cube扩展后增加order by子句。
③，cube的分组的情况更多，覆盖所有的可能分组，并计算所有可能的分组的小计。
④，对于CUBE来说，只要列的名字一样，无所谓顺序，结果都是一样的。

例如：

select deptno,
       job,
       sum(nvl(sal, 0)),
       sum(nvl(comm, 0)),
       sum(nvl(sal, 0) + nvl(comm, 0))
  from emp
 group by cube(deptno, job)
 order by 1, 2 nulls last
# 结果
1	10	CLERK	1300	0	1300
2	10	MANAGER	2450	0	2450
3	10	PRESIDENT	5000	0	5000
4	10		8750	0	8750
5	20	ANALYST	6000	0	6000
6	20	CLERK	1900	0	1900
7	20	MANAGER	2975	0	2975
8	20		10875	0	10875
9	30	CLERK	950	0	950
10	30	MANAGER	2850	0	2850
11	30	SALESMAN	5600	2200	7800
12	30		9400	2200	11600
13		ANALYST	6000	0	6000
14		CLERK	4150	0	4150
15		MANAGER	8275	0	8275
16		PRESIDENT	5000	0	5000
17		SALESMAN	5600	2200	7800
18			29025	2200	31225

⑤，cube的语法和rollup一样，部分cube和只显示合计部分请参照rollup扩展。

grouping函数

用rollup和cube函数都会对结果集产生null，这时候可用grouping函数来确认该记录是由哪个字段得出来的。
grouping函数带一个参数，必须为group by中出现的某一列，参数为字段名，结果是根据该字段得出来的就返回1，反之返回0。
例如：

select decode(grouping(deptno), 1, 'ALL DEPT', deptno),
       decode(grouping(job), 1, 'ALL JOB', job),
       sum(nvl(sal, 0)),
       sum(nvl(comm, 0)),
       sum(nvl(sal, 0) + nvl(comm, 0))
  from emp
 group by rollup(deptno, job)
# 结果
1	10	CLERK	1300	0	1300
2	10	MANAGER	2450	0	2450
3	10	PRESIDENT	5000	0	5000
4	10	ALL JOB	8750	0	8750
5	20	CLERK	1900	0	1900
6	20	ANALYST	6000	0	6000
7	20	MANAGER	2975	0	2975
8	20	ALL JOB	10875	0	10875
9	30	CLERK	950	0	950
10	30	MANAGER	2850	0	2850
11	30	SALESMAN	5600	2200	7800
12	30	ALL JOB	9400	2200	11600
13	ALL DEPT	ALL JOB	29025	2200	31225

grouping_id函数

grouping_id函数的参数可以是多个，但必须为group by中出现的列。
Grouping_id()的返回值其实就是参数中的每列的grouping()值的二进制向量，例如如果grouping(A)=1，grouping(B)=1，则grouping_id(A,B)的返回值就是二进制的11，转成11进制就是3。
例如：

select decode(grouping_id(deptno, job), 1, 'Subtotal', 3, 'Total', deptno),
       job,
       sum(nvl(sal, 0)),
       sum(nvl(comm, 0)),
       sum(nvl(sal, 0) + nvl(comm, 0))
  from emp
 group by rollup(deptno, job)
# 结果
1	10	CLERK	1300	0	1300
2	10	MANAGER	2450	0	2450
3	10	PRESIDENT	5000	0	5000
4	Subtotal		8750	0	8750
5	20	CLERK	1900	0	1900
6	20	ANALYST	6000	0	6000
7	20	MANAGER	2975	0	2975
8	Subtotal		10875	0	10875
9	30	CLERK	950	0	950
10	30	MANAGER	2850	0	2850
11	30	SALESMAN	5600	2200	7800
12	Subtotal		9400	2200	11600
13	Total		29025	2200	31225

group_id函数

group_id()函数，无参数，group by对某些列的集合会进行重复的grouping，而实际上绝大多数情况下对结果集中的这些重复grouping是不需要的，就必须有办法剔出这些重复grouping行。
当结果集中有n条重复grouping而形成的行时，每行的group_id()分别是0,1,…,n,这样我们在条件中加入一个group_id()<1就可以剔出这些重复grouping的行了。
例如：

select group_id(),
       decode(grouping_id(deptno, job), 1, 'Subtotal', 3, 'Total', deptno),
       job,
       sum(nvl(sal, 0)),
       sum(nvl(comm, 0)),
       sum(nvl(sal, 0) + nvl(comm, 0))
  from emp
 group by deptno, rollup(deptno, job)
# 结果
1	0	10	CLERK	1300	0	1300
2	0	10	MANAGER	2450	0	2450
3	0	10	PRESIDENT	5000	0	5000
4	0	20	CLERK	1900	0	1900
5	0	20	ANALYST	6000	0	6000
6	0	20	MANAGER	2975	0	2975
7	0	30	CLERK	950	0	950
8	0	30	MANAGER	2850	0	2850
9	0	30	SALESMAN	5600	2200	7800
10	0	Subtotal		8750	0	8750
11	0	Subtotal		10875	0	10875
12	0	Subtotal		9400	2200	11600
13	1	Subtotal		8750	0	8750
14	1	Subtotal		10875	0	10875
15	1	Subtotal		9400	2200	11600

使用 group_id()<1过滤后：

select group_id(),
       decode(grouping_id(deptno, job), 1, 'Subtotal', 3, 'Total', deptno),
       job,
       sum(nvl(sal, 0)),
       sum(nvl(comm, 0)),
       sum(nvl(sal, 0) + nvl(comm, 0))
  from emp
 group by deptno, rollup(deptno, job)
having group_id() < 1
# 结果
1	0	10	CLERK	1300	0	1300
2	0	10	MANAGER	2450	0	2450
3	0	10	PRESIDENT	5000	0	5000
4	0	20	CLERK	1900	0	1900
5	0	20	ANALYST	6000	0	6000
6	0	20	MANAGER	2975	0	2975
7	0	30	CLERK	950	0	950
8	0	30	MANAGER	2850	0	2850
9	0	30	SALESMAN	5600	2200	7800
10	0	Subtotal		8750	0	8750
11	0	Subtotal		10875	0	10875
12	0	Subtotal		9400	2200	11600

码农公寓