SQL Interview Prep · 课时

使用 LAG 和 LEAD 获取相邻行

Q: 「使用 LAG 和 LEAD 获取相邻行」这节课中我会学到什么？

无需自连接即可访问前一行和后一行的值 你通过在浏览器中直接运行的动手代码来练习 SQL Interview Prep，全天候 AI 导师会在你学习这节课的过程中回答你的问题。

无需自连接即可访问前一行和后一行的值

第 1 / 4 课13 个步骤

使用 LAG 和 LEAD 获取相邻行 是 CoddyKit 上的免费 SQL Interview Prep 课时。这是第 1 节课，共 4 节。你可以在下方免费阅读本课时的完整内容 — 然后在浏览器中使用内置代码编辑器和全天候 AI 导师进行实践。这是 SQL Interview Prep 学习路径的一部分，你的进度在网页和 CoddyKit 应用中同步。 SQL Interview Prep 课程共包含 4 节课。

面试官会问的问题

分析师面试中最常见的问题之一是：“不使用自连接，如何将每一行与它前面的一行进行比较？”例如月度环比收入、用户上一次登录，或序列中的下一个事件。

简洁的答案是使用 LAG 和 LEAD 窗口函数。它们可以让一行查看相邻行的值，同时保留每一行的全部明细。在本课中，您将建立一个关于它们如何浏览相邻行的准确思维模型。

LAG 和 LEAD 的作用

LAG(col) 返回 col 在上一行中的值。LEAD(col) 返回下一行中的值。“上一行”和“下一行”完全由 OVER 子句中的 ORDER BY 定义。

LAG 向后查看。
LEAD 向前查看。

二者都是偏移窗口函数：它们不会合并行，只会将相邻行的值附加到当前行。

LAG 的基本语法

下面是典型写法。我们有一个包含 month 和 revenue 的 sales 表。我们希望每一行都额外显示上个月的收入。

OVER (ORDER BY month) 告诉引擎如何定义“上一行”。第一行没有前驱，因此其中的 prev_revenue 是 NULL。

SELECT
  month,
  revenue,
  LAG(revenue) OVER (ORDER BY month) AS prev_revenue
FROM sales
ORDER BY month;

读取结果

对于以下数据：2024-01 = 100、2024-02 = 130、2024-03 = 120，查询返回：

一月：收入 100，上月收入 NULL
二月：收入 130，上月收入 100
三月：收入 120，上月收入 130

每一行都从排序后集合中紧邻其上方的行提取值。不需要自连接，不需要子查询，也不会丢失行。

LEAD 向前查看

LEAD 与之相反。当一行需要知道接下来会发生什么时，可以使用它，例如获取下一次购买日期来计算订单之间的时间间隔。

排序后集合中的最后一行没有后继行，因此它的 LEAD 结果是 NULL。

SELECT
  month,
  revenue,
  LEAD(revenue) OVER (ORDER BY month) AS next_revenue
FROM sales
ORDER BY month;

偏移量参数

这两个函数都接受一个可选的第二个参数，用于指定跳过多少行。LAG(col, 2) 向前回溯两行，LEAD(col, 3) 向后跳过三行。

面试官会利用这一点提问，例如“获取两个月前的收入”或“获取下方第三行的值”。默认偏移量是 1。

SELECT
  month,
  revenue,
  LAG(revenue, 2) OVER (ORDER BY month) AS revenue_2_months_ago
FROM sales
ORDER BY month;

默认值参数

第三个参数会在不存在相邻行时提供替代值，而不是得到 NULL。其签名是 LAG(col, offset, default)。

当后续计算无法处理 NULL 时，这个参数很有用。例如，将缺失的上一期值视为 0，这样仍然可以计算差值。

SELECT
  month,
  revenue,
  LAG(revenue, 1, 0) OVER (ORDER BY month) AS prev_revenue
FROM sales
ORDER BY month;

PARTITION BY 会重置窗口

真实数据很少只有一个全局序列。您通常需要在每个客户、产品或地区内部进行比较。PARTITION BY 会在每个分区的开头重新开始 LAG/LEAD 计算。

这意味着每个分区的第一行都会从 LAG 得到 NULL，不会跨越边界将其他客户的数据泄漏进来。

SELECT
  customer_id,
  order_date,
  amount,
  LAG(amount) OVER (
    PARTITION BY customer_id
    ORDER BY order_date
  ) AS prev_amount
FROM orders;

示例：订单之间的天数

一个常见任务是计算客户连续下单之间的间隔。使用 LAG 提取上一笔订单的日期，然后做减法。

每位客户的第一笔订单会得到 NULL，因为没有可供相减的前一个日期。这正是面试官希望您用窗口函数解决的按客户比较问题。

SELECT
  customer_id,
  order_date,
  order_date - LAG(order_date) OVER (
    PARTITION BY customer_id
    ORDER BY order_date
  ) AS days_since_prev
FROM orders;

为什么不使用自连接

在窗口函数出现之前，解决方法是相关自连接：将表与自身连接，条件是“日期小于当前日期的行中，日期最大的一行”。这种方法虽然可行，但写法冗长，遇到并列值容易出错，而且通常更慢。

LAG/LEAD 用一行就能表达意图。
它们会在一次有序遍历中完成计算。
通过您的 ORDER BY 可以确定性地处理并列值。

说出“我会使用 LAG 而不是自连接”，就能体现您对这类用法很熟悉。

常见陷阱：缺少 ORDER BY

如果 OVER 子句中没有 ORDER BY，“前一行”就是未定义的。有些数据库引擎会拒绝执行，另一些则会返回不可预测的结果。请始终为窗口指定排序。

还请记住，OVER 内部的排序与查询外层的 ORDER BY 相互独立。窗口决定哪一行是相邻行；外层子句只决定显示顺序。

快速检查

测试您对偏移窗口函数的理解。

在交互式课程中回答

回顾

您现在已经掌握了偏移窗口函数：

LAG(col) 读取前一行，LEAD(col) 读取后一行，具体由窗口的 ORDER BY 定义。
可选参数：LAG(col, offset, default)。
PARTITION BY 会按组重置导航，因此边界行的结果是 NULL。
它们可以取代用于比较相邻行的繁琐自连接。

接下来，我们将把这些用法应用到分析师必考的问题：逐期变化。

免费开始

用 AI 导师学习 SQL — 免费

在浏览器中编写并运行真实代码，获得全天候 AI 导师的即时帮助，并在网页或应用中继续学习。

课程: 30
课程: 120

免费开始学习查看完整课程

常见问题解答

「使用 LAG 和 LEAD 获取相邻行」课时是免费的吗？

是的 — 「使用 LAG 和 LEAD 获取相邻行」的完整文本可在网页上免费阅读。要进行交互式练习（内置代码编辑器和全天候 AI 导师）并解锁 SQL Interview Prep 课程的其余内容，请升级到 CoddyKit PRO。 SQL Interview Prep 课程共包含 4 节课。

「使用 LAG 和 LEAD 获取相邻行」这节课中我会学到什么？

无需自连接即可访问前一行和后一行的值你通过在浏览器中直接运行的动手代码来练习 SQL Interview Prep，全天候 AI 导师会在你学习这节课的过程中回答你的问题。

学习 SQL Interview Prep 需要有经验吗？

无需任何先前经验。CoddyKit 上的 SQL Interview Prep 课程适合初学者到高级学习者，你可以从这里开始或从头开始，按照自己的节奏学习。这是第 1 节课，共 4 节。

「使用 LAG 和 LEAD 获取相邻行」课时需要多长时间？

大多数 CoddyKit 课程大约需要 5–10 分钟。每节课都很精短且互动，所以你能稳步进步，并在网页和应用中从离开的地方继续。

我能在这节 SQL Interview Prep 课中编写并运行代码吗？

能。每节 SQL Interview Prep 课都包含内置代码编辑器，你可以在浏览器中直接编写并运行真实代码，并获得即时 AI 反馈 — 无需本地设置。

此课程中的所有课时

← 返回 SQL Interview Prep