MySQL LAG 怎样比较相邻日期记录

要比较同一用户或同一订单下相邻两条日期记录，最小写法是先用 LAG() 取上一条日期，再用 DATEDIFF() 计算间隔。关键不在于把函数写得复杂，而是先固定分区和排序：PARTITION BY 决定“在哪一组里找上一条”，ORDER BY 决定“哪一条算上一条”。

官方文档：https://dev.mysql.com/doc/refman/8.4/en/window-function-descriptions.html

如果只想比较相邻日期，通常使用 LAG(date_column, 1, NULL)。分区首行没有上一条记录，会得到 NULL；因此日期差也应保留为 NULL，不要把首行硬算成某个业务天数。

• 先用业务键划分比较范围，再按日期和真正唯一的记录键排序。
• N 控制向前取几行，省略时默认为 1；N=0 取当前行。
• default 只处理越过分区边界的缺失行，不能冒充真实历史日期。

先固定相邻记录的排序和比较范围

假设一张记录表包含 customer_id、record_date 和 record_id。如果直接按日期比较全表，A 用户的上一条记录可能被 B 用户“接上”；如果日期相同又没有唯一键，数据库也没有足够信息表达同一天内的确定顺序。

所以窗口定义至少要回答两个问题：比较范围是哪个业务对象，排序键是否能唯一确定记录顺序。常见写法如下：

-- 窗口按客户分组，并用日期和主键确定上一条记录
SELECT
  customer_id,
  record_date,
  record_id,
  LAG(record_date, 1, NULL) OVER (
    PARTITION BY customer_id
    ORDER BY record_date, record_id
  ) AS previous_date
FROM customer_records
ORDER BY customer_id, record_date, record_id;

这里的 1 是向前偏移一行，NULL 是找不到上一行时的默认值。窗口里的排序只服务于 LAG 的取值关系，查询最后的 ORDER BY 则负责让最终结果按客户和日期清楚展示；两者最好都写出来。

用 LAG 的偏移量取上一条或前两条

MySQL 8.4 文档把 LAG(expr[, N[, default]]) 定义为：在当前分区内取当前行之前第 N 行的表达式值。省略 N 时是 1，省略 default 时是 NULL；当 N=0 时，表达式取当前行本身。

-- 同时保留上一条和上两条日期，方便比较不同跨度
SELECT
  customer_id,
  record_date,
  LAG(record_date, 1, NULL) OVER w AS previous_date,
  LAG(record_date, 2, NULL) OVER w AS two_rows_before_date
FROM customer_records
WINDOW w AS (
  PARTITION BY customer_id
  ORDER BY record_date, record_id
);

在每个客户的第一条记录上，上一条和上两条都会因为越过分区起点而返回 NULL；第二条记录只有上两条为空。这种结果表达的是“历史记录不存在”，比随意填一个日期更容易被后续统计正确处理。

偏移量不是日期间隔。LAG(..., 2) 表示向前两行，不表示向前两天；如果中间缺少某个自然日，它仍然只会取排序结果中的第二条记录。

用 default 处理首行，但别伪造历史日期

有些报表希望首行显示一个业务标记，这时可以显式给出 default。但默认值会改变字段含义：它表示“没有对应上一行时使用的回退值”，不是从表中找到的日期。

-- 用 NULL 保留缺失语义；需要标记时也可以返回固定文本
SELECT
  customer_id,
  record_date,
  LAG(record_date, 1, NULL) OVER (
    PARTITION BY customer_id
    ORDER BY record_date, record_id
  ) AS previous_date,
  CASE
    WHEN LAG(record_date, 1, NULL) OVER (
      PARTITION BY customer_id
      ORDER BY record_date, record_id
    ) IS NULL THEN '首条记录'
    ELSE '存在上一条记录'
  END AS previous_state
FROM customer_records;

如果把默认值设成某个很早的日期，再直接做日期减法，首行会得到一个看似合理但没有业务依据的巨大间隔。更稳妥的做法是保留 NULL，或额外输出状态字段，让调用方明确区分“真实上一日期”和“没有上一行”。

把 LAG 结果放到外层，再计算日期差

为了让查询更容易排查，我会先在公共表表达式中生成 previous_date，再在外层计算 DATEDIFF(record_date, previous_date)。这样可以单独查看上一条日期，避免把窗口取值和日期运算混在一段表达式里。

-- 先取上一条日期，再在外层计算相邻日期相差天数
WITH dated AS (
  SELECT
    customer_id,
    record_date,
    record_id,
    LAG(record_date, 1, NULL) OVER (
      PARTITION BY customer_id
      ORDER BY record_date, record_id
    ) AS previous_date
  FROM customer_records
)
SELECT
  customer_id,
  record_date,
  previous_date,
  DATEDIFF(record_date, previous_date) AS days_since_previous
FROM dated
ORDER BY customer_id, record_date, record_id;

当 previous_date 是 NULL 时，DATEDIFF 的结果也应按缺失处理；这正好保留了分区首行的语义。若业务要求只看有上一条记录的行，可以在外层增加 WHERE previous_date IS NOT NULL，不要在窗口计算前就删掉首行或改变参与比较的行集。

按需求选择偏移量和默认值

LAG 的 N 可以传负数吗？

不可以。MySQL 文档要求 N 是非负整数；需要取后续记录时应考虑 LEAD，不要用负数伪装成反向偏移。

为什么 LAG 比较出来的“上一条”不稳定？

通常是窗口 ORDER BY 不能唯一确定顺序。日期相同的记录应补上真正唯一的键；但唯一键只稳定同一批输入中的顺序，不能阻止新增数据改变整体相邻关系。

能不能把 LAG 的 default 设成上一天？

可以写出固定默认值，但它表达的是缺失行时的回退值，不是表中真实存在的上一条日期。若要区分首行，应保留 NULL 或单独增加状态字段。

总结一下：比较相邻日期时，先用 PARTITION BY 划定对象，再用日期和唯一键排序；用 LAG(..., 1, NULL) 取上一条日期；最后在外层用 DATEDIFF 计算间隔。偏移量决定取前几行，default 只负责越过分区边界时的缺失语义，不能替代真实历史数据。