SQL中如何统计每个分组里状态改变的次数？

核心思路是用LAG()获取上一行状态并与当前行比较，仅在变化时标记为1；必须显式按业务顺序（如created_at）排序并正确使用PARTITION BY分组，首行NULL需妥善处理。

用LAG()识别相邻行状态变化

核心思路是把当前行和上一行的状态做对比，只在变化时计1。MySQL 8.0+、PostgreSQL、SQL Server 都支持 LAG() 窗口函数，这是最直接的方式。

常见错误是直接用 GROUP BY + COUNT(*)，那只能统计总记录数，完全无法捕捉“变化”这个动态行为。

• 必须按业务逻辑顺序排序（比如时间戳 created_at），否则 LAG() 拿到的“上一行”毫无意义
• LAG(status) OVER (PARTITION BY group_id ORDER BY created_at) 中的 PARTITION BY 要和你要统计的分组字段一致
• 第一行的 LAG() 返回 NULL，比较时要用 IS DISTINCT FROM（PostgreSQL）或显式处理 NULL（如 COALESCE(prev_status, '') != COALESCE(status, '')）

SELECT group_id,       COUNT(*) AS status_change_countFROM (  SELECT group_id,         status,         LAG(status) OVER (PARTITION BY group_id ORDER BY created_at) AS prev_status  FROM events) tWHERE status IS DISTINCT FROM prev_statusGROUP BY group_id;

兼容旧版 MySQL（5.7 及更早）的替代方案

没有窗口函数时，得靠自连接或变量模拟“上一行”。自连接写法清晰但性能差；用户变量看似简洁，但 MySQL 官方明确警告：@var := 在 ORDER BY 和赋值顺序间存在不确定性，极易出错。

• 自连接要加 ON t1.group_id = t2.group_id AND t1.created_at > t2.created_at，再用 NOT EXISTS 找紧邻前一条，非常慢
• 如果硬要用变量，必须确保查询不带任何优化干扰（如关闭 sql_mode 中的严格模式，且不能有 JOIN 或子查询嵌套）
• 更稳妥的做法是导出数据到 Python/Shell，用脚本按 group_id 分组后逐行比对——尤其当数据量不大或只需离线跑一次时

状态变化定义不明确时的陷阱

“状态改变”到底指什么？业务上常有歧义：

• A → B → A 算两次变化，还是回到原状就不算？多数场景要算两次
• A → NULL → B 是否算变化？取决于业务是否把 NULL 视为有效状态
• 连续相同状态（A → A → A）必须过滤掉，但容易因排序字段重复导致 LAG() 拿到错误的“上一行”

建议在 ORDER BY 子句中加入唯一字段兜底，例如：ORDER BY created_at, id，避免时间相同时序混乱。

性能敏感场景下的优化要点

当表有千万级记录且分组多时，LAG() 查询可能变慢，关键在索引：

• 复合索引必须覆盖 (group_id, created_at)，且顺序不能颠倒
• 如果只查特定几个 group_id，加 WHERE group_id IN (...) 能大幅减少扫描范围
• 避免在 LAG() 的 ORDER BY 表达式里用函数（如 DATE(created_at)），会破坏索引使用

真正难的不是写出 SQL，而是确认“变化”在业务里究竟怎么定义、数据里有没有脏值、排序依据是否绝对可靠——这些往往比语法本身更耗时间。