Oracle笔记 之 Oracle高级分组函数

高级分组函数

主要讲解 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

上一篇:剑指offer 连续子数组的最大和


下一篇:二叉树中和为某一值的路径