如何在SQL中通过聚合函数统计连续签到天数？-SQL-PHP中文网

row_number()差值法可识别连续签到，因连续日期与序号相减得恒定起始日；须先去重、按user_id分组排序、用date_sub等兼容函数计算差值分组。

如何在sql中通过聚合函数统计连续签到天数？

用 `ROW_NUMBER()` 和日期差识别连续签到段

连续签到的本质是：签到日期构成等差数列。只要把每个签到记录按时间排序，再用日期减去其序号，同一连续段的结果必然相同——因为「第 n 天的日期 − n」在连续时恒为起始日。

常见错误是直接对 DATE_SUB(sign_in_date, INTERVAL ROW_NUMBER() OVER (...) DAY) 分组，却忘了先去重、排序，或没处理用户多签同一天的情况。

必须先按 user_id 和 sign_in_date 去重（DISTINCT 或 GROUP BY），否则同一天多次签到会干扰序号
ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY sign_in_date) 的 PARTITION BY 不能漏，否则全表编号会混用户
日期相减要用 DATE_SUB(sign_in_date, INTERVAL rn DAY)，不是 sign_in_date - rn（MySQL 中后者可能被解释为数值运算）

MySQL 8.0+ 中完整统计每人最长连续天数

核心思路：构造「连续段标识」后分组计数，再取最大值。注意 MIN(sign_in_date) 和 MAX(sign_in_date) 可反推起止日，方便验证。

SurferSEO

SurferSEO是一款面向 SEO 内容规划、写作和优化的 AI 内容平台。

下载

SELECT 
  user_id,
  MAX(consecutive_days) AS max_consecutive_days,
  MIN(segment_start) AS earliest_streak_start
FROM (
  SELECT 
    user_id,
    DATE_SUB(sign_in_date, INTERVAL rn DAY) AS segment_key,
    MIN(sign_in_date) AS segment_start,
    COUNT(*) AS consecutive_days
  FROM (
    SELECT 
      user_id,
      sign_in_date,
      ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY sign_in_date) AS rn
    FROM (SELECT DISTINCT user_id, sign_in_date FROM sign_in_log) t
  ) ranked
  GROUP BY user_id, DATE_SUB(sign_in_date, INTERVAL rn DAY)
) grouped
GROUP BY user_id;

PostgreSQL 或 SQLite 需替换日期运算写法

PostgreSQL 不支持 DATE_SUB，得用 sign_in_date - (rn || ' days')::INTERVAL；SQLite 没原生窗口函数支持 ROW_NUMBER()（3.25+ 支持，但需确认版本）。若环境不支持，得改用自连接或递归 CTE，性能明显下降。

PostgreSQL 示例片段：sign_in_date - (rn::TEXT || ' days')::INTERVAL
SQLite 若版本 ROW_NUMBER() 不可用，必须用子查询模拟序号，且无法高效处理大表
所有数据库中，sign_in_date 字段类型必须是 DATE 或带时区的 TIMESTAMP，若存为字符串（如 '2024-05-01'），需先 CAST 或 STR_TO_DATE 转换，否则减法失败

遇到 `NULL` 或跨年/闰年时的实际影响

只要日期类型正确，跨年、闰年完全不影响算法——因为计算基于日粒度差值，不是月份或年份逻辑。真正要小心的是 NULL 签到日期：它会让 ROW_NUMBER() 排序异常（NULLS FIRST/LAST 行为因数据库而异），且 DATE_SUB(NULL, ...) 返回 NULL，导致整段丢失。