ROW_NUMBER()差值法识别连续日期的核心在于：同一用户按登录日期排序后，序号与日期均呈等差，其差值在连续段内恒为定值，成为分组标识；必须PARTITION BY user_id再ORDER BY login_date，否则失效。
为什么用 ROW_NUMBER() 差值能识别连续日期
核心在于：对同一用户的登录记录按日期排序后，ROW_NUMBER() 生成的是严格递增的整数序列（1,2,3,…），而真实日期本身若连续，其与某个基准日的差值（如 date - '2000-01-01'）也呈等差。两者相减后，连续日期段内的结果恒为定值——这个定值就是“连续组”的唯一标识。
注意：必须先按 user_id 分组、再按 login_date 排序，否则差值失去意义。
- 错误做法：
ROW_NUMBER() OVER (ORDER BY login_date)—— 混合所有用户，序号乱套 - 正确写法：
ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY login_date) - 日期差推荐用
login_date - INTERVAL '1 day' * ROW_NUMBER()...或直接login_date - ROW_NUMBER() OVER (...)::int（PostgreSQL）；MySQL 需用DATE_SUB(login_date, INTERVAL rn DAY)
PostgreSQL / MySQL 8.0+ 实现连续天数统计
以 PostgreSQL 为例，目标是得到每个用户最长连续活跃天数，以及每一段连续区间的起止日期。
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,
login_date - ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY login_date)::int AS grp
FROM user_login_log
) t
GROUP BY user_id, grp
ORDER BY user_id, start_date;MySQL 8.0+ 只需把 ::int 改成 + INTERVAL 0 DAY 或显式 CAST(... AS SIGNED)，否则类型不匹配报错 ERROR 1292: Truncated incorrect date value。
- 若只要「最长连续天数」，外层加
MAX(consecutive_days)并GROUP BY user_id - 若要排除单日孤立记录，加
HAVING COUNT(*) > 1 - 日期字段必须是
DATE类型；若为TIMESTAMP，先CAST(login_time AS DATE)
遇到重复登录或跨午夜数据怎么办
如果同一天有多个登录记录（比如用户上午、下午各登一次），直接按 login_date 分组会虚增连续性。必须先去重，否则 ROW_NUMBER() 序列膨胀，差值崩坏。
- 预处理必做：
SELECT DISTINCT user_id, login_date::date FROM ...（PostgreSQL） - MySQL：
SELECT DISTINCT user_id, DATE(login_time) FROM ... - 不能依赖
GROUP BY user_id, login_date代替去重——它可能掩盖真实行为，且无法控制保留哪条记录 - 若业务要求“当天任意一次登录即算活跃”，那去重合理；若要求“连续24小时无断点”，就得用时间戳+窗口函数算间隔，不再是本方法适用场景
性能和边界情况提醒
该方法在千万级日志表上执行较慢，因为依赖全排序和窗口计算，且无法利用普通 B-tree 索引加速 PARTITION BY + ORDER BY 组合。
- 强烈建议在
(user_id, login_date)上建联合索引 - SQLite 不支持窗口函数，此法完全不可用
- SQL Server 2012+ 支持，但
ROW_NUMBER()必须带ORDER BY，且OVER()内不能省略PARTITION BY - 差值法对“连续”定义是严格的日历连续（不含周末/节假日过滤），如需工作日连续，得先生成工作日维度表做 JOIN，再跑差值
真正容易被忽略的是：当某用户最后登录日是 2024-05-01，但数据库只存到那天，你无法判断他是否还会在 05-02 登录——连续段截止状态永远是开放的，统计结果仅反映已知数据范围内的事实。
