如何使用SQL计算连续登录天数等复杂行为指标？

连续登录天数这个需求，在数据分析或用户行为画像里几乎是个必考题目。乍一看很简单——把用户每天的登录记录拉出来，数一数连续出现了几天就行。但真上手写 SQL，就发现各种边界问题：同一天多次登录怎么算？跨月跨年怎么办？数据缺失了还要不要连？下面就把这套逻辑掰开揉碎，从原理到实战、从正确到高效，一次讲透。

用 ROW_NUMBER() 和日期差算连续登录天数

核心思路其实就一句话：把「连续日期」转化成「相同的分组标识」。具体怎么做？对每个用户按登录时间排序，再拿登录日期减去排名序号 ROW_NUMBER()——差值相同的记录，就属于同一段连续登录。

为什么不用 LAG() 比较前一天？LAG() 只能告诉你相邻两天是否连续，但没法自动把多段连续的日子合并成一个组。而日期减序号的方案天然做到了这一点：连续的日子里，日期每天加1，序号也每天加1，差值恒定；一旦中断，差值就变了。

实施时有几个关键点，稍不注意就容易翻车：

• 排序必须严格按 user_id 分组、login_date 升序，否则 ROW_NUMBER() 乱序会导致差值完全无意义。
• 日期类型必须统一。如果原字段是 DATETIME，记得用 DATE(login_time) 截断时分秒——否则同一天多次登录会生成多个不同的日期，连续判断直接崩溃。
• 各数据库的兼容性也要留意：PostgreSQL、MySQL 8.0+、SQL Server 都支持窗口函数，但 SQLite 不支持，得换用其他方法（比如游标或自连接）。

SELECT user_id,       MIN(login_date) AS start_date,       MAX(login_date) AS end_date,       COUNT(*) AS daysFROM (  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 login_log) tGROUP BY user_id, grp;

处理数据缺失和跨月连续性问题

真实场景里，用户日志总有缺漏——比如某天没打点，但业务上仍希望把「逻辑连续」（例如1月31日和2月1日）视为连续。这时单纯的日期减序号方案就失效了，需要改用 LAG() 计算实际间隔天数，再人工标记断点。

容易忽略的坑是时区和跨年场景。比如 2023-12-31 和 2024-01-01，日历上差1天，但如果数据库时区配置不同，可能被截成不同日期，导致误判。稳妥的做法是：

• 用 DATEDIFF(login_date, LAG(login_date) OVER (...)) != 1 判断是否断连，比硬减更鲁棒。
• 对每个断点设一个累计标志，例如 SUM(CASE WHEN gap > 1 THEN 1 ELSE 0 END) OVER (...) 生成分段 ID，再按 seg_id 分组统计。
• 注意数据库函数差异：MySQL 的 DATEDIFF 返回整数天差；PostgreSQL 则要用 login_date - LAG(login_date) OVER (...) 再比较 INTERVAL '1 day'。

性能瓶颈在哪？怎么加速？

当用户量上千万、日志表过亿行时，窗口函数 ROW_NUMBER() 和 LAG() 会涉及全表扫描加排序，成为主要瓶颈。这时候，优化 SQL 语句本身的作用有限，索引和分区策略才是关键。

• 必须在 (user_id, login_date) 上建联合索引，否则排序成本直接爆炸。
• 按月分区效果明显。比如 login_log_202401 这样的分区，查询最近30天只需要扫1-2个分区，完全避开历史冷数据。
• 如果业务只关心「当前连续多少天」，更聪明的做法是用物化视图或定时任务预计算：直接记录每个用户的 last_login_date 和 continuity_days 字段，查询时走索引就能秒出结果。

别忘了空值和重复数据的清洗

原始日志里的脏数据是连续指标的天敌。登录日期为空、同一用户同一天多次登录未去重，都会让计算结果翻倍甚至报错。

• 子查询最外层务必加 WHERE login_date IS NOT NULL，否则 ROW_NUMBER() 可能因为空值导致序号跳变，分组错误。
• 去重不能只靠 SELECT DISTINCT——它不保证日期归一化。正确做法是：PostgreSQL 用 DISTINCT ON (user_id, DATE(login_time))，MySQL 用 GROUP BY user_id, DATE(login_time)。
• 有些埋点 SDK 会重复发送事件，建议在数据清洗阶段就基于 event_id 做去重，不要把脏活留给指标计算层。

连续登录这类指标，公式写出来就那么几行，但跑通一次不难，稳定跑准三个月才见真章。真正考验人的，往往不是 SQL 本身的语法，而是对数据质量、边界条件以及执行计划的理解。