🙏 我们非常需要您的支持。 我们需要资金来继续我们的使命：发布新课程并持续改进平台。如果您愿意，请通过捐助支持这个项目。 现在帮助这个项目 →
SQL 代码已复制到剪贴板

SQLtest.online

练习

课程

练习场

测试

课程 7.4 · 阅读时间：约 9 分钟

本课程介绍窗口函数 LAG、LEAD、FIRST_VALUE 和 LAST_VALUE。您将学习如何在不使用 JOIN 的情况下检索前一个和下一个值，如何在窗口内获取第一个和最后一个值，以及为什么 LAST_VALUE 通常需要显式框架。到课程结束时，您将能够自信地使用这些函数进行行比较、趋势分析和分析报告。

LAG、LEAD、FIRST_VALUE 和 LAST_VALUE

在上一课中，我们讨论了窗口框架，并观察了框架边界如何影响计算。现在我们转向可以向后、向前查看以及查看窗口内边缘值的函数。

这些函数在分析中尤其有用：它们帮助比较每日销售、识别客户的先前行为、计算与早期值的变化，并在不使用 self join 的情况下找到组中的第一条或最后一条记录。

LAG LEAD FIRST_VALUE LAST_VALUE

这些函数的作用

这四个函数都是窗口函数，并与 OVER (...) 一起使用。

  • LAG 返回窗口中前一行的值。
  • LEAD 返回窗口中后一行的值。
  • FIRST_VALUE 返回当前窗口中的第一个值。
  • LAST_VALUE 返回当前窗口中的最后一个值。

关键思想很简单：当前行保持不变，但可以访问同一分区中其他行的值。

基本语法

LAG 和 LEAD

LAG(expression [, offset [, default_value]]) OVER (
    [PARTITION BY ...]
    ORDER BY ...
)

LEAD(expression [, offset [, default_value]]) OVER (
    [PARTITION BY ...]
    ORDER BY ...
)
  • expression 是您想从另一行检索的值。
  • offset 是向后或向前移动的行数。
  • default_value 是如果该行不存在时返回的值。

FIRST_VALUE 和 LAST_VALUE

FIRST_VALUE(expression) OVER (
    [PARTITION BY ...]
    ORDER BY ...
    [frame_clause]
)

LAST_VALUE(expression) OVER (
    [PARTITION BY ...]
    ORDER BY ...
    [frame_clause]
)

对于 FIRST_VALUE，尤其是 LAST_VALUE，窗口框架很重要。没有显式框架，LAST_VALUE 通常会产生与初学者预期不同的结果。

使用 LAG

LAG 在需要将当前行与前一行进行比较时非常有用。

客户的前一次付款

SELECT
    customer_id,
    payment_date,
    amount,
    LAG(amount) OVER (
        PARTITION BY customer_id
        ORDER BY payment_date
    ) AS previous_amount
FROM payment
WHERE customer_id = 1
ORDER BY payment_date;

结果：每行显示当前付款和同一客户的前一次付款金额。

与前一次付款的差异

SELECT
    customer_id,
    payment_date,
    amount,
    amount - LAG(amount, 1, 0) OVER (
        PARTITION BY customer_id
        ORDER BY payment_date
    ) AS amount_diff
FROM payment
WHERE customer_id = 1
ORDER BY payment_date;

结果：您可以看到当前付款与前一次付款的差异。对于第一行，使用 0 作为默认值。

使用 LEAD

LEAD 的工作方式是对称的，但向前查看而不是向后查看。

客户的下一次付款

SELECT
    customer_id,
    payment_date,
    amount,
    LEAD(amount) OVER (
        PARTITION BY customer_id
        ORDER BY payment_date
    ) AS next_amount
FROM payment
WHERE customer_id = 1
ORDER BY payment_date;

结果：每行显示该客户的下一次付款金额。

下一次租赁日期

SELECT
    customer_id,
    rental_date,
    LEAD(rental_date) OVER (
        PARTITION BY customer_id
        ORDER BY rental_date
    ) AS next_rental_date
FROM rental
WHERE customer_id = 1
ORDER BY rental_date;

结果：查询显示同一客户将何时进行下一次租赁。

使用 FIRST_VALUE

FIRST_VALUE 返回窗口中的第一个值。当您想将当前行与起始点进行比较时，这非常有用。

客户的第一次付款

SELECT
    customer_id,
    payment_date,
    amount,
    FIRST_VALUE(amount) OVER (
        PARTITION BY customer_id
        ORDER BY payment_date
        ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
    ) AS first_payment_amount
FROM payment
WHERE customer_id = 1
ORDER BY payment_date;

结果：客户第一次付款的金额在窗口中的每一行上都重复。

将当前付款与第一次付款进行比较

SELECT
    customer_id,
    payment_date,
    amount,
    amount - FIRST_VALUE(amount) OVER (
        PARTITION BY customer_id
        ORDER BY payment_date
        ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
    ) AS diff_from_first
FROM payment
WHERE customer_id = 1
ORDER BY payment_date;

结果：这有助于衡量当前值与序列中第一个值的偏离程度。

使用 LAST_VALUE

LAST_VALUE 看起来很简单，但这是期望常常破裂的地方。

重要的细微差别：默认框架

如果您写这个：

SELECT
    customer_id,
    payment_date,
    amount,
    LAST_VALUE(amount) OVER (
        PARTITION BY customer_id
        ORDER BY payment_date
    ) AS last_amount_default
FROM payment
WHERE customer_id = 1
ORDER BY payment_date;

那么在许多数据库管理系统中，结果不是整个分区的最后一个值，而是当前框架末尾的值。通常，这意味着当前行本身。

获取分区中最后一个值的正确版本

SELECT
    customer_id,
    payment_date,
    amount,
    LAST_VALUE(amount) OVER (
        PARTITION BY customer_id
        ORDER BY payment_date
        ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
    ) AS last_payment_amount
FROM payment
WHERE customer_id = 1
ORDER BY payment_date;

结果：现在每一行都可以看到客户在整个分区中的最后一次付款金额。

为什么这很有用

当您需要将当前值与系列中的最后已知值、最终订单状态或客户的最后付款进行比较时，这种模式非常方便。

比较 LAG、LEAD、FIRST_VALUE 和 LAST_VALUE

函数返回的内容典型用例
LAG前一行的值与过去的值进行比较
LEAD后一行的值为下一步或日期做准备
FIRST_VALUE窗口中的第一个值比较的基准
LAST_VALUE窗口中的最后一个值序列中的最终值

如果任务是比较相邻行，LAG 和 LEAD 通常是合适的工具。如果您需要在窗口的开始或结束处的参考点，请使用 FIRST_VALUE 和 LAST_VALUE。

实际示例：每日收入与相邻天的比较

首先，按天汇总付款，然后对汇总结果应用窗口函数：

SELECT
    pay_day,
    daily_total,
    LAG(daily_total) OVER (ORDER BY pay_day) AS previous_day_total,
    LEAD(daily_total) OVER (ORDER BY pay_day) AS next_day_total,
    FIRST_VALUE(daily_total) OVER (
        ORDER BY pay_day
        ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
    ) AS first_day_total,
    LAST_VALUE(daily_total) OVER (
        ORDER BY pay_day
        ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
    ) AS last_day_total
FROM (
    SELECT
        DATE(payment_date) AS pay_day,
        SUM(amount) AS daily_total
    FROM payment
    GROUP BY DATE(payment_date)
) AS daily_stats
ORDER BY pay_day;

结果：每个日期都可以访问前一天的收入、下一天的收入，以及整个序列中的第一个和最后一个值。

这是时间序列分析、仪表板准备和识别趋势偏差的强大模板。

常见问题解答

LAG 和 LEAD 之间有什么区别？

LAG 向后查看并返回前一行的值，而 LEAD 向前查看并返回后一行的值。这两个函数在定义的窗口和排序顺序内操作。

为什么 LAST_VALUE 通常返回当前行？

因为结果取决于窗口框架。如果您保持默认框架，框架的最后一行可能是当前行。要获取整个分区的最后一个值，通常需要 ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING。

我可以在没有 PARTITION BY 的情况下使用 LAG 和 LEAD 吗？

可以。在这种情况下，函数在整个结果集上作为一个大分区工作。当您需要分析单个整体序列而不将其拆分为组时，这很有用。

面试问题

我什么时候应该使用 LAG，什么时候应该使用 LEAD？

当您需要将当前行与前一行进行比较时，使用 LAG，例如查找与先前付款的变化。使用 LEAD 当您需要向前查看时，例如获取下一个事件日期或下一个指标值。

FIRST_VALUE 与 MIN 有什么不同？

MIN 返回一组行中的最小值，而 FIRST_VALUE 返回根据指定排序的第一行的值。如果排序顺序与最小值不匹配，结果将会不同。

为什么 LAST_VALUE 通常需要显式框架？

因为 LAST_VALUE 并不意味着“无论如何都是分区的最后一行”。它意味着当前框架的最后一行。如果默认框架在当前行结束，函数将返回当前值。显式框架将窗口扩展到整个分区。

本课的关键要点：

  • LAG 和 LEAD 让您在不使用 self join 的情况下访问相邻行。
  • FIRST_VALUE 和 LAST_VALUE 返回窗口内的边缘值，而不仅仅是最小值或最大值。
  • 对于所有这些函数，ORDER BY 子句至关重要，因为它定义了行的顺序。
  • LAST_VALUE 通常需要显式框架 UNBOUNDED PRECEDING ... UNBOUNDED FOLLOWING。
  • 这些函数在序列分析、时间序列工作和行之间的变化检测中尤其有用。

在下一课中，我们将把窗口函数应用于运行总计和移动平均。

尝试解决以下任务，以巩固您在本课中学到的内容。

  1. 计算租赁之间的平均天数
  2. 识别恐怖电影爱好者
上一课
继续练习
下一课