如何在SQL中统计连续签到天数并按用户GROUP BY？-SQL-PHP中文网

用窗口函数识别连续签到段：先按用户和日期排序，用日期转天数与行号作差生成组标识（grp），再按用户和grp分组计数；需去重并建复合索引优化性能。

如何在sql中统计连续签到天数并按用户group by？

用窗口函数计算连续签到的「段」

连续签到的本质是：把同一用户按日期排序后，找出日期差等于行号差的连续子序列。关键不是算“差几天”，而是识别“哪几行属于同一连续段”。ROW_NUMBER() 和日期本身的 TO_DAYS()（MySQL）或 DATE_PART('day', date)（PostgreSQL）做差，相同差值即为同一连续段。

常见错误是直接对 date 做 LAG(date) 然后判断是否 =1 天前——这只能发现断点，无法聚合出每段长度。必须先打上段标识（group_id），再按用户+段标识 GROUP BY 计数。

MySQL 示例（假设表 sign_in 有 user_id, sign_date）：

SELECT user_id, COUNT(*) AS consecutive_days
FROM (
  SELECT user_id, sign_date,
         TO_DAYS(sign_date) - ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY sign_date) AS grp
  FROM sign_in
) t
GROUP BY user_id, grp;

PostgreSQL 需改用 sign_date - INTERVAL '1 day' * ROW_NUMBER() OVER ... 或 DATE_PART('day', sign_date::timestamp - '1970-01-01'::date) 配合 ROW_NUMBER 做差
注意：日期字段必须是 DATE 类型，带时间戳的要先 ::DATE 或 CAST(sign_time AS DATE)，否则同一天多次签到会拆成多行干扰连续性

取每个用户的最长连续签到天数

上一步只分出了所有连续段，但业务通常要的是“该用户历史最长连续多少天”。这时需在外层再套一层 MAX()，按 user_id 聚合。

容易踩的坑是忘记去重——如果用户当天多次签到，原始表可能含重复 (user_id, sign_date)，必须先 DISTINCT ON (user_id, sign_date)（PostgreSQL）或 GROUP BY user_id, DATE(sign_date)（MySQL）清洗。

安全写法（含去重）：

SELECT user_id, MAX(consecutive_days) AS max_consecutive_days
FROM (
  SELECT user_id, COUNT(*) AS consecutive_days
  FROM (
    SELECT DISTINCT user_id, DATE(sign_date) AS d
    FROM sign_in
  ) t1
  JOIN (
    SELECT user_id, DATE(sign_date) AS d,
           DATE(sign_date) - INTERVAL ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY DATE(sign_date)) DAY AS grp
    FROM sign_in
  ) t2 USING (user_id, d)
  GROUP BY user_id, grp
) t3
GROUP BY user_id;

SQL Server 用户注意：DATEADD(day, -ROW_NUMBER(), date) 是等效写法，别漏掉 OVER 的 PARTITION BY，否则全表被当成一段

性能敏感时避免全表扫描

当 sign_in 表超百万行，上述嵌套窗口函数可能变慢。核心优化点有两个：索引和范围剪枝。

必须有复合索引：CREATE INDEX idx_user_date ON sign_in(user_id, sign_date); —— 没这个，PARTITION BY user_id ORDER BY sign_date 就没法高效执行
如果只查“最近30天内连续签到”，务必在最内层加 WHERE sign_date >= CURRENT_DATE - INTERVAL '30 days'，窗口函数不会自动下推条件
某些旧版 MySQL（@grp := IF(@uid = user_id AND DATEDIFF(sign_date, @last) = 1, @grp, @grp + 1)，但变量顺序依赖 ORDER BY 且不可并行，稳定性差

区分“当前连续”和“历史最长连续”

业务常混淆这两个概念。“当前连续”指截至今天、未中断的天数；“历史最长”是过去任意一段峰值。前者需要判断“最后一条记录是否紧邻今天”，后者只需全量扫描。

查当前连续（MySQL）：

SELECT user_id, COUNT(*) AS current_consecutive
FROM (
  SELECT user_id, sign_date,
         @grp := IF(
           @prev_user = user_id AND DATEDIFF(sign_date, @prev_date) = 1,
           @grp,
           @grp + 1
         ) AS grp,
         @prev_user := user_id,
         @prev_date := sign_date
  FROM (SELECT user_id, DATE(sign_date) AS sign_date FROM sign_in ORDER BY user_id, sign_date) t,
       (SELECT @grp := 0, @prev_user := NULL, @prev_date := NULL) init
) t
WHERE grp = (SELECT MAX(grp) FROM (...same subquery...) t2 WHERE t2.user_id = t.user_id)
  AND LAST_DAY(sign_date) = LAST_DAY(CURDATE()) -- 简化示意，实际需确保是末尾段
GROUP BY user_id;