因为ROW_NUMBER()仅按时间排序编号而不识别连续性,需用日期减去ROW_NUMBER()构造恒定差值作为分组键来识别连续区间。

连续签到天数为什么不能直接用 ROW_NUMBER()?
因为 ROW_NUMBER() 只按时间排序编号,不区分“是否连续”。比如用户在 1号、2号、4号签到,ROW_NUMBER() 给出 1、2、3,但实际连续段只有两段(1–2 天、单独 4 号)。必须把“日期本身”和“序号”做差,让同一连续段内差值恒定——这是识别连续区间的通用 trick。
核心思路:用日期减去 ROW_NUMBER() 构造分组键
对每个用户,按签到日期升序排,算 ROW_NUMBER(),再用签到日期减去该序号(转成天数差)。同一连续段里,日期每天+1、序号也每天+1,差值不变。这个差值就是连续签到的“锚点”,可用于 GROUP BY。
实操注意点:
-
签到日期必须是DATE类型(不是DATETIME),否则减法可能出错;若为TIMESTAMP,先用DATE(签到时间)提取日期 - MySQL 8.0+ / PostgreSQL / SQL Server 2012+ 支持标准窗口函数;SQLite 需 3.25+ 且需启用
ENABLE_WINDOW_FUNCTIONS - Oracle 中用
TRUNC(签到时间)替代DATE()
示例(MySQL):
SELECT user_id,
MIN(sign_date) AS start_date,
MAX(sign_date) AS end_date,
COUNT(*) AS days
FROM (
SELECT user_id,
sign_date,
DATE_SUB(sign_date, INTERVAL ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY sign_date) DAY) AS grp
FROM user_sign_in
) t
GROUP BY user_id, grp;
如何查“当前最长连续签到天数”?
不是查所有连续段,而是每个用户只取当前(截止最新签到日)仍在延续的那段——即包含最大 sign_date 的连续段。不能只用 MAX(COUNT(*)),得先定位到“末尾连续段”。
关键操作:
- 先给每条记录打上所属连续段的
grp(同上) - 再对每个用户,找出其最大
sign_date对应的grp - 最后按该
grp统计天数
简写写法(MySQL):
WITH ranked AS (
SELECT user_id,
sign_date,
DATE_SUB(sign_date, INTERVAL ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY sign_date) DAY) AS grp,
ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY sign_date DESC) AS rn_desc
FROM user_sign_in
),
last_grp AS (
SELECT user_id, grp
FROM ranked
WHERE rn_desc = 1
)
SELECT r.user_id, COUNT(*) AS current_streak
FROM ranked r
JOIN last_grp l ON r.user_id = l.user_id AND r.grp = l.grp
GROUP BY r.user_id;
容易被忽略的边界问题
连续签到统计最常翻车的地方不在逻辑,而在数据质量与定义歧义:
- 用户当天多次签到 → 必须先去重:
DISTINCT user_id, DATE(sign_time),否则同一天多条记录会撑大ROW_NUMBER(),破坏差值稳定性 - 跨年/跨月计算时,
DATE_SUB在 MySQL 中可安全处理(如2024-01-01 - INTERVAL 2 DAY = 2023-12-30),但某些旧版驱动或 ORM 可能隐式转成字符串拼接,务必验证结果 - “连续”是否包含今天?如果今天还没签到,那“当前连续天数”应为 0;但上述写法默认只统计已有记录。上线前必须明确业务定义,并在查询中加
LEFT JOIN或补全日期维度校验
真实场景里,连续天数往往要实时展示在 App 首页,这时候窗口函数只是离线口径;线上服务通常预计算并缓存,避免每次拉全量签到表。











