`LAG`、`LEAD`、`FIRST_VALUE` 和 `LAST_VALUE`

Q: LAG 和 LEAD 有什么区别？

LAG 向前看，返回前面某一行的值；LEAD 向后看，返回后面某一行的值。两个函数都在定义的窗口和排序规则内工作。

Q: 为什么 LAST_VALUE 经常返回当前行？

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

第 7.4 课 · 阅读时间：约 9 分钟 · 练习题 ↓

本课介绍窗口函数 LAG、LEAD、FIRST_VALUE 和 LAST_VALUE。你将学习如何不用 JOIN 就取到前一个和后一个值，如何获取窗口中的第一个和最后一个值，以及为什么 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;

那么在许多 DBMS 中，结果并不是整个分区的最后一个值，而是当前框架末尾的值。很多时候，这就是当前行本身。

获取分区最后一个值的正确写法

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。
这些函数在序列分析、时间序列处理和发现行间变化方面特别有用。

LAG、LEAD、FIRST_VALUE 和 LAST_VALUE

这些函数的作用

基本语法

LAG 和 LEAD

FIRST_VALUE 和 LAST_VALUE

使用 LAG