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

核心思路是用登录日期减去ROW_NUMBER()生成恒定分组标识：按user_id分组、login_date升序排序后，连续日期与对应序号的差值相同，从而准确划分连续段；需先去重、统一为DATE类型，并注意数据库语法差异。

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

核心思路是把「连续日期」转化为「相同分组标识」：对用户按登录时间排序，再用登录日期减去 ROW_NUMBER()。只要这个差值相同，就属于同一段连续登录。

常见错误是直接用 LAG() 比较前一天——它只能判断相邻两天是否连续，无法合并多日；而日期减序号能天然聚合整段。

• 必须先按 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日）视为连续。这时不能依赖 DATE_SUB 简单相减，得用 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 (...) AS seg_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 字段，查询直接走索引

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

原始日志里 login_date 为空、或同一用户同一天多次登录未去重，会直接让连续天数计算结果翻倍或报错。

• 务必在子查询最外层加 WHERE login_date IS NOT NULL，否则 ROW_NUMBER() 可能跳序
• 用 DISTINCT ON (user_id, DATE(login_time))（PostgreSQL）或 GROUP BY user_id, DATE(login_time) 去重，不能只靠 SELECT DISTINCT —— 它不保证日期归一
• 某些埋点 SDK 会发重复事件，建议清洗阶段就加 event_id 去重，而不是在指标层硬扛

连续登录这类指标看着简单，实际卡点全在数据质量、边界 case 和执行计划上。跑通一次不难，稳定跑准三个月才见真章。