MySQL LAG和LEAD函数用法详解

在 MySQL 中，LAG() 和 LEAD() 是窗口函数（Window Functions），用于访问结果集中当前行之前或之后的行中的数据。它们常用于计算差值、趋势分析或时间序列比较。

---

1. LAG() 函数

作用：访问当前行之前的指定偏移量（offset）处的数据。
语法：

LAG(column, offset, default_value) OVER (
    [PARTITION BY partition_expression]
    ORDER BY sort_expression [ASC|DESC]
)

• column：要获取值的列名。
• offset（可选）：向前回溯的行数（默认 1）。
• default_value（可选）：当没有前一行时返回的值（默认 NULL）。
• PARTITION BY（可选）：将数据分区，函数在分区内独立计算。
• ORDER BY：定义分区内行的排序顺序。

示例：获取前一天的销售额

SELECT 
    sale_date,
    amount,
    LAG(amount, 1, 0) OVER (ORDER BY sale_date) AS prev_day_amount
FROM sales;

结果：

sale_date	amount	prev_day_amount
2023-01-01	100	0
2023-01-02	200	100
2023-01-03	150	200

---

2. LEAD() 函数

作用：访问当前行之后的指定偏移量处的数据。
语法：

LEAD(column, offset, default_value) OVER (
    [PARTITION BY partition_expression]
    ORDER BY sort_expression [ASC|DESC]
)

参数含义与 LAG() 相同，方向相反。

示例：获取后一天的销售额

SELECT 
    sale_date,
    amount,
    LEAD(amount, 1, 0) OVER (ORDER BY sale_date) AS next_day_amount
FROM sales;

结果：

sale_date	amount	next_day_amount
2023-01-01	100	200
2023-01-02	200	150
2023-01-03	150	0

---

3. 分区使用示例（PARTITION BY）

按产品分区，分别计算每个产品的前后订单金额：

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

结果：

product_id	order_date	amount	prev_amount	next_amount
A	2023-01-01	100	NULL	200
A	2023-01-02	200	100	150
A	2023-01-03	150	200	NULL
B	2023-01-01	50	NULL	80
B	2023-01-02	80	50	NULL

---

4. 实际应用场景

计算日环比变化

SELECT 
    sale_date,
    amount,
    LAG(amount, 1, amount) OVER (ORDER BY sale_date) AS prev_amount,
    (amount - LAG(amount, 1, amount) OVER (ORDER BY sale_date)) AS diff
FROM sales;

检测数据间断（如缺失日期）

SELECT 
    date,
    LEAD(date) OVER (ORDER BY date) AS next_date,
    DATEDIFF(LEAD(date) OVER (ORDER BY date), date) AS day_gap
FROM events
HAVING day_gap > 1;  -- 查找间隔大于1天的日期

---

注意事项

1. MySQL 版本要求：LAG() 和 LEAD() 需 MySQL 8.0+（早期版本不支持）。
2. 必须搭配 OVER() 子句：定义窗口分区和排序。
3. 默认值 default_value 可省略，此时无前/后行时返回 NULL。
4. 性能：对大数据集建议在 ORDER BY 列上建立索引。

通过灵活使用偏移量和分区，可以高效实现跨行数据分析需求。