SQL 如何统计连续行为区间？

窗口函数识别连续区间本质是用ROW_NUMBER()与有序字段作差生成锚点，使同段记录锚点相同；日期用date_SUB(date, intERVAL rn DAY)，整数直接seq_id-rn；需注意ORDER BY、PARTITION BY、去重、索引及业务定义。

用窗口函数识别连续日期或序号段

连续行为区间本质是把相邻的、差值固定的记录聚成一组，比如用户连续登录的日期、订单连续的 order_id。核心思路是：对有序数据生成一个“锚点”，让同一连续段内所有行的锚点值相同。

最常用做法是用 ROW_NUMBER() 配合分组字段做差值。例如按日期统计连续登录：

SELECT    MIN(login_date) AS start_date,   MAX(login_date) AS end_date,   COUNT(*) AS days FROM (   SELECT      login_date,     DATE_SUB(login_date, INTERVAL ROW_NUMBER() OVER (ORDER BY login_date) DAY) AS grp   FROM user_login   WHERE user_id = 123 ) t GROUP BY grp;

这里 grp 就是锚点：对连续日期，ROW_NUMBER() 和真实日期的差是恒定的；一旦断开，差值跳变，新组就产生了。

注意点：

• ORDER BY 必须严格对应连续依据（如 login_date 或 event_time），否则锚点错乱
• 若原始字段是 timestamp，先用 DATE() 截断，避免时分秒干扰
• postgresql 用 login_date - ROW_NUMBER() OVER (...)::INT，语法略有不同

处理非日期型连续序号（如 ID、版本号）

当连续依据是整数型字段（如 version_no、seq_id），逻辑一样，但差值计算更直接：

SELECT    MIN(seq_id) AS start_id,   MAX(seq_id) AS end_id,   COUNT(*) AS length FROM (   SELECT      seq_id,     seq_id - ROW_NUMBER() OVER (ORDER BY seq_id) AS grp   FROM event_log   WHERE service = 'payment' ) t GROUP BY grp;

关键在 seq_id - ROW_NUMBER()：连续整数减去连续序号，结果恒定；中间缺一个数，差值就+1，自动分组。

常见陷阱：

• 字段含重复值？ROW_NUMBER() 仍递增，但重复会导致“伪断连”——此时应改用 DENSE_RANK() 或先 DISTINCT
• 起始值不为 1？不影响，差值偏移量一致即可
• mysql 8.0 以下不支持窗口函数，只能用变量模拟，稳定性差，慎用于生产

跨多列判断连续（如用户+日期联合连续）

实际场景常需“某用户在某设备上连续操作”，这时分组维度变多，锚点需结合多字段构造：

例如统计每个用户自己的连续登录段：

SELECT    user_id,   MIN(login_date) AS start_date,   MAX(login_date) AS end_date FROM (   SELECT      user_id,     login_date,     DATE_SUB(login_date, INTERVAL ROW_NUMBER() OVER (       PARTITION BY user_id ORDER BY login_date     ) DAY) AS grp   FROM user_login ) t GROUP BY user_id, grp;

重点是 PARTITION BY user_id：确保每个用户的 ROW_NUMBER() 独立计数，互不干扰。

容易忽略的细节：

• PARTITION BY 字段必须和业务分组强一致，漏写或写错会导致跨用户混组
• 若还需按设备细分，就把 PARTITION BY user_id, device_id 写全
• MySQL 5.7 不支持 PARTITION BY + 窗口函数，得用自连接或存储过程硬算，性能极差

性能与边界情况提醒

连续区间统计看起来简单，但在大数据量下极易慢得离谱，尤其当没索引或排序字段无索引时。

必须检查：

• ORDER BY 字段是否有索引？没有就加：CREATE INDEX idx_login_user_date ON user_login(user_id, login_date)
• 是否误把 WHERE 条件放在子查询外？会导致全表扫描后再过滤，应尽量下推到内层
• NULL 值存在吗？ROW_NUMBER() 会跳过 NULL 行，若字段允许 NULL，先 WHERE col IS NOT NULL
• 时间范围极大（如十年日志）？考虑按年/月分区后分别计算再合并，避免单次扫描过大

真正难的不是写出语句，而是确认“连续”的定义是否被业务方准确传达——比如“隔天登录算不算连续”“节假日是否排除”，这些逻辑必须在 SQL 之前厘清，否则窗口函数再准也没用。