珠海专业网站建设/微信群二维码推广平台
系列文章目录
【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做小计
- 二、既然有了自动合计，那怎么判断出来哪些行是做的自动合计？
- 三、计算所有表达式组合的小计
- 总结
前言
本篇文章讲解的主要内容是：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 80030 ALLEN SALESMAN 160030 WARD SALESMAN 125020 JONES MANAGER 297530 MARTIN SALESMAN 125030 BLAKE MANAGER 285010 CLARK MANAGER 245020 SCOTT ANALYST 300010 KING PRESIDENT 500030 TURNER SALESMAN 150020 ADAMS CLERK 110030 JAMES CLERK 95020 FORD ANALYST 300010 MILLER CLERK 130010 CLERK 130010 MANAGER 245010 PRESIDENT 500020 CLERK 190020 ANALYST 600020 MANAGER 2975DEPTNO ENAME JOB SAL
---------- ---------- --------- ----------30 CLERK 95030 MANAGER 285030 SALESMAN 560010 875020 1087530 94002902527 rows selected
那么问题来了，上面的写法你不感觉挺麻烦的吗，起码你要写的代码量挺多，思考一个问题：必须要用union all才能做吗？
答案是否定的,我们用ROLLUP就可以达到这个目的。
SQL> set pagesize 200;
SQL>
SQL> select deptno,ename,job,sum(sal)as sal2 from emp3 group by rollup(deptno,job,ename)4 order by deptno,job,ename;DEPTNO ENAME JOB SAL
------ ---------- --------- ----------10 MILLER CLERK 130010 CLERK 130010 CLARK MANAGER 245010 MANAGER 245010 KING PRESIDENT 500010 PRESIDENT 500010 875020 FORD ANALYST 300020 SCOTT ANALYST 300020 ANALYST 600020 ADAMS CLERK 110020 SMITH CLERK 80020 CLERK 190020 JONES MANAGER 297520 MANAGER 297520 1087530 JAMES CLERK 95030 CLERK 95030 BLAKE MANAGER 285030 MANAGER 285030 ALLEN SALESMAN 160030 MARTIN SALESMAN 125030 TURNER SALESMAN 150030 WARD SALESMAN 125030 SALESMAN 560030 9400test 2902530 rows selected
上述语句中,ROLLUP是GROUP BY子句的一种扩展,可以为每个分组返回小计记录,以及为所有的分组返回总计记录。
可能这种方式有很多人已用过,如果按部门编号和工作两列汇总,加上总合计有没有办法处理呢？
我们可以把部门与工作这两列放入括号中,这样部门与工作会被当作一个整体：
SQL> SELECT deptno AS 部门编码,job 工作,2 SUM(sal) AS 工资小计3 FROM emp4 group by rollup((deptno,job));部门编码 工作 工资小计
---- --------- ----------10 CLERK 130010 MANAGER 245010 PRESIDENT 500020 CLERK 190020 ANALYST 600020 MANAGER 297530 CLERK 95030 MANAGER 285030 SALESMAN 56002902511 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 207499 ALLEN SALESMAN 7698 1981-2-20 1600.00 300.00 307521 WARD SALESMAN 7698 1981-2-22 1250.00 500.00 307566 JONES MANAGER 7839 1981-4-2 2975.00 207654 MARTIN SALESMAN 7698 1981-9-28 1250.00 1400.00 7698 BLAKE MANAGER 7839 1981-5-1 2850.00 307782 CLARK MANAGER 7839 1981-6-9 2450.00 107788 SCOTT 7566 1987-4-19 3000.00 207839 KING PRESIDENT 1981-11-17 5000.00 107844 TURNER SALESMAN 7698 1981-9-8 1500.00 0.00 307876 ADAMS CLERK 7788 1987-5-23 1100.00 207900 JAMES CLERK 7698 1981-12-3 950.00 307902 FORD ANALYST 7566 1981-12-3 3000.00 7934 MILLER CLERK 7782 1982-1-23 1300.00 1014 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_grouping11 from empp aa12 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 128 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 then3 '总计'4 else5 to_Char(aa.deptno)6 end as 部门编码,7 case8 when grouping(deptno) = 0 and grouping(job) = 1 then9 '根据部门汇总'10 else11 job12 end as 工作,13 case14 when grouping(mgr) = 1 then15 '根据工作汇总'16 else17 to_char(mgr)18 end as 主管,19 max(case20 when empno in (7788, 7654, 7902) then21 empno22 end) as max_empno,23 sum(sal) as sal,24 grouping(deptno) deptno_grouping,25 grouping(job) job_grouping,26 grouping(mgr) mgr_grouping27 from emp aa28 where mgr is not null29 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 121 rows selectedSQL>
三、计算所有表达式组合的小计
现在有个需求：按DEPTNO,JOB的各种组合汇总,并返回总的合计。
可能很多人都用过,那就是CUBE语句。
CUBE也是GROUPBY子句的一种扩展,可以返回每一个列组合的小计记录,同时在末尾加上总计记录。
下面介绍一下grouping_id函数,见下列语句中的注释及与GROUPING的对比。
SQL> select case grouping(deptno) || grouping(job)2 when '00' then3 '按照部门与工作分组'4 when '10' then5 '按照工作分组，部门聚合'6 when '01' then7 '按照部门分组，工作聚合'8 when '11' then9 '做了汇总'10 end as grouping,11 /*把GROUPING(deptno)IIGROUPING(job)的结果当作二进制,再转为十进制就是grouping_id(deptno,job)的值*/12 case grouping_id(deptno, job)13 when 0 then14 '按照部门与工作分组'15 when 2 then16 '按照工作分组，部门聚合'17 when 1 then18 '按照部门分组，工作聚合'19 when 3 then20 '做了汇总'21 end as grouping_id,22 deptno,23 job,24 sum(sal) as smsal25 from emp a26 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
做了汇总 做了汇总 2902518 rows selected
总结
上面内容是对rollup做的详细测试~