SQL中怎样统计每个用户连续登录天数并进行分组-SQL-PHP中文网

连续登录通过“日期 - 排序号”恒定值识别区间，需先按用户和日期去重归一，再用窗口函数或变量模拟分组；各数据库语法不同但逻辑一致，关键在日期处理与去重。

sql中怎样统计每个用户连续登录天数并进行分组

用窗口函数计算连续登录的日期差

连续登录的本质是：把每个用户的登录日期排序后，看“日期 - 排序号”是否恒定。相同值就代表一段连续区间。比如用户 A 登录了 2024-01-01、01-02、01-03，按顺序编号为 1/2/3，那么 date - INTERVAL rank DAY（或等价的 date - (rank - 1)）会得到三个相同的基准日 2024-01-01。

关键点在于：

必须先对每个用户按日期升序 ROW_NUMBER()
日期类型需支持算术运算（如 PostgreSQL/MySQL 8.0+/SQL Server 支持 date - INTERVAL n DAY；SQLite 用 julianday()；旧版 MySQL 需转成 UNIX_TIMESTAMP() 或用 DATE_SUB()）
不同数据库写法差异大，但逻辑一致

示例（MySQL 8.0+）：

SELECT user_id,
       MIN(login_date) AS start_date,
       MAX(login_date) AS end_date,
       COUNT(*) AS days
FROM (
  SELECT user_id,
         login_date,
         DATE_SUB(login_date, INTERVAL ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY login_date) - 1 DAY) AS grp
  FROM user_logins
) t
GROUP BY user_id, grp;

处理重复登录和时间精度问题

如果同一天有多个登录记录，直接 GROUP BY user_id, login_date 前必须去重，否则 ROW_NUMBER() 会把同一天拆成多行，破坏连续性判断。

常见错误现象：

统计出“连续 1 天”特别多 → 没去重
连续段被意外截断 → 时间字段含时分秒，未 DATE(login_time) 归一化

建议步骤：

先用 SELECT DISTINCT user_id, DATE(login_time) AS login_date FROM ... 构建干净日期集
若原始表只有 login_time（datetime/timestamp），务必用 DATE() 或 CAST(... AS DATE) 提取日期
不要依赖 GROUP BY 隐式去重，显式 DISTINCT 更可控

按连续天数分组并筛选（比如找 7 天以上用户）

上面的子查询已产出每段连续登录的 user_id、start_date、end_date、days，后续分组筛选就是普通聚合操作。

例如，只保留连续 ≥ 5 天的记录：

SELECT user_id, start_date, end_date, days
FROM (
  -- 上面的连续段子查询
) t
WHERE days >= 5;

若还需“每个用户最长连续天数”，再套一层：

SELECT user_id, MAX(days) AS max_consecutive_days
FROM (...) t
GROUP BY user_id;

注意：MAX(days) 是每个用户的最长段，不是总登录天数；若想统计“有多少用户达成过 7 天连续”，外层用 COUNT(DISTINCT user_id)。

兼容老版本 MySQL（无窗口函数）的替代方案

MySQL 5.7 及更早不支持 ROW_NUMBER()，只能用变量模拟：

SELECT user_id, MIN(login_date), MAX(login_date), COUNT(*) AS days
FROM (
  SELECT user_id,
         login_date,
         @grp := IF(@prev = user_id AND DATEDIFF(login_date, @prev_date) = 1,
                   @grp,
                   @grp + 1) AS grp,
         @prev := user_id,
         @prev_date := login_date
  FROM (SELECT DISTINCT user_id, DATE(login_time) AS login_date FROM user_logins) t
  CROSS JOIN (SELECT @grp := 0, @prev := '', @prev_date := '1970-01-01') init
  ORDER BY user_id, login_date
) t
GROUP BY user_id, grp;

风险点：