如何通过SQL结合ROW_NUMBER和日期字段实现连续达标月份的统计算法？

本文共计868个文字，预计阅读时间需要4分钟。

直接说明结论：

为什么月份不能像日期一样直接相减

月份是字符串（如 '2025-03'）或年月整数（如 202503），没有内置的“加1个月”原子语义。用 DATE_ADD() 或 TO_DAYS() 强转会出问题：

• TO_DAYS('2025-03') 返回的是 3 月 1 日的天数，但你根本不知道该月是否有记录；
• DATE_ADD('2025-03-01', INTERVAL 1 MONTH) 得到 '2025-04-01'，可你的数据里月份字段是 '2025-04'，类型不一致导致 JOIN 或比较失败；
• 更关键的是：业务定义的“连续月份”指日历上相邻的月份（如 2025-03 → 2025-04），不是字符串字典序连续（'2025-03' → '2025-04' 可以，但 '2025-12' → '2026-01' 字典序就断了）。

正确做法：把月份标准化为日期再算差值

核心是统一转换成可计算的日期起点（比如每月 1 日），再套用经典“序号差法”：

• 先确保月份字段是 CHAR(7) 或 DATE 类型；如果不是，用 STR_TO_DATE(month_str, '%Y-%m') 转成 DATE；
• 用 STR_TO_DATE(CONCAT(month, '-01'), '%Y-%m-%d') 安全转成当月首日；
• 对每个用户，按月份升序排，生成 rn = ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY month)；
• 计算分组键：grp = DATE_SUB(STR_TO_DATE(CONCAT(month, '-01'), '%Y-%m-%d'), INTERVAL rn - 1 MONTH)；
• 这个 grp 在连续月份中恒定——例如 2025-03、2025-04、2025-05 对应的 grp 都是 '2025-03-01'。

达标条件必须在分组前过滤，不能后筛

连续达标 ≠ 连续月份中“有达标记录”，而是“所有月份都达标”。常见错误是先求出所有连续月份段，再 HAVING COUNT(*) = COUNT(CASE WHEN score >= 80 THEN 1 END) ——这会漏掉中间某月没数据但业务上算“未达标”的情况。

正确顺序是：

• 第一步：只取 score >= 80 的记录（即“达标月”）；
• 第二步：对这些达标月执行上述 ROW_NUMBER + 差值分组；
• 第三步：按 user_id 和 grp 分组，COUNT(*) 就是该段连续达标月份数。

注意：如果某用户在 2025-03 达标、2025-04 缺失、2025-05 达标，这两段会被拆成两个独立 grp，不会被误判为连续 —— 这正是你要的逻辑。

MySQL 8.0+ 示例片段（可直接跑）

WITH qualified AS ( SELECT user_id, month, STR_TO_DATE(CONCAT(month, '-01'), '%Y-%m-%d') AS dt FROM user_monthly_score WHERE score >= 80 ), ranked AS ( SELECT user_id, month, dt, ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY month) AS rn FROM qualified ), grouped AS ( SELECT user_id, month, DATE_SUB(dt, INTERVAL rn - 1 MONTH) AS grp FROM ranked ) SELECT user_id, MIN(month) AS start_month, MAX(month) AS end_month, COUNT(*) AS streak_months FROM grouped GROUP BY user_id, grp ORDER BY user_id, start_month;

最后一句容易被忽略：MIN(month) 和 MAX(month) 必须从原始 month 字段取，别用 grp 格式化输出——因为 grp 是日期类型，显示为 '2025-03-01'，而你要的是 '2025-03'。