SQL中如何统计用户最长连续活跃天数-SQL-PHP中文网

用窗口函数识别连续登录段的核心思路是将连续日期映射为同一组：对每个user_id按login_date排序后，用日期减去row_number()生成恒定差值（date_group），再按user_id和date_group分组统计天数并取最大值。

sql中如何统计用户最长连续活跃天数

用窗口函数识别连续登录段

核心思路是把连续日期变成同一组，再按组统计天数。关键在于：相邻日期的差值恒为1，而跨断点时差值会跳变。先对用户登录日期排序，再用 ROW_NUMBER() 生成序号，用日期减去序号——同一连续段内这个差值恒定，不同段之间差值不同。

常见错误是直接用 LAG() 比较前一天是否存在，但这样只能判断是否“隔天”，无法聚合出完整段长；还有人用自连接做日期范围扫描，数据量一大就超时。

必须对每个 user_id 单独排序，否则 ROW_NUMBER() 乱序导致分组错位
login_date 字段类型要是 DATE，不能是 DATETIME 或字符串，否则减法行为不一致
PostgreSQL 和 MySQL 8.0+ 支持标准写法；MySQL 5.7 需改用变量模拟窗口，稳定性差

构造连续段并计算长度

上一步得到的差值（常被称作 date_group）就是连续段标识符。接下来只需按 user_id 和 date_group 分组，用 COUNT(*) 算每段天数，再取最大值。

注意不是所有数据库都支持在同一个查询里嵌套两层窗口函数，稳妥做法是用子查询或 CTE 拆开。

WITH ranked AS (
  SELECT
    user_id,
    login_date,
    login_date - INTERVAL ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY login_date) DAY AS date_group
  FROM user_login
),
grouped AS (
  SELECT user_id, COUNT(*) AS streak_len
  FROM ranked
  GROUP BY user_id, date_group
)
SELECT user_id, MAX(streak_len) AS max_streak
FROM grouped
GROUP BY user_id;

处理重复登录和缺失日期

如果同一天有多次登录记录，COUNT(*) 会虚高；如果表里有脏数据（如未来日期、NULL），也会干扰排序和分组。

务必在最外层或 CTE 起始处加 DISTINCT ON (user_id, login_date)（PostgreSQL）或 GROUP BY user_id, DATE(login_date)（MySQL）去重
过滤掉无效日期：WHERE login_date IS NOT NULL AND login_date
不要依赖业务侧“保证每天最多一条”，实际表里常有补录、测试数据混入