SELF JOIN统计连续登录的核心逻辑是通过日期差JOIN关联相邻日期记录，再用DATE_SUB(login_date, INTERVAL ROW_NUMBER() OVER(PARTITION BY user_id ORDER BY login_date) DAY)生成连续段标识grp，最后按user_id和grp分组计算长度。
什么是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）。
- 多层自连接在千万级日志表上极易超时
- 每增加1天连续要求，就得加1次JOIN，维护成本指数上升
- 无法在一个结果集中同时返回起止日期和长度
日期类型和时区容易踩的坑
-
login_date 必须是 DATE 类型，不是 DATETIME。若为后者，先用 DATE(login_date) 提取日期部分，否则 2024-01-01 14:30:00 和 2024-01-02 09:15:00 看似连续，但直接减会因时间部分导致差值 ≠ 1
- 跨时区场景下，确保所有登录时间已统一转为业务所在时区（如北京时间），否则凌晨时段可能出现“断连假象”
- PostgreSQL 中用
login_date - ROW_NUMBER() ...::INTEGER，MySQL 中用 DATE_SUB(login_date, INTERVAL ... DAY)，语法细节不兼容
login_date 必须是 DATE 类型，不是 DATETIME。若为后者，先用 DATE(login_date) 提取日期部分，否则 2024-01-01 14:30:00 和 2024-01-02 09:15:00 看似连续，但直接减会因时间部分导致差值 ≠ 1 login_date - ROW_NUMBER() ...::INTEGER，MySQL 中用 DATE_SUB(login_date, INTERVAL ... DAY)，语法细节不兼容 连续登录统计真正的难点不在JOIN本身，而在把“日期序列”可靠地聚合成“段”。一旦 grp 计算准确，后续过滤、排序、分页就都是常规操作。别在JOIN条件里硬塞逻辑，让窗口函数干它该干的事。
