如何利用SQL聚合函数计算连续签到天数的逻辑实现

连续签到天数计算本质是按日期连续性分组：用日期减行号生成恒定差值作为分组键，再统计各组长度；需确保数据按用户和日期排序、去重，并适配不同数据库的日期运算语法。

连续签到天数的计算本质是分组问题

连续签到不是简单求 COUNT(*)，而是要把日期序列按“是否连续”切分成若干段，再对每段取长度。核心在于识别断点：当某天签到，但前一天没签到，就说明新连续段开始。

常用做法是用日期减去行号（ROW_NUMBER()），同一连续段内这个差值恒定——因为日期等差、行号等差，差值不变；一旦断开，差值跳变。这个差值就是分组依据。

• 确保签到表有用户标识字段（如 user_id）和日期字段（如 sign_date），且日期为 DATE 类型（非 DATETIME，否则需先 CAST(sign_time AS DATE)）
• 必须先按 user_id 和 sign_date 排序，否则 ROW_NUMBER() 顺序错，差值就不可靠
• MySQL 8.0+、PostgreSQL、SQL Server 2012+ 支持窗口函数；SQLite 3.25+ 也支持，但旧版不支持 ROW_NUMBER()

标准写法：用 ROW_NUMBER() + 差值分组

以 MySQL 8.0 为例，计算每个用户的当前最长连续签到天数：

SELECT user_id, MAX(consecutive_days) AS max_streakFROM (  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) tGROUP BY user_id;

注意：DATE_SUB(sign_date, INTERVAL ... DAY) 是 MySQL 写法；PostgreSQL 用 sign_date - ROW_NUMBER() OVER (...)::INT；SQL Server 用 DATEADD(DAY, -ROW_NUMBER() OVER (...), sign_date)。差值类型必须与日期运算兼容。

• 别直接用 sign_date - ROW_NUMBER()（无单位），多数数据库会报类型错误
• 如果签到表存在重复日期（同一用户同天多次记录），先 DISTINCT 或 GROUP BY user_id, sign_date 去重，否则 ROW_NUMBER() 会把同一天算作多行，破坏连续性
• 这个逻辑默认只统计「已有数据中的连续段」，不预测未来或补全缺失日；若需包含今天但今天未签到，则不能直接套用

查当前正在发生的连续签到（含今日）

很多场景要的是「截至今天的连续天数」，而非历史最长。这时不能只依赖已有记录，得确认最后签到日是否紧连今天。

关键判断：最大签到日期 = 今天，且该连续段的起始日 ≤ 今天 - N + 1（N 为天数）。更稳妥做法是先筛出每个用户最近连续段：

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_streakFROM groupedWHERE end_date = CURDATE();

• CURDATE() 在 PostgreSQL 中换成 CURRENT_DATE，SQL Server 用 GETDATE() 配合 CAST(... AS DATE)
• 如果用户今天根本没签到，end_date = CURDATE() 不成立，结果为空——这正是预期行为
• 性能上，对大表务必在 (user_id, sign_date) 上建联合索引，否则 ROW_NUMBER() 开窗成本极高

容易被忽略的边界情况

真实业务中，连续签到逻辑常因这些细节崩坏：

• 时区问题：服务器时间 vs 用户本地时间。若签到接口按用户时区记录 sign_date，但数据库用 UTC 存储，DATE(sign_time) 可能跨日，导致「用户认为连续，系统判定断开」
• 单日多次签到：有些产品允许当天多次打卡，只记一次有效。若没在聚合前去重，ROW_NUMBER() 会虚增行数，使差值偏移
• 跨年/跨月：DATE_SUB 和 INTERVAL 在所有主流数据库中都支持跨年计算，无需特殊处理，但别手写 YEAR(sign_date)*365 + ... 这类近似算法——闰年、大小月会让结果错
• 空数据：用户从未签到，子查询可能返回空，外层 MAX() 为 NULL。业务代码需处理 NULL 场景，不能假设总有值

连续签到看着简单，真正落地时，日期语义、数据质量、时区、索引这四点漏掉任一个，结果就不可信。