SQL分组统计状态变更次数的方法

研究状态变化统计这个问题，其实核心就是一句话：用LAG()拿到上一行的状态，跟当前行比，变了就计1。听起来简单，但真正落地时，排序、分组、边界处理，每一个细节都可能让你翻车。

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

核心思路就是把当前行和上一行的状态做对比，只有发生变化时才计为1次。MySQL 8.0+、PostgreSQL、SQL Server 都原生支持LAG()窗口函数，这也是最直接、最推荐的做法。

但很多人一上来就想用GROUP BY加COUNT(*)，然后发现统计的是总记录数，跟“变化”完全不搭边——这显然是走错了方向。

要正确实现，有几个关键点必须盯住：

• 排序必须严格按业务逻辑来（比如时间戳created_at），否则LAG()拿到的“上一行”大概率是错的
• 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找紧邻的前一条，涉及大量子查询，数据量稍大就卡死
• 如果硬要用变量，必须保证查询不带任何优化干扰（比如关闭严格模式、不能有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，而是确认业务里“变化”的定义是否清晰、数据里有没有脏值、排序依据是否绝对可靠——这些往往比语法本身更耗时间，也更容易被忽视。