MySQL LAG 检测分组数据中的连续变化点

我在整理设备状态流水时，最容易写错的地方不是比较条件，而是“上一条”到底属于谁。把整张表直接按时间排序再比较，会把不同设备的记录串在一起；正确做法是让窗口先按设备分组，再在每组内部按记录时间排序，最后用 LAG() 取上一行。

官方地址：https://dev.mysql.com/doc/refman/8.4/en/

下面的例子只标记每个设备状态发生切换的记录。窗口函数负责产生上一值，外层查询负责筛选变化点。这样首行、NULL 和并列时间都有明确的处理位置。

先把每组的上一行取出来

假设有一张状态表：

CREATE TABLE device_status (
    id BIGINT PRIMARY KEY,
    device_id BIGINT NOT NULL,
    recorded_at DATETIME NOT NULL,
    status VARCHAR(20) NULL
);

-- 示例数据按设备和时间组织，id 用来解决同一时刻的并列记录
INSERT INTO device_status (id, device_id, recorded_at, status) VALUES
    (1, 101, '2026-10-10 09:00:00', 'idle'),
    (2, 101, '2026-10-10 09:05:00', 'run'),
    (3, 101, '2026-10-10 09:10:00', 'run'),
    (4, 202, '2026-10-10 09:01:00', 'idle'),
    (5, 202, '2026-10-10 09:08:00', 'alarm');

最小窗口写法如下：

SELECT
    device_id,
    recorded_at,
    status,
    -- 每台设备独立取上一条状态，第一条没有上一值时返回 NULL
    LAG(status) OVER (
        PARTITION BY device_id
        ORDER BY recorded_at, id
    ) AS prev_status
FROM device_status;

PARTITION BY device_id 把数据拆成互不串行的设备分区，ORDER BY recorded_at, id 决定每个分区中的先后关系。这里加上主键 id 是为了让同一时间的多条记录也有稳定顺序；如果业务没有可比较的第二排序键，就不能把“上一条”解释成确定的业务事实。

把上一值变成变化点标记

有了 prev_status，就可以把“本组第一条”与“状态发生变化”分开表达。第一条记录没有上一值，通常应该单独标记为组起点；状态从 idle 变成 run，或者从 run 变成 alarm，才是连续序列中的变化点。

WITH ordered_status AS (
    SELECT
        id,
        device_id,
        recorded_at,
        status,
        -- 先保留上一行，外层再决定 NULL 的业务含义
        LAG(status) OVER (
            PARTITION BY device_id
            ORDER BY recorded_at, id
        ) AS prev_status
    FROM device_status
), marked_status AS (
    SELECT
        id,
        device_id,
        recorded_at,
        status,
        prev_status,
        -- 第一行是组起点；其余行只在状态不同于上一行时标记
        CASE
            WHEN prev_status IS NULL THEN 1
            WHEN NOT (status  prev_status) THEN 1
            ELSE 0
        END AS changed
    FROM ordered_status
)
SELECT
    device_id,
    recorded_at,
    prev_status,
    status
FROM marked_status
WHERE changed = 1
ORDER BY device_id, recorded_at, id;

这里使用 MySQL 的 NULL 安全比较运算符 ：两个值都为 NULL 时视为相等，一个为 NULL、另一个非 NULL 时视为不同。外层查询再筛选 changed = 1，因为窗口函数不能直接写在同一层的 WHERE 条件中。

首行、NULL 和排序要怎么取舍

第一行是否算变化点取决于报表含义。如果只关心“从一个状态切到另一个状态”，可以把首行排除：

-- 只保留存在上一状态且确实发生切换的记录
SELECT device_id, recorded_at, prev_status, status
FROM marked_status
WHERE prev_status IS NOT NULL
  AND changed = 1;

如果业务把“未知变成正常”也算一次变化，就不能简单依靠普通的 。普通比较遇到 NULL 会得到未知结果，应该继续使用 ，或按业务把缺失值先映射成明确的哨兵状态。

另外，LAG(status, 2, 'unknown') 可以取前两行并设置默认值，但偏移量应是非负整数；多数连续变化检测只需要默认的前一行。窗口里的排序只决定分区内部的相邻关系，最终展示顺序仍应在最外层 ORDER BY 中明确写出。

数据量变大时先看排序成本

LAG() 解决的是相邻记录的表达，不会自动让任意查询变快。实际查询应先缩小时间范围，再让排序键尽量贴合分组和时间条件。可以从这个方向检查索引：

-- 索引顺序要结合过滤条件和数据分布，用 EXPLAIN 判断实际计划
CREATE INDEX idx_device_status_device_time
    ON device_status (device_id, recorded_at, id);

-- 先限制设备和时间范围，再计算窗口结果
WITH ordered_status AS (
    SELECT
        id, device_id, recorded_at, status,
        -- 保持与窗口定义一致的稳定排序
        LAG(status) OVER (
            PARTITION BY device_id
            ORDER BY recorded_at, id
        ) AS prev_status
    FROM device_status
    WHERE device_id IN (101, 202)
      AND recorded_at >= '2026-10-10 00:00:00'
)
SELECT *
FROM ordered_status
ORDER BY device_id, recorded_at, id;

不要因为看到窗口函数就直接添加索引。若过滤条件、分组字段和排序字段不同，索引可能只帮助过滤，排序仍需要额外代价；用 EXPLAIN 检查扫描范围、排序和临时表，再决定是否调整。

几个容易混淆的问题

LAG 和 LEAD 有什么区别？ LAG 看当前行之前的记录，LEAD 看之后的记录；前者适合找状态开始变化的位置，后者适合观察下一次值或计算向前的差异。

能不能把 LAG 写到 WHERE 里？ 通常不能在同一查询层直接这样做。先在 CTE 或派生表中产生 prev_status、changed，再由外层筛选。

为什么同一时间的上一行不稳定？ 因为时间列相同的记录属于同一排序键，数据库没有被要求选择哪一条在前。补充唯一且符合业务顺序的字段，例如自增主键或事件序号。

总结

• 用 PARTITION BY 保证不同对象之间不互相比较。
• 用稳定的 ORDER BY 定义“上一条”的业务顺序。
• 先用 LAG() 生成上一值，再在外层处理首行、NULL 和变化点筛选。