如何在SQL中计算分组后连续达标的天数？-SQL-PHP中文网

“连续达标”指同一分组内按时间排序后is_pass=1的记录日期必须连续，中断则重新计数；需用行号差法识别连续段，而非简单sum(is_pass)。

如何在sql中计算分组后连续达标的天数？

什么是“连续达标”在分组场景下的实际含义

连续达标不是简单地 COUNT(*)，而是指同一分组内（比如某个用户、某台设备）按时间排序后，is_pass = 1 的记录必须日期/序号连续。中间一旦出现 is_pass = 0 或缺失日期，就得重新计数。

常见错误是直接用 GROUP BY user_id + SUM(is_pass)——这算的是总达标天数，不是连续段长度。真正要的是每个连续段的长度，尤其是最大值或所有段。

用行号差法识别连续段（兼容 MySQL 8.0+、PostgreSQL、SQL Server）

核心思路：对每个分组内按时间排序的达标记录打序号，再减去全局自然序号。相同差值即属于同一连续段。

SELECT 
  user_id,
  MIN(dt) AS start_dt,
  MAX(dt) AS end_dt,
  COUNT(*) AS consecutive_days
FROM (
  SELECT 
    user_id,
    dt,
    is_pass,
    ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY dt) 
      - ROW_NUMBER() OVER (PARTITION BY user_id, is_pass ORDER BY dt) AS grp
  FROM t_daily_check
  WHERE is_pass = 1
) t
GROUP BY user_id, grp;

ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY dt) 是该用户所有记录的顺序号
ROW_NUMBER() OVER (PARTITION BY user_id, is_pass ORDER BY dt) 是该用户+达标状态内的顺序号
两者相减，只要 dt 连续且 is_pass = 1，差值就恒定；一旦断掉，差值重置

注意：必须先 WHERE is_pass = 1 再计算，否则 is_pass = 0 会干扰分组逻辑。

MySQL 5.7 等不支持窗口函数时的替代方案

只能靠自连接或变量模拟行号，但变量方式不稳定（执行计划可能打乱顺序），更稳妥的是用相关子查询生成序号：

SELECT 
  t1.user_id,
  t1.dt,
  t1.is_pass,
  (SELECT COUNT(*) FROM t_daily_check t2 
   WHERE t2.user_id = t1.user_id 
     AND t2.dt <p>然后在外层用 <code>rn_all - rn_local</code> 当作 <code>grp</code> 字段继续分组。性能较差，数据量超 1 万行就明显变慢。</p>

不推荐用 @var := @var + 1 方式，MySQL 5.7 中变量赋值顺序不保证与 ORDER BY 一致
如果表有主键且时间字段唯一，可考虑用 (SELECT COUNT(*) FROM ... WHERE id 替代，避免时间重复问题

查“每个用户最长连续达标天数”时的典型陷阱

很多人写成：

SELECT user_id, MAX(consecutive_days)
FROM (/* 上面的分组结果 */) t
GROUP BY user_id;

看起来没错，但漏掉了关键点：如果某用户从未达标（全为 is_pass = 0），这条记录根本不会出现在子查询里，结果就丢失该用户。

正确做法是先保底生成所有用户，再左连接连续段结果：

SELECT 
  u.user_id,
  COALESCE(MAX(c.consecutive_days), 0) AS max_consecutive_days
FROM (SELECT DISTINCT user_id FROM t_daily_check) u
LEFT JOIN (
  /* 上面的连续段分组子查询 */
) c ON u.user_id = c.user_id
GROUP BY u.user_id;