You need to enable JavaScript to run this app. 查找第N高薪水:MAX与DENSE_RANK的优劣、去重及性能对比
优惠活动
大模型
产品
解决方案
定价
更多

查找第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

相关产品推荐
方舟 Agent Plan

超全模态模型 × Harness 升级,最新支持 Deepseek-V4.1-Flash、GLM-5.3 系列、Doubao-Seedream-5.0-pro、Kimi-K3 (部分), 限时 9.9 元起

最近更新时间:2026.11.15 23:06:03