SQL 如何高效计算“连续活跃天数”或“连续登录”

用ROW_NUMBER()配合日期差分组的核心是将连续日期映射为同一组号：按user_id和login_date排序后，用login_date减去行号，结果相同即属同一连续段；需注意分区、排序字段、数据库兼容性及索引优化。

用 ROW_NUMBER() 配合日期差做分组

核心思路是把连续日期映射成同一组号：对用户按登录日期排序，用 login_date 减去 ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY login_date)，相同结果即为同一连续段。

注意必须用日期类型直接相减（如 postgresql/mysql 8.0+ 支持 DATE - INTERVAL '1 day' * rn，SQL Server 用 DATEADD(day, -rn, login_date)），不能转字符串或时间戳再算，否则会丢失精度或报错。

常见错误是漏掉 PARTITION BY user_id，导致全表混排，结果完全错乱；或者 ORDER BY 用了 login_time 而非 login_date，同一天多次登录会被拆成多段。

MySQL 5.7 没有窗口函数怎么办

得用变量模拟 ROW_NUMBER()，但要注意执行顺序不可靠——必须用子查询先按 user_id, login_date 排序，再在外层赋值。

SELECT user_id, login_date,        @rn := IF(@prev = user_id, @rn + 1, 1) AS rn,        @prev := user_id FROM (SELECT user_id, DATE(login_time) AS login_date       FROM logins       ORDER BY user_id, login_date) t,      (SELECT @rn := 0, @prev := '') init

变量方式在 JOIN 或子查询嵌套过深时容易失效；另外 DATE(login_time) 必须提前归一化，否则凌晨跨天日志可能被误判为不连续。

如何查“当前连续活跃了多少天”

不是找历史最长，而是算截止今天还在延续的那段。关键在于过滤出“最后登录日 = 今天”的用户，再反向推最近断点。

推荐写法：先取每个用户最大登录日 MAX(login_date)，再左连一张“前一天是否存在登录”的表（用 DATE_SUB(max_date, INTERVAL 1 DAY) 去关联），直到某天没匹配上为止。

更稳的做法是用递归 CTE（PostgreSQL/SQL Server）或自连接限制深度（比如最多查 30 天），避免全表扫描。别直接 WHERE login_date >= DATE_SUB(CURDATE(), INTERVAL 30 DAY) 就 group by —— 这只能保证最近 30 天有记录，不能证明中间没断。

性能瓶颈通常卡在哪

连续计算本质是 O(n²) 可能性操作，尤其在没索引或数据倾斜时：

• user_id + login_date 必须建联合索引，单列索引无效
• 如果表里有千万级登录记录，但只关心近 7 天，务必加 WHERE login_date >= ... 提前过滤，别让窗口函数扫全表
• oracle 的 LAG() 在大偏移量下比 ROW_NUMBER() 更慢，优先选后者

真正上线时，连续天数字段建议冗余到用户维表里异步更新，而不是每次实时算——毕竟“连续”是个状态，不是瞬时快照。