如何在SQL中计算用户连续登录天数并处理日期断档-SQL-PHP中文网

答案：用datediff与row_number差值法识别连续登录段，即对每个user_id按login_date排序后，计算“日期-序号”得到恒定grp值，相同grp即为同一连续段；需partition by user_id、login_date转date类型、mysql 5.7需变量模拟。

如何在sql中计算用户连续登录天数并处理日期断档

用 DATEDIFF + ROW_NUMBER 计算连续登录段

连续登录的本质是：把用户每天的登录日期排序后，若 日期 - 排序序号 的值相同，则属于同一连续段。这个差值就是“连续组标识”。关键不是算总天数，而是先分组再聚合。

实操时注意三点：
- 必须对每个 user_id 单独排序，用 PARTITION BY user_id ORDER BY login_date
- login_date 要是 DATE 类型，不能带时间戳，否则需先 CAST(login_time AS DATE)
- MySQL 8.0+、PostgreSQL、SQL Server 都支持；MySQL 5.7 及更早版本不支持窗口函数，得用变量模拟（后面会提）

SELECT
  user_id,
  MIN(login_date) AS start_date,
  MAX(login_date) AS end_date,
  COUNT(*) AS consecutive_days
FROM (
  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 user_login_log
) t
GROUP BY user_id, grp;

MySQL 5.7 怎么绕过窗口函数限制

没有 ROW_NUMBER() 就得靠变量维护序号。核心是按用户和日期排序后，用 @rn := @rn + 1 生成序号，再用 @prev_user 判断是否切换用户重置。

容易踩的坑：
- 变量初始化必须写在 SELECT 前，且要确保排序稳定（ORDER BY user_id, login_date 不可少）
- 如果表数据量大，变量方式性能明显劣于窗口函数，别在千万级日志表上直接跑
- DATE_SUB 在 MySQL 中对变量计算敏感，建议统一转成 UNIX_TIMESTAMP 差值再除 86400，避免跨月误差

SELECT user_id, MIN(login_date), MAX(login_date), COUNT(*) AS consecutive_days
FROM (
  SELECT
    user_id,
    login_date,
    DATE_SUB(login_date, INTERVAL @rn DAY) AS grp,
    @rn := IF(@prev_user = user_id, @rn + 1, 1) AS rn,
    @prev_user := user_id
  FROM user_login_log
  CROSS JOIN (SELECT @rn := 0, @prev_user := '') AS _
  ORDER BY user_id, login_date
) t
GROUP BY user_id, grp;

断档超过 N 天怎么算“新连续段”

默认逻辑是“隔一天就算断”，但业务常要求“允许最多断 2 天仍视为连续”。这时不能依赖 ROW_NUMBER()，而要用前一行日期做比较。

推荐用 LAG() 获取上一次登录日期，再判断间隔：
- LAG(login_date) OVER (PARTITION BY user_id ORDER BY login_date)
- 若 DATEDIFF(login_date, prev_login) ，则继承上一组编号；否则新建组<br> - 组编号用条件累加：用 <code>SUM(CASE WHEN ... THEN 0 ELSE 1 END) OVER (...)

注意：
- LAG 返回 NULL 是首条记录，要 IS NULL 单独处理
- PostgreSQL 和 SQL Server 支持 SUM() OVER 累加，MySQL 8.0+ 也支持；旧版只能嵌套子查询模拟
- 断档阈值写死在 SQL 里不灵活，线上建议抽成参数或配置表关联