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

林译川

2026-09-12 • 👁 0人浏览过

窗口函数识别连续区间本质是用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 以下不支持窗口函数，只能用变量模拟，稳定性差，慎用于生产跨多列判断连续（如用户+日期联合连续）实际场景常需“某用户在某设备上连续操作”，这时分组维度变多，锚点需结合多字段构造： MySQL（Linux） MySQL 9.6.0是面向Linux平台的2026年创新版本，核心架构迎来重大革新。其将外键约束与级联操作从InnoDB引擎层上移至SQL层，确保所有数据变更均被完整记录至Binlog，彻底解决了CDC（变更数据捕获）与主从复制中的数据不一致难题。此外，该版本引入container_aware启动选项以原生适配容器环境，并对审计日志进行了组件化重构，为追求极致数据一致性与云原生体验的开发者提供了全新选择。下载例如统计每个用户自己的连续登录段：

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 之前厘清，否则窗口函数再准也没用。

相关文章