SQL聚合函数实现连续签到天数的计算逻辑与方法

连续签到天数这事儿，说穿了就是个分组问题。不是简单数一数总共有多少条签到记录，而是得把日期按“是不是连着的”切成一截一截，再数每一截有多长。关键在哪？在于识别断点：如果今天签到了，但昨天没签，那就是一个新连续段的开始。 实现这个逻辑，最经典的手法是用日期减去行号（`ROW_NUMBER()`）。原理说起来也简单：同一段连续签到里，日期是按天递增的，行号也是按顺序递增的，一减，出来的差值恒定不变；一旦中间断了一天，差值就会跳变。这个差值，就是给每段连续记录打上的“组标”。 当然，动手之前有几件事得先确认：签到表里一定要有用户ID和日期字段，日期那列必须是纯日期类型，不是带时间戳的`DATETIME`——如果是后者，得先`CAST`成日期才行。另外，给行号排序前，必须先按用户和日期排好序，不然计算出的差值会乱得离谱。至于窗口函数，MySQL 8.0+、PostgreSQL、SQL Server 2012+ 都支持，SQLite 也得3.25以上的版本才行。 **标准写法：用 `ROW_NUMBER()` + 差值分组** 以MySQL 8.0为例，要算每个用户历史上的最长连续签到天数，可以这么写： ```sql SELECT user_id, MAX(consecutive_days) AS max_streak FROM ( SELECT user_id, DATE_SUB(sign_date, INTERVAL ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY sign_date) DAY) AS grp, COUNT(*) AS consecutive_days FROM sign_log GROUP BY user_id, grp ) t GROUP BY user_id; ``` 注意：代码里的`DATE_SUB`是MySQL的写法；换成PostgreSQL，就得写成`sign_date - ROW_NUMBER() OVER (...)::INT`；SQL Server则用`DATEADD(DAY, -ROW_NUMBER() OVER (...), sign_date)`。不同数据库的差值类型和日期运算语法得对上号，否则报错是轻的，算错才是大问题。 另外有两个容易踩的坑：一个是签到表里可能有重复日期——同一天同一个用户签了好几次，这时候必须先`DISTINCT`或者`GROUP BY`去重，不然行号会把同一天拆成多行，连续性就崩了。另一个是这种写法只统计已有记录里的连续段，不会帮你去预测未来或补缺失日期。如果想算“截至今天的连续签到天数”，逻辑就得换一下。 **查当前正在发生的连续签到（含今日）** 很多业务场景关心的不是历史最长，而是“到今天为止，我连续签到了多少天”。这时候光靠已有记录是不够的，得确认最后一条签到日是不是紧挨着今天。 判断逻辑其实就一条：最大签到日期必须等于今天，而且那一段连续记录的起始日不能早于“今天 - 天数 + 1”。更稳妥的做法是先筛出每个用户最近一段连续记录： ```sql WITH ranked AS ( SELECT user_id, sign_date, ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY sign_date) AS rn FROM sign_log WHERE sign_date <= CURDATE() ), grouped AS ( SELECT user_id, DATE_SUB(sign_date, INTERVAL rn DAY) AS grp, MIN(sign_date) AS start_date, MAX(sign_date) AS end_date, COUNT(*) AS days FROM ranked GROUP BY user_id, grp ) SELECT user_id, days AS current_streak FROM grouped WHERE end_date = CURDATE(); ``` 这里也有几个地方要注意：`CURDATE()`在PostgreSQL里得换成`CURRENT_DATE`，SQL Server换成`GETDATE()`再`CAST`成日期。如果用户今天压根没签到，那`end_date = CURDATE()`这一条就不会命中，结果自然是空的——这也正是期望的行为。还有，这张表如果数据量很大，`ROW_NUMBER()`的开窗成本非常可观，务必在`(user_id, sign_date)`上建联合索引。 **容易被忽略的边界情况** 真实业务里，连续签到逻辑最容易栽在下面这几个细节上： 时区问题是个大杀器。服务器存的是UTC时间，但用户签到按本地时区算，一旦`DATE(sign_time)`转换后跨了日，用户觉得“我连续签到了”，系统却判定“断开了”。这里有个坑要提前说。 单日多次签到也很常见。有些产品允许当天多次打卡，但只算一次。如果没在聚合前去重，`ROW_NUMBER()`会多出一堆行，差值算出来全错位。 跨年、跨月听起来复杂，但实际上主流数据库的`DATE_SUB`和`INTERVAL`都能正确处理，不用额外操心。千万别自己手写`YEAR(sign_date)*365 + ...`这种近似算法——闰年、大小月分分钟让你翻车。 最后就是空数据。用户从没签过到，子查询返回空，外层的`MAX()`自然就是`NULL`。业务代码里如果不处理这个`NULL`场景，页面崩了才开始排查，大家都很累。 连续签到看着简单，真正落地时，日期语义、数据质量、时区、索引这四点——漏掉任一个，结果就不可信。