如何在SQL中用SELF JOIN实现连续登录天数的统计-SQL-PHP中文网

self join统计连续登录的核心逻辑是通过日期差join关联相邻日期记录，再用date_sub(login_date, interval row_number() over(partition by user_id order by login_date) day)生成连续段标识grp，最后按user_id和grp分组计算长度。

如何在sql中用self join实现连续登录天数的统计

什么是SELF JOIN统计连续登录的核心逻辑

SELF JOIN本身不直接计算“连续”，它只是把同一张用户登录表 login_logs 按不同别名（比如 t1 和 t2）连起来，让某一天的记录能和“前一天”“后一天”的记录对上。真正识别“连续”的关键是：用日期差做JOIN条件，并配合分组+计数筛出长度≥N的序列。

常见错误是写成 ON t1.user_id = t2.user_id AND t2.login_date = DATE_ADD(t1.login_date, INTERVAL 1 DAY) 后直接 COUNT(*) —— 这只能查出“有后继日”的记录数，不是“连续段长度”。必须把连续日期映射到同一个分组标识上。

用DATE_SUB + GROUP BY 构造连续段ID

核心技巧是：对每个用户，按登录日期排序，然后用 login_date - INTERVAL ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY login_date) DAY 得到一个“基准日期”。同一连续段内，这个值恒定。

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 login_logs
) t
GROUP BY user_id, grp
HAVING COUNT(*) >= 3;

ROW_NUMBER() 必须按 login_date 升序，否则 grp 错乱
MySQL 8.0+、PostgreSQL、SQL Server 支持该写法；MySQL 5.7 需改用变量模拟窗口函数
如果表里有重复日期（同用户单日多次登录），先用 DISTINCT user_id, login_date 去重，否则 ROW_NUMBER() 会多算

为什么不能只靠LEFT JOIN + IS NULL判断边界

有人尝试用 LEFT JOIN 找“没有前一天”的记录作为连续段起点：

SELECT t1.user_id, t1.login_date
FROM login_logs t1
LEFT JOIN login_logs t2 
  ON t1.user_id = t2.user_id 
  AND t2.login_date = DATE_SUB(t1.login_date, INTERVAL 1 DAY)
WHERE t2.user_id IS NULL;

这只能拿到起点，但要统计长度还得再嵌套一次JOIN找终点，或加子查询数后续天数——性能差、可读性低、难以限制最小连续天数（如“至少连续7天”需7层JOIN或7个EXISTS）。