查找第N高薪水:MAX与DENSE_RANK的优劣、去重及性能对比
从Employee表查询第二高薪水的方案修正与性能对比
常见基础实现
针对查找第二高薪水的需求,行业内通常有两种实现思路:
基于MAX聚合的实现
对应查询语句为:
select max(salary) as SecondHighestSalary from employee where salary < (select max(salary) from employee)
该写法的天然特性是:当表中不存在第二高薪水时,聚合操作会自动返回
NULL,天然匹配“返回单值结果”的需求。
基于DENSE_RANK窗口函数的初始实现
对应查询语句为:
select e.salary as SecondHighestSalary from ( select salary, dense_rank() OVER (order by salary desc) dr from employee) e where dr = 2
DENSE_RANK写法的缺陷修正
现存问题
当Employee表的salary字段值为[300, null, null]时,上述初始查询会返回[null, null]的多值结果,不符合需求要求的单条结果返回规则。
问题根源是:窗口函数计算排名时没有过滤NULL值,当表中仅存在300一个非空的最高薪资时,后续两个NULL值会被DENSE_RANK标记为排名2,最终产生重复返回结果。
修正方案
调整逻辑从三个维度入手:
- 排名计算阶段过滤空薪资,避免NULL参与排名流程,从根源上阻断无效值被标记为目标排名
- 外层对筛选出的结果做聚合收敛,即使存在同排名的重复值,也强制聚合为单条结果
- 利用聚合函数的空值特性,保证无匹配结果时自动返回NULL
修正后的查询语句为:
select max(salary) as SecondHighestSalary from ( select salary, dense_rank() OVER (order by salary desc) dr from employee -- 过滤空值,避免NULL参与排名计算 where salary is not null ) e where dr = 2
修正后的效果:当表中仅存在300一个非空薪资时,排名2的行不存在,max()聚合会自动返回NULL;当存在多个同值的第二高薪资时,max()会将同值收敛为单条结果,不会出现重复返回的问题。
两种方案的性能对比
从内存占用、处理耗时的维度评估,MAX聚合方案的整体性能更优,差异体现在两个层面:
- 处理逻辑效率:MAX方案仅需要两次值匹配(第一次定位最高薪资,第二次定位低于最高值的最大薪资),过程中不需要对全表数据做全量排序,也不需要为窗口函数计算预留额外的排序中间结果存储空间。
- 资源消耗水平:DENSE_RANK方案需要对全表的非空薪资做全量排序,为每一行计算对应的排名值,需要额外的临时内存(或磁盘临时表)存储排序中间结果,当表数据量较大时,内存占用、排序耗时都会明显高于MAX聚合方案。
补充:如果Employee表的salary字段建立了降序索引,MAX方案可以直接通过索引定位到最高值、次高值,完全不需要全表扫描,性能优势会进一步放大。
内容的提问来源于stack exchange,提问作者user4628567
相关产品推荐
相关产品推荐

