【SQL开发实战技巧】系列（十五）：查找最值所在行数据信息及快速计算总和百之max/min() keep() over()、fisrt_value、last_v

irpas 02-07 4740

系列文章目录

【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是如何分别做分组合计的以及如何识别哪些行是做汇总的结果行

文章目录系列文章目录前言一、计算部门中哪个工资等级的员工最多二、返回最值所在行数据三、通过fisrt_value\last_value实现上面返回最值所在行数据四、求总和的百分比总结

前言

本篇文章讲解的主要内容是：计算部门中那个工资等级的员工最多、通过返回部门10最大工资所在行的员工名称小案例来讲解max/min() keep() over()、通过查询工资最高的人小案例来介绍fisrt_value、last_value、通过计算各个部门的工资合计以及各个部门合计工资占总工资的比例小案例来介绍如何计算百分比及ratio_to_report分析函数的使用【SQL开发实战技巧】这一系列博主当作复习旧知识来进行写作，毕竟SQL开发在数据分析场景非常重要且基础，面试也会经常问SQL开发和调优经验，相信当我写完这一系列文章，也能再有所收获，未来面对SQL面试也能游刃有余~。

一、计算部门中哪个工资等级的员工最多

如题，这个需求怎么实现呢？只需要如下图这么做就可以啦，每一步我都写了简短说明，这里使用的是 dense_rank而不是row_number，否则有失公允。

SQL> with t as 2 ( --求出来每个部门每个薪资的人数 3 select sal, deptno, count(*) as cnt from emp group by sal, deptno), 4 t1 as --根据人数进行排序 5 (select deptno, 6 sal, 7 dense_rank() over(partition by deptno order by cnt desc) as sortrn 8 from t) 9 select * from t1 --取出来排名第一的 10 where sortrn =1; DEPTNO SAL SORTRN ------ --------- ---------- 10 1300.00 1 10 2450.00 1 10 5000.00 1 20 3000.00 1 30 1250.00 1 1 6 rows selected 二、返回最值所在行数据

现在有个需求：返回部门10最大工资所在行的员工名称！这个解决办法比较多了，我这里给大家提供两种：

1、标量

这里给了三种写法，都比较麻烦，那你感觉数据量大的时候哪个快？

SQL> select deptno, 2 empno, 3 (select max(b.ename) from emp b where b.sal = a.max_sal) as 工资最高的人, 4 ename, 5 sal 6 from (select deptno, 7 empno, 8 max(sal) over(partition by deptno) as max_sal, 9 ename, 10 sal 11 from emp a 12 where deptno = 10) a 13 order by 1, 5 desc; DEPTNO EMPNO 工资最高的人 ENAME SAL ------ ----- ---------- ---------- --------- 10 7839 KING KING 5000.00 10 7782 KING CLARK 2450.00 10 7934 KING MILLER 1300.00 SQL> SQL> with t as 2 (select max(b.sal) mx from emp b where deptno = 10) 3 select deptno, empno, ename, sal from emp where sal = (select mx from t); DEPTNO EMPNO ENAME SAL ------ ----- ---------- --------- 10 7839 KING 5000.00 SQL> with t as 2 (select max(b.sal) mx from emp b where deptno = 10) 3 select deptno, empno, ename, sal from emp, t where sal = t.mx 4 ; DEPTNO EMPNO ENAME SAL ------ ----- ---------- --------- 10 7839 KING 5000.00 SQL> 2、分析函数

在Oracle里有分析函数可以直接满足这个需求，而且还可以方便地同时取最大及最小值：

SQL> select deptno, 2 empno, 3 max(ename) keep(dense_rank first order by sal) over(partition by deptno) as 工资最低的人, 4 max(ename) keep(dense_rank last order by sal) over(partition by deptno) as 工资最搞高的人, 5 ename, 6 sal 7 from emp 8 where deptno = 10 9 order by 1, 6 desc; DEPTNO EMPNO 工资最低的人工资最搞高的人 ENAME SAL ------ ----- ---------- ---------- ---------- --------- 10 7839 MILLER KING KING 5000.00 10 7782 MILLER KING CLARK 2450.00 10 7934 MILLER KING MILLER 1300.00

另外，first、last语句也可以放在group里与其他聚合函数一样使用，这时要去掉后面的over(partition by xxx)：

SQL> select deptno, 2 min(sal) as min_sal, 3 max(ename) keep(dense_rank first order by sal) as 工资最低的人, 4 max(sal) as max_sal, 5 max(ename) keep(dense_rank last order by sal) as 工资最搞高的人 6 from emp 7 where deptno = 10 8 group by deptno 9 order by 1 desc; DEPTNO MIN_SAL 工资最低的人 MAX_SAL 工资最搞高的人 ------ ---------- ---------- ---------- ---------- 10 1300 MILLER 5000 KING

或许有人注意到，在first、last语句中，我们不管取工资最低还是最高，都用聚合函数MAX。若要搞清楚这个MAX有什么用，需要换一个部门来看：

select deptno, empno, max(sal) over(partition by deptno) as max_sal, ename, sal from emp where deptno = 20 order by 1, 5 desc;

可以看到，工资最高的有两个人，对于这种数据，first、last语句会出现什么结果呢？

SQL> select deptno, 2 empno, 3 ename, 4 sal, 5 min(ename)keep(dense_rank last order by sal)over(partition by deptno) as 工资最高的人min, 6 max(ename)keep(dense_rank last order by sal)over(partition by deptno) as 工资最高的人max 7 from scott.emp 8 where deptno = 20 9 order by 1, 4 desc; DEPTNO EMPNO ENAME SAL 工资最高的人MIN 工资最高的人MAX ------ ----- ---------- --------- ---------- ---------- 20 7788 SCOTT 3000.00 FORD SCOTT 20 7902 FORD 3000.00 FORD SCOTT 20 7566 JONES 2975.00 FORD SCOTT 20 7876 ADAMS 1100.00 FORD SCOTT

工资最高的3000有两个人：SCOTT、FORD。当最值有重复数据时，keep(...)部分得到的是一个数据集(SCOTT,FORD), 这时前面的聚合函数就会起作用：min()与max()分别得到"FORD"与"SCOTT"。

三、通过fisrt_value\last_value实现上面返回最值所在行数据

下面来看一个示例：

SQL> SELECT deptno, 2 empno, 3 first_value(ename)over(PARTITION BY deptno ORDER BY sal DESC)AS 工资最高的人, 4 ename,sal 5 FROM emp 6 WHERE deptno=10 ORDER BY 1,5 DESC; DEPTNO EMPNO 工资最高的人 ENAME SAL ------ ----- ---------- ---------- --------- 10 7839 KING KING 5000.00 10 7782 KING CLARK 2450.00 10 7934 KING MILLER 1300.00

看上去这个语句没有问题，但若把DESC改写为last_value来看一下：

SQL> SQL> SELECT deptno, 2 empno, 3 last_value(ename)over(PARTITION BY deptno ORDER BY sal)AS 工资最高的人, 4 ename,sal 5 FROM emp 6 WHERE deptno=10 ORDER BY 1,5 DESC; DEPTNO EMPNO 工资最高的人 ENAME SAL ------ ----- ---------- ---------- --------- 10 7839 KING KING 5000.00 10 7782 CLARK CLARK 2450.00 10 7934 MILLER MILLER 1300.00 SQL>

结果不对了哈，下面先直接对比一下语法：

可以看到first_value、last_value的order by在over()中，这实际上与累加模式类似。 first_value取分组（当然，因为这个查询中只有一个部门，也可以不分组）排序后，最前面一行的数据类似下面的语句：

SQL> SELECT deptno, 2 empno, 3 min(sal) over(PARTITION BY deptno ORDER BY sal desc) AS 最高工资, 4 ename, 5 sal 6 FROM emp 7 WHERE deptno = 10 8 ORDER BY 1, 5 DESC; DEPTNO EMPNO 最高工资 ENAME SAL ------ ----- ---------- ---------- --------- 10 7839 5000 KING 5000.00 10 7782 2450 CLARK 2450.00 10 7934 1300 MILLER 1300.00

而last_value取分组排序后，最后面一行的数据类似于下列语句：

SQL> SQL> SELECT deptno, 2 empno, 3 max(sal) over(PARTITION BY deptno ORDER BY sal) AS 最高工资, 4 ename, 5 sal 6 FROM emp 7 WHERE deptno = 10 8 ORDER BY 1, 5; DEPTNO EMPNO 最高工资 ENAME SAL ------ ----- ---------- ---------- --------- 10 7934 1300 MILLER 1300.00 10 7782 2450 CLARK 2450.00 10 7839 5000 KING 5000.00 SQL>

当部门改为20时，要分别取出"FORD"与"SCOTT",用first_value需要更改为：

SQL> SELECT deptno, 2 empno, 3 ename, 4 sal, 5 first_value(ename)over(partition by deptno order by sal desc,ename) as 工资最高的人min, 6 first_value(ename)over(partition by deptno order by sal desc,ename desc) as 工资最高的人max 7 FROM emp 8 WHERE deptno = 20 9 ORDER BY 1, 4 DESC; DEPTNO EMPNO ENAME SAL 工资最高的人MIN 工资最高的人MAX ------ ----- ---------- --------- ---------- ---------- 20 7902 FORD 3000.00 FORD SCOTT 20 7788 SCOTT 3000.00 FORD SCOTT 20 7566 JONES 2975.00 FORD SCOTT 20 7876 ADAMS 1100.00 FORD SCOTT 20 7369 SMITH 800.00 FORD SCOTT 四、求总和的百分比

现在有个需求：计算各个部门的工资合计以及各个部门合计工资占总工资的比例！现在给出来两种写：

1、普通写法 SQL> select deptno, sm, ssm, round((nvl(sm, 0) / ssm) * 100, 2) as bl 2 from (select deptno, sm, sum(sm) over() as ssm 3 from (select deptno, sum(sal) as sm from emp group by deptno)) 4 order by 1; DEPTNO SM SSM BL ------ ---------- ---------- ---------- 10 8750 29025 30.15 20 10875 29025 37.47 30 9400 29025 32.39 29025 0 SQL> 2、使用ratio_to_report SQL> select deptno, round(ratio_to_report(sm) over() * 100, 2) as bl 2 from (select deptno, sum(sal) as sm from emp group by deptno) 3 order by 1; DEPTNO BL ------ ---------- 10 30.15 20 37.47 30 32.39

总结

本章节主要介绍的就是如何查询最值所在行的信息，不早了，休息！