SQL窗口函数统计连续登录天数的方法

连续登录天数通过“登录日期减去行号”构造恒定分组标识，相同差值即为连续段；需用DATEDIFF等函数适配数据库差异；筛选至少连续3天用户应在外层按user_id聚合取最大值判断。

连续登录天数怎么算？先理解窗口函数的核心逻辑

说穿了，连续登录天数就是一个很经典的问题：按用户把登录日期排好序，找出那些日期正好差1天的连续片段。窗口函数不是直接给你答案，而是帮你搭好“脚手架”——比如用 ROW_NUMBER() 生成行号，再用登录日期减去这个行号，算出来的结果如果是一样的，那就说明这些日期是连续的。

关键就在这一步：日期本身不能直接拿来分组判断，但 login_date - ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY login_date) 这个差值在连续登录时是恒定的。这就是解题的锚点，漏掉它，你很容易掉进自连接或递归CTE的坑里。

为什么用 DATEDIFF 而不是直接减？注意数据库差异

不同数据库处理日期相减的方式差得挺远的。MySQL 里可以用 login_date - INTERVAL ROW_NUMBER()... DAY，但更通用也更安全的做法是用 DATEDIFF（SQL Server、MySQL）或 DATE_PART（PostgreSQL）。如果直接写 login_date - rn，在 PostgreSQL 里会直接报错，因为日期不能和整数直接相减。

• MySQL：DATEDIFF(login_date, '1970-01-01') - ROW_NUMBER()... 或者 login_date - INTERVAL ROW_NUMBER() OVER (...) DAY
• PostgreSQL：必须用 (login_date - ROW_NUMBER() OVER (...) * INTERVAL '1 day')::date 或 login_date - make_interval(days := rn)
• SQL Server：推荐 DATEADD(day, -ROW_NUMBER() OVER (...), login_date)

不统一处理，本地跑通、上线报错的事并不少见，尤其是跨团队协作的时候。建议封装成 CTE 或视图，把底层差异屏蔽掉。

如何过滤出「至少连续3天」的用户？GROUP BY 后加 HA VING 是错的

不少同学写完分组后直接 GROUP BY user_id, grp_id HA VING COUNT(*) >= 3，结果确实拿到了每个连续段，但需求往往只问“用户是否曾经连续登录过至少3天”，而不是把用户所有达标段都列出来。这时候需要在外层再做一次聚合。

正确的做法是先算每个用户的最大连续天数，再筛选：

SELECT user_id
FROM (
  SELECT user_id, 
         COUNT(*) AS consecutive_days,
         MAX(COUNT(*)) OVER (PARTITION BY user_id) AS max_consecutive
  FROM (
    SELECT user_id, 
           login_date,
           login_date - INTERVAL ROW_NUMBER() OVER (
             PARTITION BY user_id ORDER BY login_date
           ) DAY AS grp_id
    FROM login_log
  ) t
  GROUP BY user_id, grp_id
) t2
GROUP BY user_id
HA VING MAX(consecutive_days) >= 3;

注意：内层 GROUP BY 必须包含 user_id 和 grp_id，否则窗口函数生成的分组会被打散；外层 HA VING 才能真正按用户维度判断。

性能陷阱：没有索引时，ROW_NUMBER() 会全表排序

ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY login_date) 看起来很简单，但如果表上没有 (user_id, login_date) 的联合索引，数据库就得先对几百万行做排序，IO 和内存开销一下子就上来了。

实战中要注意几点：

• 确认执行计划里 ORDER BY 是否走了索引扫描，而不是文件排序（Using filesort 或 Sort 操作）
• 如果登录记录带时间戳（比如 login_time），务必用 DATE(login_time) 来算，但索引要建在生成列或者提前物化出日期字段上，否则索引就废了
• 面对千万级的超大表，建议先用 WHERE login_date >= DATE_SUB(CURDATE(), INTERVAL 30 DAY) 缩小范围，再算连续性——毕竟“历史连续30天”和“最近连续3天”是两码事

连续登录统计逻辑看起来清晰，真正卡住人的永远是索引缺失、日期类型隐式转换、以及跨数据库语法兼容性——这些地方不动手跑一遍，光看理论很容易以为自己懂了。