MAX() OVER窗口函数分区排序返回值异常及内部工作原理咨询

问题场景 #

尝试通过以下SQL查询各部门最高薪资时，结果不符合预期：

SELECT name,
       dept,
       salary,
       MAX(salary) OVER (PARTITION BY dept ORDER BY salary asc ) AS dept_max_salary
FROM zone_hr_dev.employees;

该查询中，每个部门的dept_max_salary列会逐行递增，仅分区最后一行才显示部门最高薪资。

而使用以下SQL时结果正确，每个部门的所有行都直接显示该部门的最高薪资：

SELECT name,
       dept,
       salary,
       MAX(salary) OVER (PARTITION BY dept ORDER BY salary desc ) AS dept_max_salary
FROM zone_hr_dev.employees;

MAX()窗口函数的内部工作机制（含PARTITION BY + ORDER BY） #

当窗口函数同时指定PARTITION BY和ORDER BY时，默认的计算范围是当前分区的起始行到当前行（等价于显式声明RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW）：

• 若ORDER BY salary asc，分区内数据按薪资从小到大排序。逐行计算时，第一行的最大值是自身薪资，第二行是前两行的最大值，以此类推，直到最后一行才覆盖整个分区，得到部门最高薪资。
• 若ORDER BY salary desc，分区内数据按薪资从大到小排序。第一行就是部门最高薪资，后续每行的计算范围都包含第一行到当前行，因此最大值始终是部门最高薪资，所有行的结果一致。

正确获取各部门最高薪资的方法 #

方法1：移除ORDER BY（最简洁、语义最清晰） #

不需要排序时，窗口函数默认计算范围是整个分区，直接得到每个部门的最高薪资：

SELECT name,
       dept,
       salary,
       MAX(salary) OVER (PARTITION BY dept) AS dept_max_salary
FROM zone_hr_dev.employees;

方法2：显式指定全分区范围（即使保留ORDER BY也能生效） #

如果需要保留排序逻辑，同时要获取整个分区的最大值，可以显式声明窗口范围覆盖分区的所有行：

SELECT name,
       dept,
       salary,
       MAX(salary) OVER (
           PARTITION BY dept 
           ORDER BY salary asc 
           RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
       ) AS dept_max_salary
FROM zone_hr_dev.employees;

方法3：使用降序排序（即你用到的正确写法） #

虽然这种写法能得到正确结果，但语义上不够直观，建议优先使用前两种方法。

内容的提问来源于stack exchange，提问作者sj17