SQL统计分类连续达标月份数开窗函数与差值分组方法详解

统计连续达标月份，是数据分析中一个经典且高频的需求。无论是监控用户活跃度、追踪产品KPI，还是评估销售业绩，我们常常需要知道：某个指标在特定分类下，连续“达标”了多久。

免费影视、动漫、音乐、游戏、小说资源长期稳定更新！ 👉 点此立即查看 👈

直接说结论：用 ROW_NUMBER() 减去按分类和时间排序后的“年月序号”（例如 YEAR(date)*12 + MONTH(date)），得到的差值相同的记录就属于同一个连续月份段。最后，按分类和这个差值分组计数，问题就迎刃而解了。

为什么差值能识别连续月份？

这个方法的巧妙之处在于对齐了两个等差数列。想象一下，连续的月份（如202401, 202402, 202403）在数值上是等差的。同时，ROW_NUMBER() 函数在同一个分类内按时间排序后，生成的序号也是等差的。两个等差数列相减，结果在同一个连续段内自然就恒定了。这个恒定值，本质上是一个“偏移锚点”，它巧妙地将数据的物理顺序对齐到了日历顺序上。

相比之下，一些传统方法就显得笨拙或易错。比如，用 LAG() 函数逐行比对上一月，逻辑嵌套深，还容易漏掉跨越多个月的连续情况；而用 DATEADD(MONTH, -1, ...) 进行自连接，不仅性能堪忧，在边界条件上也容易出错。

几个实操要点：

• 数据类型是基础：确保日期字段是 DATE 或 DATETIME 类型，避免使用字符串，否则比较和计算会埋下隐患。
• 计算“月序号”：使用 YEAR(date)*12 + MONTH(date) 来生成一个唯一的整数序号，这比拼接年月字符串更可靠、更高效。
• 天然处理断点：这个方法的美妙之处在于，即使数据中存在跨年的间隔（比如记录从202312月直接跳到202402月），算法也能自动识别出断点并正确分组，无需额外编写复杂的断点检测逻辑。

SQL实现：核心三步走

以支持窗口函数的现代数据库（如 MySQL 8.0+、PostgreSQL、SQL Server）为例，实现必须严格遵循以下顺序：

第一步：排序与编号
在子查询中，按 category 分区，并按 date 升序排列，使用 ROW_NUMBER() 为每一行生成一个连续的序号。

第二步：计算关键差值
为每一行计算“年月序号”，然后用这个序号减去第一步生成的 ROW_NUMBER()。这个差值（通常命名为 diff 或 grp_key）就是后续分组的依据。

第三步：分组统计
在外层查询中，按 category 和计算出的 diff 进行分组，使用 COUNT(*) 即可得到每个连续段的长度。

来看一个MySQL的简明示例：

SELECT category, COUNT(*) AS consecutive_months
FROM (
  SELECT category,
         (YEAR(date)*12 + MONTH(date)) 
         - ROW_NUMBER() OVER (PARTITION BY category ORDER BY date) AS diff
  FROM monthly_scores 
  WHERE score >= 80
) t
GROUP BY category, diff;

过滤条件的位置：一个关键的细节

这里有一个极易踩坑的细节：达标条件的过滤（WHERE score >= 80）必须放在内层子查询里。如果放在外层，那么 ROW_NUMBER() 的生成就会包含那些不达标的月份，导致差值计算完全错乱，无法正确识别连续段。

根据不同的业务需求，可以在此模式上灵活变通：

• 如果需要知道原始表中每一条达标记录所属的连续段长度，可以用计算出的 diff 作为关联键，将结果连回原表。
• 如果只关心每个分类下的“最长连续月数”，则在外层再套一层聚合，使用 MAX(consecutive_months) 即可。
• 注意数据库方言：在SQL Server中，年月序号的公式通常写作 DATEPART(YEAR, date)*12 + DATEPART(MONTH, date)。

时区与唯一性：不容忽视的边界情况

最后，还有两个高级但重要的注意事项。

时区问题：如果日期字段是带时区的 TIMESTAMP WITH TIME ZONE（常见于PostgreSQL），务必先将其转换为标准的日期类型，例如使用 date::DATE 或 CAST(date AT TIME ZONE 'UTC' AS DATE)。否则，同一条记录在不同时区下可能被解析成不同的日历日，导致月份统计错误。

排序唯一性：当某个分类下只有单月达标时，COUNT(*) 结果为1，但计算过程依然依赖于 ROW_NUMBER() 的确定性。务必确保 ORDER BY 子句能产生唯一的排序。如果 date 在同一个月内可能重复，一定要在 ORDER BY 中加入一个唯一列（如主键 id），以保证结果的绝对稳定。

结论：用 ROW_NUMBER() 减去“年月序号”（YEAR×12+MONTH）得到恒定差值，相同差值即同一连续月份段；因两者均为等差序列，相减后连续段内结果恒定，实现物理序与日历序对齐。