LAG() 和 LEAD() 的语义？典型应用：求同比/环比？

考察要点

这道题面试官想看你是否掌握了窗口函数（Window Functions）的核心用法，特别是 LAG() 和 LEAD() 这两个导航函数。考察点具体包括：

1. 基本语义：是否能清晰区分 LAG()（向前看）和 LEAD()（向后看）的含义、参数和语法。
2. OVER 子句：是否理解 PARTITION BY（分区）和 ORDER BY（排序）在决定“前一行”或“后一行”时的关键作用。
3. 应用能力：能否将理论知识应用于解决经典的业务场景，如计算同比、环比增长率，这是衡量候选人实战能力的试金石。

核心概念

窗口函数与聚合函数（如 SUM, COUNT）不同，它在与当前行相关的“窗口”上进行计算，但不会将多行聚合为一行，而是为结果集中的每一行都返回一个值。

1. LAG() 函数

LAG() 函数用于访问当前行之前的某一行的数据。

• 语义：获取当前行在分区内按指定排序规则的“上一行”的某个列值。
• 语法：
  sql

  ⧉⛶

   1LAG(expression [, offset [, default_value]]) OVER (
   2    [PARTITION BY partition_expression, ...]
   3    ORDER BY sort_expression [ASC | DESC], ...
   4)

• 参数：
  • expression: 要获取值的列或表达式。
  • offset: (可选) 向前偏移的行数，默认为 1。
  • default_value: (可选) 如果偏移后超出分区范围（如分区的第一行），则返回此默认值，默认为 NULL。

2. LEAD() 函数

LEAD() 函数与 LAG() 相反，用于访问当前行之后的某一行的数据。

• 语义：获取当前行在分区内按指定排序规则的“下一行”的某个列值。
• 语法：与 LAG() 类似。
  sql

  ⧉⛶

   1LEAD(expression [, offset [, default_value]]) OVER (
   2    [PARTITION BY partition_expression, ...]
   3    ORDER BY sort_expression [ASC | DESC], ...
   4)

• 参数：与 LAG() 相同，但 offset 指的是向后偏移。

示例说明

假设我们有一张电商月度销售额表 sales：

使用 LAG() 和 LEAD() 获取每个产品上个月和下个月的销售额：

 1SELECT
 2    product_id,
 3    sale_month,
 4    revenue,
 5    LAG(revenue, 1, 0) OVER (PARTITION BY product_id ORDER BY sale_month) AS prev_month_revenue,
 6    LEAD(revenue, 1, 0) OVER (PARTITION BY product_id ORDER BY sale_month) AS next_month_revenue
 7FROM sales;

输出结果：

• PARTITION BY product_id：将数据按产品 A 和 B 分成两个独立的窗口。
• ORDER BY sale_month：在每个产品窗口内，按月份升序排列，这是确定“上个月”和“下个月”的依据。
• LAG(revenue, 1, 0)：取上个月的 revenue，如果不存在（如 A 产品的 1 月），则返回默认值 0。

版本兼容性

• 支持: MySQL 8.0+, PostgreSQL 8.4+, Oracle 8i+, SQL Server 2012+。
• 不支持: MySQL 5.7 及更早版本。在这些版本中，需要使用其他方法（如自连接）来模拟。

多种解法（求月度销售额环比增长率）

问题：计算每个月的销售额相较于上个月的环比增长率。增长率公式为 (本月收入 - 上月收入) / 上月收入。

数据表 monthly_revenue：

解法 1：使用 LAG() 窗口函数（推荐）

这是最现代、最直观、最高效的解法。

• 版本：✅ MySQL 8.0+ / PostgreSQL / Oracle；❌ MySQL 5.7
• SQL 实现：
  sql

  ⧉⛶

   1WITH monthly_revenue_with_lag AS (
   2    SELECT
   3        month,
   4        revenue,
   5        -- 获取上个月的收入，如果不存在则为 NULL
   6        LAG(revenue, 1) OVER (ORDER BY month) AS prev_month_revenue
   7    FROM
   8        monthly_revenue
   9)
  10SELECT
  11    month,
  12    revenue,
  13    prev_month_revenue,
  14    -- 计算环比增长率，注意处理除数为 0 或 NULL 的情况
  15    CASE
  16        WHEN prev_month_revenue IS NULL OR prev_month_revenue = 0 THEN NULL
  17        ELSE (revenue - prev_month_revenue) * 1.0 / prev_month_revenue
  18    END AS mom_growth_rate
  19FROM
  20    monthly_revenue_with_lag
  21ORDER BY
  22    month;

• 时间/空间复杂度：时间复杂度主要由 ORDER BY 决定，为 $O(N \log N)$。如果数据已按 month 排序或有相应索引，则可降至 $O(N)$。空间复杂度为 $O(N)$，用于存储中间结果。
• 索引依赖：在 month 列上建立索引可以极大地加速 ORDER BY 操作，避免全表排序。

解法 2：使用自连接 (Self-Join)

这是兼容老版本数据库（如 MySQL 5.7）的经典解法。

• 版本：✅ 所有 SQL 版本
• SQL 实现：
  sql

  ⧉⛶

   1SELECT
   2    t1.month,
   3    t1.revenue,
   4    t2.revenue AS prev_month_revenue,
   5    -- 计算环比增长率
   6    CASE
   7        WHEN t2.revenue IS NULL OR t2.revenue = 0 THEN NULL
   8        ELSE (t1.revenue - t2.revenue) * 1.0 / t2.revenue
   9    END AS mom_growth_rate
  10FROM
  11    monthly_revenue t1
  12LEFT JOIN
  13    monthly_revenue t2 ON t2.month = DATE_SUB(t1.month, INTERVAL 1 MONTH) -- MySQL specific
  14    -- 对于 PostgreSQL: ON t2.month = t1.month - INTERVAL '1 month'
  15ORDER BY
  16    t1.month;

• 时间/空间复杂度：如果 month 列上有索引，LEFT JOIN 的效率很高，时间复杂度接近 $O(N \log N)$（对于索引查找）或 $O(N)$（对于哈希/归并连接）。若无索引，则可能退化为 $O(N^2)$。空间复杂度为 $O(N)$。
• 索引依赖：month 列上必须有索引（最好是主键或唯一索引），否则连接操作会非常慢。

何时使用：

• LAG()：首选方案。代码更简洁、可读性强、意图明确，且通常数据库对窗口函数的优化更好。
• 自连接：当必须兼容不支持窗口函数的老旧数据库（如 MySQL 5.7）时使用。代码相对复杂，且日期处理函数可能存在跨数据库的兼容性问题。

常见低分回答

1. 在 OVER() 子句中忘记 ORDER BY：LAG(revenue) OVER ()。这会导致 SQL 报错或返回不确定的结果，因为数据库不知道“前一行”是哪一行。
2. 试图在 WHERE 或 GROUP BY 子句中使用窗口函数：SELECT ... FROM ... WHERE LAG(revenue) OVER(...) > 100。这是非法的，因为窗口函数在逻辑上是在 WHERE 和 GROUP BY 之后执行的。必须使用 CTE 或子查询。
3. 计算增长率时未处理除数为零或 NULL 的情况：(revenue - prev_revenue) / prev_revenue。当 prev_revenue 为 0 时会导致除零错误；当其为 NULL 时（如第一行），整个表达式结果为 NULL。高分回答应使用 CASE 语句或 NULLIF 进行健壮性处理。
4. 自连接时使用 INNER JOIN 而非 LEFT JOIN：... FROM monthly_revenue t1 INNER JOIN monthly_revenue t2 ON ...。这会导致结果中缺少第一个月的数据，因为第一个月在表中找不到它的“上一个月”。
5. 对 PARTITION BY 和 GROUP BY 的混淆：认为 PARTITION BY 会像 GROUP BY 一样减少行数。PARTITION BY 只是定义计算窗口的边界，不会改变结果集的行数。

工程实践与优化

• 索引是关键：对于 OVER (PARTITION BY a, b ORDER BY c, d)，在 (a, b, c, d) 上建立复合索引能带来巨大性能提升。优化器可以利用该索引直接按序读取数据，避免昂贵的内存排序（filesort）。
• 执行计划分析：在 MySQL 中，使用 EXPLAIN ANALYZE (8.0.18+) 查看执行计划。如果看到 Window: (lag) ... 后面跟着 -> Sort，说明发生了排序。如果索引有效，则不会有显式的 Sort 步骤。
• 内存消耗：当分区（PARTITION BY 的分组）非常大时，窗口函数可能会消耗大量内存来存储分区内的数据。对于超大规模数据集，如果内存成为瓶颈，可能需要考虑分批处理或使用 ClickHouse、Apache Doris 等专为大规模分析设计的 OLAP 数据库。
• 避免在 ON 子句中使用函数：在自连接解法中，ON t2.month = DATE_SUB(t1.month, INTERVAL 1 MONTH) 里的 DATE_SUB 函数作用在 t1.month 上，这使得 t1 表无法有效利用 month 列的索引进行连接。更优化的写法是 ON t1.month = DATE_ADD(t2.month, INTERVAL 1 MONTH)，将函数作用在 t2 上，这样 t1 可以走索引。

常见追问与延伸题目

1. 追问：如果要计算“季度”同比增长（去年同季度），SQL 该怎么写？

   • 回答要点：思路类似，但偏移量和分区逻辑更复杂。
     • 方法一（窗口函数）：使用 LAG(revenue, 4) OVER (PARTITION BY QUARTER(sale_date) ORDER BY YEAR(sale_date)) 是不行的，因为 LAG 是按行偏移。正确做法是 LAG(revenue, 1) OVER (PARTITION BY QUARTER(sale_date) ORDER BY YEAR(sale_date))，但这要求数据是按年、按季度聚合好的。如果原始数据是月度的，需要先用 DATE_FORMAT 或 QUARTER() 函数提取年份和季度，然后使用 LAG(revenue, 1) OVER (PARTITION BY product_id, QUARTER(sale_month) ORDER BY YEAR(sale_month))。
     • 方法二（自连接）：LEFT JOIN ... ON t2.sale_month = DATE_SUB(t1.sale_month, INTERVAL 1 YEAR)。这是最直接的方式，连接当前行与一年前的同一月份行。

2. 追问：除了 LAG / LEAD，你还知道哪些常用的窗口函数？请举例说明 FIRST_VALUE 和 LAST_VALUE 的应用和陷阱。

   • 回答要点：
     • 其他函数：排名函数（ROW_NUMBER, RANK, DENSE_RANK）、分布函数（PERCENT_RANK, CUME_DIST）、聚合窗口函数（SUM(...) OVER (...), AVG(...) OVER (...)）。
     • FIRST_VALUE(expr) OVER(...)：获取分区中的第一个值。例如，获取每个部门里薪水最高的员工姓名。
     • LAST_VALUE(expr) OVER(...)：获取分区中的最后一个值。
     • 陷阱：LAST_VALUE 的默认窗口框架是 RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW。这意味着它只会看到从分区开始到当前行的所有行，因此每一行的 LAST_VALUE 都是当前行的值。要获取整个分区的最后一个值，必须显式定义框架为 ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING。

3. 追问：如果数据量巨大，比如几十亿条日志，用 LAG() 计算会话的上一个页面，会有什么性能问题？如何优化？

   • 回答要点：
     • 性能问题：主要有两个：1) 内存压力，如果一个用户（一个分区）的会话日志非常长，整个分区数据可能需要加载到内存中进行排序。2) 排序开销，即使有索引，对海量数据进行排序也是昂贵的。
     • 优化策略：
       • 索引优化：确保 (user_id, session_id, timestamp)上有复合索引，以匹配 PARTITION BY user_id, session_id ORDER BY timestamp。
       • 数据预处理/批处理：不在主 OLTP 数据库上直接运行。将数据导入到专门的 OLAP 系统（如 ClickHouse, Doris, StarRocks）中，这些系统对窗口函数有列式存储和并行计算的极致优化。
       • 应用层逻辑：如果数据库实在无法承受，可以在数据流处理（如 Flink/Spark Streaming）中，通过 keyed state 来维护每个用户的上一个页面状态，实现流式计算，避免在数据库中进行大规模的批处理查询。