系列文章目录
【SQL开发实战技巧】系列（一）:关于SQL不得不说的那些事
【SQL开发实战技巧】系列（二）：简单单表查询
【SQL开发实战技巧】系列（三）：SQL排序的那些事
【SQL开发实战技巧】系列（四）：从执行计划讨论UNION ALL与空字符串&UNION与OR的使用注意事项
【SQL开发实战技巧】系列（五）：从执行计划看IN、EXISTS 和 INNER JOIN效率，我们要分场景不要死记网上结论
【SQL开发实战技巧】系列（六）：从执行计划看NOT IN、NOT EXISTS 和 LEFT JOIN效率，记住内外关联条件不要乱放
【SQL开发实战技巧】系列（七）：从有重复数据前提下如何比较出两个表中的差异数据及对应条数聊起
【SQL开发实战技巧】系列（八）：聊聊如何插入数据时比约束更灵活的限制数据插入以及怎么一个insert语句同时插入多张表
【SQL开发实战技巧】系列（九）：一个update误把其他列数据更新成空了？Merge改写update！给你五种删除重复数据的写法！
【SQL开发实战技巧】系列（十）：从拆分字符串、替换字符串以及统计字符串出现次数说起
【SQL开发实战技巧】系列（十一）：拿几个案例讲讲translate|regexp_replace|listagg|wmsys.wm_concat|substr|regexp_substr常用函数
【SQL开发实战技巧】系列（十二）：三问(如何对字符串字母去重后按字母顺序排列字符串？如何识别哪些字符串中包含数字？如何将分隔数据转换为多值IN列表?)
【SQL开发实战技巧】系列（十三）：讨论一下常用聚集函数&通过执行计划看sum()over()对员工工资进行累加
【SQL开发实战技巧】系列（十四）：计算消费后的余额&计算银行流水累计和&计算各部门工资排名前三位的员工
【SQL开发实战技巧】系列（十五）：查找最值所在行数据信息及快速计算总和百之max/min() keep() over()、fisrt_value、last_value、ratio_to_report
【SQL开发实战技巧】系列（十六）：数据仓库中时间类型操作（初级）日、月、年、时、分、秒之差及时间间隔计算
【SQL开发实战技巧】系列（十七）：数据仓库中时间类型操作（初级）确定两个日期之间的工作天数、计算—年中周内各日期出现次数、确定当前记录和下一条记录之间相差的天数
【SQL开发实战技巧】系列（十八）：数据仓库中时间类型操作（进阶）INTERVAL、EXTRACT以及如何确定一年是否为闰年及周的计算
【SQL开发实战技巧】系列（十九）：数据仓库中时间类型操作（进阶）如何一个SQL打印当月或一年的日历？如何确定某月内第一个和最后—个周内某天的日期？
【SQL开发实战技巧】系列（二十）：数据仓库中时间类型操作（进阶）获取季度开始结束时间以及如何统计非连续性时间的数据
【SQL开发实战技巧】系列（二十一）：数据仓库中时间类型操作（进阶）识别重叠的日期范围，按指定10分钟时间间隔汇总数据
【SQL开发实战技巧】系列（二十二）：数仓报表场景☞ 从分析函数效率一定快吗聊一聊结果集分页和隔行抽样实现方式
【SQL开发实战技巧】系列（二十三）：数仓报表场景☞ 如何对数据排列组合去重以及通过如何找到包含最大值和最小值的记录这个问题再次用执行计划给你证明分析函数性能不一定高
【SQL开发实战技巧】系列（二十四）：数仓报表场景☞通过案例执行计划详解”行转列”,”列转行”是如何实现的
【SQL开发实战技巧】系列（二十五）：数仓报表场景☞结果集中的重复数据只显示一次以及计算部门薪资差异高效的写法以及如何对数据进行快速分组
【SQL开发实战技巧】系列（二十六）：数仓报表场景☞聊聊ROLLUP、UNION ALL是如何分别做分组合计的以及如何识别哪些行是做汇总的结果行
前言
本篇文章讲解的主要内容是：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的对比。文章来源：https://www.toymoban.com/news/detail-411231.html
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做的详细测试~文章来源地址https://www.toymoban.com/news/detail-411231.html
到了这里，关于【SQL开发实战技巧】系列（二十六）：数仓报表场景☞聊聊ROLLUP、UNION ALL是如何分别做分组合计的以及如何识别哪些行是做汇总的结果行的文章就介绍完了。如果您还想了解更多内容，请在右上角搜索TOY模板网以前的文章或继续浏览下面的相关文章，希望大家以后多多支持TOY模板网！
