前言
本篇文章讲解的主要内容是：ROLLUP、UNION ALL是如何分别做分组合计的以及如何通过CUBE 、GROUPING、GROUPING_ID 识别哪些行是做汇总的结果行
【SQL开发实战技巧】这一系列博主当作复习旧知识来进行写作，毕竟SQL开发在数据分析场景非常重要且基础，面试也会经常问SQL开发和调优经验，相信当我写完这一系列文章，也能再有所收获，未来面对SQL面试也能游刃有余~。
一、ROLLUP代替UNION ALL做小计
生成报表数据时通常还要加一个总合计,比如我现在有个需求：想要统计每个部门各个员工的工资以及每个部门每个工作岗位的工资总计以及每个部门的工资总计数以及全公司的工资总计。
如果是你你会怎么做？
是不是考虑用union all了？
比如下面实现方案：
select a.deptno,a.ename,a.job,a.sal--每个部门各个员工的工资
from emp a where deptno is not null
union all
select a.deptno,null ename,a.job,sum(a.sal)--每个部门每个工作岗位的工资总计
from emp a where deptno is not null
group by a.deptno,a.job
union all
select a.deptno,null ename,null job,sum(a.sal)--每个部门的工资总计
from emp a where deptno is not null
group by a.deptno
union all
select null deptno,null ename,null job,sum(a.sal)--全公司的工资总计
from emp a where deptno is not null;
DEPTNO ENAME JOB SAL
---------- ---------- --------- ----------
20 SMITH CLERK 800
30 ALLEN SALESMAN 1600
30 WARD SALESMAN 1250
20 JONES MANAGER 2975
30 MARTIN SALESMAN 1250
30 BLAKE MANAGER 2850
10 CLARK MANAGER 2450
20 SCOTT ANALYST 3000
10 KING PRESIDENT 5000
30 TURNER SALESMAN 1500
20 ADAMS CLERK 1100
30 JAMES CLERK 950
20 FORD ANALYST 3000
10 MILLER CLERK 1300
10 CLERK 1300
10 MANAGER 2450
10 PRESIDENT 5000
20 CLERK 1900
20 ANALYST 6000
20 MANAGER 2975
DEPTNO ENAME JOB SAL
---------- ---------- --------- ----------
30 CLERK 950
30 MANAGER 2850
30 SALESMAN 5600
10 8750
20 10875
30 9400
29025
27 rows selected那么问题来了，上面的写法你不感觉挺麻烦的吗，起码你要写的代码量挺多，思考一个问题：必须要用union all才能做吗？
答案是否定的,我们用ROLLUP就可以达到这个目的。
SQL> set pagesize 200;
SQL>
SQL> select deptno,ename,job,sum(sal)as sal
2 from emp
3 group by rollup(deptno,job,ename)
4 order by deptno,job,ename;
DEPTNO ENAME JOB SAL
------ ---------- --------- ----------
10 MILLER CLERK 1300
10 CLERK 1300
10 CLARK MANAGER 2450
10 MANAGER 2450
10 KING PRESIDENT 5000
10 PRESIDENT 5000
10 8750
20 FORD ANALYST 3000
20 SCOTT ANALYST 3000
20 ANALYST 6000
20 ADAMS CLERK 1100
20 SMITH CLERK 800
20 CLERK 1900
20 JONES MANAGER 2975
20 MANAGER 2975
20 10875
30 JAMES CLERK 950
30 CLERK 950
30 BLAKE MANAGER 2850
30 MANAGER 2850
30 ALLEN SALESMAN 1600
30 MARTIN SALESMAN 1250
30 TURNER SALESMAN 1500
30 WARD SALESMAN 1250
30 SALESMAN 5600
30 9400
test
29025
30 rows selected上述语句中,ROLLUP是GROUP BY子句的一种扩展,可以为每个分组返回小计记录,以及为所有的分组返回总计记录。
可能这种方式有很多人已用过,如果按部门编号和工作两列汇总,加上总合计有没有办法处理呢？
我们可以把部门与工作这两列放入括号中,这样部门与工作会被当作一个整体：
SQL> SELECT deptno AS 部门编码,job 工作,
2 SUM(sal) AS 工资小计
3 FROM emp
4 group by rollup((deptno,job));
部门编码 工作 工资小计
---- --------- ----------
10 CLERK 1300
10 MANAGER 2450
10 PRESIDENT 5000
20 CLERK 1900
20 ANALYST 6000
20 MANAGER 2975
30 CLERK 950
30 MANAGER 2850
30 SALESMAN 5600
29025
11 rows selected二、既然有了自动合计，那怎么判断出来哪些行是做的自动合计？
前面介绍了用ROLLUP来生成级次汇总,那么如何判断哪些行是做了小计的呢？
有些人会说可以用NVL,如NVL(DEPTNO,总计')、NVL(JOB,'小计')下面来看是否可行。
首先来生成一下测试数据：
create table empp as select * from emp where deptno is not null;
update empp set job =null where empno=7788;
update empp set deptno =null where empno in(7654,7902);
commit;
SQL> select * from empp;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
----- ---------- --------- ----- ----------- --------- --------- ------
7369 SMITH CLERK 7902 1980-12-17 800.00 20
7499 ALLEN SALESMAN 7698 1981-2-20 1600.00 300.00 30
7521 WARD SALESMAN 7698 1981-2-22 1250.00 500.00 30
7566 JONES MANAGER 7839 1981-4-2 2975.00 20
7654 MARTIN SALESMAN 7698 1981-9-28 1250.00 1400.00
7698 BLAKE MANAGER 7839 1981-5-1 2850.00 30
7782 CLARK MANAGER 7839 1981-6-9 2450.00 10
7788 SCOTT 7566 1987-4-19 3000.00 20
7839 KING PRESIDENT 1981-11-17 5000.00 10
7844 TURNER SALESMAN 7698 1981-9-8 1500.00 0.00 30
7876 ADAMS CLERK 7788 1987-5-23 1100.00 20
7900 JAMES CLERK 7698 1981-12-3 950.00 30
7902 FORD ANALYST 7566 1981-12-3 3000.00
7934 MILLER CLERK 7782 1982-1-23 1300.00 10
14 rows selected好了测试数据生成好了，可以看到(7788,7654,7902)这几个员工的job,deptno分别被设置成了空值。
我们接下来验证一下用NVL来判断小计是否合理哈！
SQL> select nvl(to_Char(aa.deptno),'总计') as 部门编码,
2 nvl(job,'小计') as 工作,
3 deptno,
4 job,
5 mgr as 主管,
6 max(case when empno in(7788,7654,7902) then empno end) as max_empno,
7 sum(sal) as sal,
8 grouping(deptno) deptno_grouping,
9 grouping(job)job_grouping,
10 grouping(mgr)mgr_grouping
11 from empp aa
12 group by rollup (deptno,job,mgr);
部门编码 工作 DEPTNO JOB 主管 MAX_EMPNO SAL DEPTNO_GROUPING JOB_GROUPING MGR_GROUPING
---------------------------------------- -------------------------------- ------ --------- ----- ---------- ---------- --------------- ------------ ------------
总计 ANALYST ANALYST 7566 7902 3000 0 0 0
总计 ANALYST ANALYST 7902 3000 0 0 1
总计 SALESMAN SALESMAN 7698 7654 1250 0 0 0
总计 SALESMAN SALESMAN 7654 1250 0 0 1
总计 小计 7902 4250 0 1 1
10 CLERK 10 CLERK 7782 1300 0 0 0
10 CLERK 10 CLERK 1300 0 0 1
10 MANAGER 10 MANAGER 7839 2450 0 0 0
10 MANAGER 10 MANAGER 2450 0 0 1
10 PRESIDENT 10 PRESIDENT 5000 0 0 0
10 PRESIDENT 10 PRESIDENT 5000 0 0 1
10 小计 10 8750 0 1 1
20 小计 20 7566 7788 3000 0 0 0
20 小计 20 7788 3000 0 0 1
20 CLERK 20 CLERK 7788 1100 0 0 0
20 CLERK 20 CLERK 7902 800 0 0 0
20 CLERK 20 CLERK 1900 0 0 1
20 MANAGER 20 MANAGER 7839 2975 0 0 0
20 MANAGER 20 MANAGER 2975 0 0 1
20 小计 20 7788 7875 0 1 1
30 CLERK 30 CLERK 7698 950 0 0 0
30 CLERK 30 CLERK 950 0 0 1
30 MANAGER 30 MANAGER 7839 2850 0 0 0
30 MANAGER 30 MANAGER 2850 0 0 1
30 SALESMAN 30 SALESMAN 7698 4350 0 0 0
30 SALESMAN 30 SALESMAN 4350 0 0 1
30 小计 30 8150 0 1 1
总计 小计 7902 29025 1 1 1
28 rows selected看到上面结果，那么当有空值(empno为 7788,7654,7902)时,对应的detpno或job本身就是空值,所以小计结果是错误的。
这时我们就要用GROUPJNG函数,该函数的参数只能是列名,而且只能是group by后显示的列名。
当该列被汇总时,GROUPING的返回值为1,如DEPTNO_GROUPING最后一行。当该列没有被汇总而是显示明细时,GROUPING的返回值为0,
就像DEPTNO_GROUPTNG前的所有行。
于是查询语句可以更改如下：
SQL> select case grouping(deptno)
2 when 1 then
3 '总计'
4 else
5 to_Char(aa.deptno)
6 end as 部门编码,
7 case
8 when grouping(deptno) = 0 and grouping(job) = 1 then
9 '根据部门汇总'
10 else
11 job
12 end as 工作,
13 case
14 when grouping(mgr) = 1 then
15 '根据工作汇总'
16 else
17 to_char(mgr)
18 end as 主管,
19 max(case
20 when empno in (7788, 7654, 7902) then
21 empno
22 end) as max_empno,
23 sum(sal) as sal,
24 grouping(deptno) deptno_grouping,
25 grouping(job) job_grouping,
26 grouping(mgr) mgr_grouping
27 from emp aa
28 where mgr is not null
29 group by rollup(deptno, job, mgr)
30 order by deptno, job, mgr;
部门编码 工作 主管 MAX_EMPNO SAL DEPTNO_GROUPING JOB_GROUPING MGR_GROUPING
---------------------------------------- -------------------------------- ---------------------------------------- ---------- ---------- --------------- ------------ ------------
10 CLERK 7782 1300 0 0 0
10 CLERK 根据工作汇总 1300 0 0 1
10 MANAGER 7839 2450 0 0 0
10 MANAGER 根据工作汇总 2450 0 0 1
10 根据部门汇总 根据工作汇总 3750 0 1 1
20 ANALYST 7566 7902 6000 0 0 0
20 ANALYST 根据工作汇总 7902 6000 0 0 1
20 CLERK 7788 1100 0 0 0
20 CLERK 7902 800 0 0 0
20 CLERK 根据工作汇总 1900 0 0 1
20 MANAGER 7839 2975 0 0 0
20 MANAGER 根据工作汇总 2975 0 0 1
20 根据部门汇总 根据工作汇总 7902 10875 0 1 1
30 CLERK 7698 950 0 0 0
30 CLERK 根据工作汇总 950 0 0 1
30 MANAGER 7839 2850 0 0 0
30 MANAGER 根据工作汇总 2850 0 0 1
30 SALESMAN 7698 7654 5600 0 0 0
30 SALESMAN 根据工作汇总 7654 5600 0 0 1
30 根据部门汇总 根据工作汇总 7654 9400 0 1 1
总计 根据工作汇总 7902 24025 1 1 1
21 rows selected
SQL> 三、计算所有表达式组合的小计
现在有个需求：按DEPTNO,JOB的各种组合汇总,并返回总的合计。
可能很多人都用过,那就是CUBE语句。
CUBE也是GROUPBY子句的一种扩展,可以返回每一个列组合的小计记录,同时在末尾加上总计记录。
下面介绍一下grouping_id函数,见下列语句中的注释及与GROUPING的对比。
SQL> select case grouping(deptno) || grouping(job)
2 when '00' then
3 '按照部门与工作分组'
4 when '10' then
5 '按照工作分组，部门聚合'
6 when '01' then
7 '按照部门分组，工作聚合'
8 when '11' then
9 '做了汇总'
10 end as grouping,
11 /*把GROUPING(deptno)IIGROUPING(job)的结果当作二进制,再转为十进制就是grouping_id(deptno,job)的值*/
12 case grouping_id(deptno, job)
13 when 0 then
14 '按照部门与工作分组'
15 when 2 then
16 '按照工作分组，部门聚合'
17 when 1 then
18 '按照部门分组，工作聚合'
19 when 3 then
20 '做了汇总'
21 end as grouping_id,
22 deptno,
23 job,
24 sum(sal) as smsal
25 from emp a
26 group by cube(deptno, job)
27 order by grouping(job), grouping(deptno);
GROUPING GROUPING_ID DEPTNO JOB SMSAL
---------------------- ---------------------- ------ --------- ----------
按照部门与工作分组 按照部门与工作分组 10 MANAGER 2450
按照部门与工作分组 按照部门与工作分组 30 MANAGER 2850
按照部门与工作分组 按照部门与工作分组 30 CLERK 950
按照部门与工作分组 按照部门与工作分组 20 MANAGER 2975
按照部门与工作分组 按照部门与工作分组 20 ANALYST 6000
按照部门与工作分组 按照部门与工作分组 20 CLERK 1900
按照部门与工作分组 按照部门与工作分组 10 PRESIDENT 5000
按照部门与工作分组 按照部门与工作分组 30 SALESMAN 5600
按照部门与工作分组 按照部门与工作分组 10 CLERK 1300
按照工作分组，部门聚合 按照工作分组，部门聚合 SALESMAN 5600
按照工作分组，部门聚合 按照工作分组，部门聚合 CLERK 4150
按照工作分组，部门聚合 按照工作分组，部门聚合 ANALYST 6000
按照工作分组，部门聚合 按照工作分组，部门聚合 MANAGER 8275
按照工作分组，部门聚合 按照工作分组，部门聚合 PRESIDENT 5000
按照部门分组，工作聚合 按照部门分组，工作聚合 10 8750
按照部门分组，工作聚合 按照部门分组，工作聚合 30 9400
按照部门分组，工作聚合 按照部门分组，工作聚合 20 10875
做了汇总 做了汇总 29025
18 rows selected总结
上面内容是对rollup做的详细测试~
