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

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

连续签到的本质是：把同一用户按日期排序后，找出日期差等于行号差的连续子序列。关键不是算“差几天”，而是识别“哪几行属于同一连续段”。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;

• 更稳的做法：先算出每个用户的最大 sign_date，再反查该用户从那天往前能连多少天，用递归 CTE 或应用层循环——数据库硬算“当前连续”反而易错

连续签到统计真正麻烦的不是语法，而是日期去重、时区对齐、以及“连续”的定义是否包含节假日——这些都得在进窗口函数前就处理干净，否则结果偏差无法回溯。