“连续达标”的本质是识别不间断的满足条件时间序列，需用LAG()判断状态延续性并用SUM() OVER构造段ID，而非依赖日期相减。
什么是“连续达标”的本质问题
连续达标不是简单求和或计数，而是识别一段不间断的、满足条件的时间序列。SQL 里没有原生的“连续”概念，必须靠窗口函数构造状态转移逻辑：前一天达标且当天达标 → 延续当前连续段；否则 → 开启新连续段。关键在于用 LAG() 或 SUM() OVER 打断连续性，而非依赖日期相减（日期空缺时会误判）。
实操建议：
- 先定义“达标”为布尔表达式（如
score >= 80），统一转成 0/1 - 用
SUM() OVER (ORDER BY date ROWS UNBOUNDED PRECEDING)累加“是否中断”，但需先构造中断标记 - 更稳的做法是：用
LAG()拿前一行达标状态，与当前行做逻辑判断，生成分组键
用LAG+条件累计生成连续段ID
核心思路是：如果当前行达标，且前一日也达标，则属于同一连续段；否则新开一段。这需要两层计算：第一层算“是否中断”，第二层用 SUM() OVER 累积中断次数作为段ID。
示例（PostgreSQL / MySQL 8.0+ / SQL Server）：
SELECT
user_id,
date,
score,
-- 第一步：标记是否“中断连续”（即：今日达标但昨日不达标，或今日不达标）
CASE
WHEN score >= 80 AND COALESCE(LAG(score) OVER (PARTITION BY user_id ORDER BY date), 0) < 80
THEN 1
WHEN score < 80 THEN 1
ELSE 0
END AS is_break,
-- 第二步：按用户累计中断次数，得到每段连续区间的唯一ID
SUM(CASE
WHEN score >= 80 AND COALESCE(LAG(score) OVER (PARTITION BY user_id ORDER BY date), 0) < 80
THEN 1
WHEN score < 80 THEN 1
ELSE 0
END) OVER (PARTITION BY user_id ORDER BY date) AS streak_group
FROM user_daily_score;注意：COALESCE(LAG(...), 0) 是防首行 LAG() 返回 NULL 导致整个 CASE 失效 —— 这个坑很多人踩。
按streak_group聚合出最大连续天数
有了 streak_group，连续达标段就变成了标准分组问题。但要注意：只统计其中“全达标”的段，即该段内所有 score >= 80 的记录才有效。
继续上例，追加聚合：
WITH labeled AS (
SELECT
user_id,
date,
score,
SUM(CASE
WHEN score >= 80 AND COALESCE(LAG(score) OVER (PARTITION BY user_id ORDER BY date), 0) < 80 THEN 1
WHEN score < 80 THEN 1
ELSE 0
END) OVER (PARTITION BY user_id ORDER BY date) AS streak_group
FROM user_daily_score
),
streaks AS (
SELECT
user_id,
streak_group,
COUNT(*) AS days,
MIN(date) AS start_date,
MAX(date) AS end_date
FROM labeled
WHERE score >= 80 -- 只取达标日参与连续段统计
GROUP BY user_id, streak_group
)
SELECT
user_id,
MAX(days) AS max_consecutive_days
FROM streaks
GROUP BY user_id;关键点：
-
WHERE score >= 80必须放在streaks子查询里，不能在最外层过滤 —— 否则会漏掉被中断包围的短连续段 - 不同数据库对
LAG()的 NULL 处理一致，但ORDER BY中若存在相同日期，需加次级排序（如id）保证确定性 - MySQL 5.7 不支持窗口函数，此方案不可用；得用变量或自连接模拟，性能差且易错
为什么不用DATEDIFF(date, ROW_NUMBER())？
有人尝试用 date - ROW_NUMBER() OVER (...) 构造伪连续键，原理是：连续日期的 date - rownum 相同。但这只在数据每日必有、无缺失时成立。
真实场景中常见问题：
- 用户某天没打卡 → 日期不连续，但逻辑上仍希望“只要后续达标就算延续”（例如周一达标、周二缺卡、周三又达标，应视为两段，而非错误合并）
- 多用户混排时，
ROW_NUMBER()全局编号会导致跨用户污染 - 时间精度为秒级时，
date - rownum计算可能溢出或类型不匹配（尤其在 SQL Server 或 Oracle 中）
所以，基于状态迁移的 LAG + SUM 模型才是鲁棒解法 —— 它不依赖物理日期间隔，只关心逻辑状态是否延续。
真正难的不是写对第一段窗口函数，而是想清楚：你的“连续”定义是否允许跳过非达标日？是否要排除节假日？这些业务规则会直接决定 is_break 的判断逻辑，而不是套个模板就能跑通。
