FIRST_VALUE / LAST_VALUE 的使用？为什么 LAST_VALUE 经常和你期望的不一样（默认窗口为 RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW）？

考察要点

这道题面试官想看你是否真正理解 SQL 窗口函数的工作机制，而不仅仅是会用 OVER() 的基本语法。考察的核心是**窗口框架（Window Frame）**的概念，特别是 ROWS 和 RANGE 的区别，以及在 ORDER BY 存在时，LAST_VALUE 的默认框架 RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW 如何导致非预期的结果。这能有效地区分出对窗口函数有深度理解的候选人。

核心概念

窗口函数在一个与当前行相关的“窗口”或行集上执行计算。FIRST_VALUE 和 LAST_VALUE 是两个典型的窗口函数。

1. FIRST_VALUE(expr)：返回窗口框架内第一行的 expr 的值。
2. LAST_VALUE(expr)：返回窗口框架内最后一行的 expr 的值。

它们的行为完全取决于**窗口框架（Window Frame）**的定义。窗口框架是 OVER() 子句的一部分，用于定义计算的行范围。

窗口框架语法

 1OVER (
 2    [PARTITION BY partition_expression, ... ]
 3    [ORDER BY sort_expression [ASC | DESC], ... ]
 4    [frame_clause]
 5)

其中 frame_clause 的完整语法是： { ROWS | RANGE } BETWEEN <frame_start> AND <frame_end>

• ROWS：基于物理行数定义边界，如 ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING 表示前一行、当前行、后一行。
• RANGE：基于逻辑值定义边界，它包含与当前行在 ORDER BY 列上具有相同值的所有对等行（peers）。

LAST_VALUE 的陷阱：默认框架

当 OVER() 子句中包含 ORDER BY 但未显式指定 frame_clause 时，数据库会使用一个默认的框架： RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW

这意味着窗口从分区的开始一直延伸到当前行。

• 对于 FIRST_VALUE，这个默认框架没有问题，因为它总是能看到分区的第一行。
• 对于 LAST_VALUE，由于框架的终点是 CURRENT ROW，所以它能看到的“最后一行”永远只是“当前行”自己。这就是为什么 LAST_VALUE 的结果常常看起来和当前行的值一样。

示例说明

假设我们有员工薪资表 employees：

目标：找出每个部门内，薪资最低和最高的员工。

错误用法（使用默认框架）：

 1SELECT
 2    name,
 3    dept,
 4    salary,
 5    FIRST_VALUE(name) OVER (PARTITION BY dept ORDER BY salary ASC) AS lowest_salary_earner,
 6    LAST_VALUE(name) OVER (PARTITION BY dept ORDER BY salary ASC) AS highest_salary_earner_WRONG
 7FROM employees;

输出结果：

分析：

• lowest_salary_earner 结果正确，因为默认框架从分区开头 (UNBOUNDED PRECEDING) 开始，FIRST_VALUE 总能找到薪资最低的 Alice (Eng) 和 David (Sales)。
• highest_salary_earner_WRONG 结果错误。对于 Eng 部门的 Carol，她的窗口是 [Alice, Carol]，所以 LAST_VALUE 返回 Carol。对于 Bob，他的窗口是 [Alice, Carol, Bob]，LAST_VALUE 返回 Bob。结果完全不符合预期。

正确用法（指定完整框架）： 要让 LAST_VALUE 看到整个分区，必须将框架扩展到分区的末尾。

 1-- 正确的 Frame Clause
 2ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING

 1SELECT
 2    name,
 3    dept,
 4    salary,
 5    FIRST_VALUE(name) OVER (
 6        PARTITION BY dept ORDER BY salary ASC
 7        ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
 8    ) AS lowest_salary_earner,
 9    LAST_VALUE(name) OVER (
10        PARTITION BY dept ORDER BY salary ASC
11        ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
12    ) AS highest_salary_earner_CORRECT
13FROM employees;

正确输出：

多种解法（获取分区最后一个值）

解法 1：使用 LAST_VALUE 并显式指定完整窗口框架

这是最直接、最符合语义的解法。

• 适用版本：✅ MySQL 8.0+, PostgreSQL, Oracle, SQL Server
• SQL 代码：
  sql

  ⧉⛶

   1SELECT
   2    name,
   3    dept,
   4    salary,
   5    LAST_VALUE(name) OVER (
   6        PARTITION BY dept
   7        ORDER BY salary ASC
   8        ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
   9    ) AS highest_salary_earner
  10FROM employees;

• 复杂度：时间复杂度取决于排序，如果索引支持，则为 $O(N)$；否则为 $O(N \log N)$。空间复杂度为 $O(P)$，其中 $P$ 是最大分区的大小，因为可能需要缓存整个分区。
• 索引依赖：在 (dept, salary) 上建立复合索引可以避免排序，极大提升性能。

解法 2：使用 FIRST_VALUE 并反向排序

一个非常聪明且高效的技巧，通过颠倒 ORDER BY 的顺序，将“最后一个值”变成“第一个值”来获取。

• 适用版本：✅ MySQL 8.0+, PostgreSQL, Oracle, SQL Server
• SQL 代码：
  sql

  ⧉⛶

   1SELECT
   2    name,
   3    dept,
   4    salary,
   5    FIRST_VALUE(name) OVER (
   6        PARTITION BY dept
   7        ORDER BY salary DESC
   8    ) AS highest_salary_earner
   9FROM employees;

• 复杂度：与解法 1 相同。时间 $O(N \log N)$ 或 $O(N)$，空间 $O(P)$。
• 索引依赖：在 (dept, salary DESC) 上建立索引效果最佳。如果只有 (dept, salary ASC) 索引，数据库可能需要反向扫描索引（Index Backward Scan）或进行排序。

解法 3：使用 MAX 或 MIN 作为窗口函数

如果只是想获取分区中的最大/最小值，而不是基于排序的最后一个值，可以直接使用聚合函数作为窗口函数。

• 适用版本：✅ MySQL 8.0+, PostgreSQL, Oracle, SQL Server
• SQL 代码：
  sql

  ⧉⛶

   1-- 注意：这不等价于 LAST_VALUE，它只返回最大薪资值，而不是最高薪资者的名字
   2SELECT
   3    name,
   4    dept,
   5    salary,
   6    MAX(salary) OVER (PARTITION BY dept) AS highest_salary_in_dept
   7FROM employees;

• 说明：这种方法不需要 ORDER BY 和 frame_clause，性能通常很好。但它无法直接获取“最高薪资员工的名字”，只能获取“最高薪资”这个值。要获取名字，仍需结合 FIRST_VALUE/LAST_VALUE 或其他技巧。

常见低分回答

1. 不知道默认框架：只说 LAST_VALUE 是取最后一个值，当被问及为何结果不对时无法解释，暴露了对窗口框架知识的缺失。
2. 混淆 ROWS 和 RANGE：无法清晰解释两者的区别，尤其是在 ORDER BY 列有重复值时的不同行为。
3. 用 MAX() 聚合函数替代：回答说“可以用 MAX() 窗口函数”，但没有意识到 MAX(name) 在 SQL 中通常是无效的（除非是特定方言），并且 MAX(salary) 只能取到值，取不到对应的行信息。
4. 使用低效的自连接或子查询：试图用关联子查询来模拟，这在数据量大时性能极差（近似 $O(N^2)$）。
   sql

   ⧉⛶

    1-- 性能灾难
    2SELECT
    3    e1.name,
    4    e1.dept,
    5    e1.salary,
    6    (SELECT e2.name
    7     FROM employees e2
    8     WHERE e2.dept = e1.dept
    9     ORDER BY e2.salary DESC
   10     LIMIT 1) AS highest_salary_earner
   11FROM employees e1;

5. 错误地指定框架：例如写成 ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING，这对于 FIRST_VALUE 是错误的。

工程实践与优化

• 索引是关键：窗口函数的性能瓶颈通常在 PARTITION BY 和 ORDER BY。务必在 (partition_key, order_key) 上建立复合索引。例如本例中的 CREATE INDEX idx_dept_salary ON employees (dept, salary);。
• 观察执行计划：使用 EXPLAIN 查看执行计划。如果 Extra 列（MySQL）出现 Using filesort，说明排序无法利用索引，需要优化。理想情况下，数据应通过索引有序地提供给窗口函数处理器。在 PostgreSQL 中，查看 WindowAgg 节点下的 Sort 节点。
• 分区大小与内存：当窗口框架需要访问整个分区时（如 UNBOUNDED FOLLOWING），数据库需要将整个分区的数据加载到内存或溢出到磁盘。如果分区极大（例如，一个用户的所有历史订单），可能导致严重性能问题。此时应考虑：
  • 业务上是否能缩小分区范围（如只看近一年的订单）。
  • 增加服务器 work_mem (PostgreSQL) 或相关 buffer size。
• ROWS vs RANGE 的选择：在 ORDER BY 列可能存在重复值时，ROWS 的行为更符合“物理位置”的直觉，通常是更安全、更可预测的选择。RANGE 的逻辑行为虽然强大，但也更容易引入意想不到的计算结果。

常见追问与延伸题目

1. 追问：LEAD/LAG 和 FIRST_VALUE/LAST_VALUE 有什么区别？

   • 回答要点：LEAD 和 LAG 访问的是相对位置的行（前/后第 N 行），而 FIRST_VALUE 和 LAST_VALUE 访问的是窗口框架内的绝对位置的行（第一行/最后一行）。
   • 场景：计算环比（上一个周期/下一个周期的值）用 LAG/LEAD。计算与整个分区基准（如最高/最低值）的差异用 FIRST_VALUE/LAST_VALUE。

2. 追问：刚才提到了 ROWS 和 RANGE，请举一个例子说明它们在 ORDER BY 列值重复时的具体差异。

   • 回答要点：假设薪资表中有两个人的薪资都是 100000。
     • 数据：(Alice, 90k), (Carol, 100k), (Chris, 100k), (Bob, 120k)
     • 当处理到 Carol 这一行时：
       • ORDER BY salary ... ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW：框架是 [Alice, Carol]。LAST_VALUE 是 Carol。
       • ORDER BY salary ... RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW：框架是 [Alice, Carol, Chris]，因为它包含所有薪资等于 CURRENT ROW (Carol) 的对等行 Chris。LAST_VALUE 的结果将是 Chris（取决于数据库对对等行的内部排序）。这个不确定性是使用 RANGE 时需要特别小心的。

3. 追问：如果我用的 MySQL 5.7，它不支持窗口函数，你怎么实现获取每个部门最高薪资员工的需求？

   • 回答要点：这是一个考察 SQL 基础和解决问题能力的经典问题。
   • 方案 1 (JOIN + 子查询)：先用子查询找到每个部门的最高薪资，然后将原表与这个结果进行 JOIN。
     sql

     ⧉⛶

      1SELECT
      2    e.name,
      3    e.dept,
      4    e.salary
      5FROM
      6    employees e
      7JOIN
      8    (SELECT dept, MAX(salary) AS max_salary FROM employees GROUP BY dept) AS max_sal
      9ON
     10    e.dept = max_sal.dept AND e.salary = max_sal.max_salary;

   • 方案 2 (关联子查询与 NOT EXISTS)：对于表 e1 中的每一行，如果不存在另一个同部门的员工 e2 其薪水比 e1 更高，那么 e1 就是薪水最高的员工之一。
     sql

     ⧉⛶

      1SELECT
      2    e1.name,
      3    e1.dept,
      4    e1.salary
      5FROM
      6    employees e1
      7WHERE NOT EXISTS (
      8    SELECT 1
      9    FROM employees e2
     10    WHERE e2.dept = e1.dept AND e2.salary > e1.salary
     11);

   • 方案 3 (用户变量模拟)：这是 MySQL 特有的技巧，通过排序和用户变量来模拟 ROW_NUMBER()。虽然巧妙但不推荐在生产中使用，因为它依赖于执行顺序，且在 MySQL 8.0+ 中已被官方不推荐。