如何通过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 后加 HAVING 是错的

很多人写完分组后直接 GROUP BY user_id, grp_id HAVING COUNT(*) >= 3，结果把每个连续段都返回了，但需求往往只要「用户是否满足过连续3天」，而不是列出所有达标段。这时候需要外层再聚合。

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

SELECT user_idFROM (  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) t2GROUP BY user_idHAVING MAX(consecutive_days) >= 3;

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

性能陷阱：没有索引时，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天」是两个问题

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