T-SQL实现按交替值合并月度数据生成起止时间窗口

需求说明 #

• 输入：按月粒度存储的明细表，包含month_ID（YYYYMM格式月份标识）、Value两个字段，样例覆盖202211至202307共9个月份，Value在10、12之间交替出现
• 输出：包含From（窗口起始月份）、To（窗口结束月份）、Value三个字段的结果集，规则为将连续相邻、Value相同的月份合并为单条时间窗口记录，窗口长度不固定
• 此前尝试过partition、dense_rank、行号错位关联等写法均未得到正确结果

核心思路 #

之前写法失效的核心原因是没有正确给「连续同值段」分配独立分组ID：

• 直接按Value分区会把不相邻的同值月份错误归为一组
• 固定偏移1行的行号关联无法适配长度不固定的连续窗口
  正确分组逻辑为变化标记累计法：按月份顺序逐行对比当前行和上一行的Value，值发生变化就打标记，对标记做累计求和，同一个累计值对应的所有行就是一段连续的同值窗口。

可直接运行的SQL代码 #

兼容所有支持窗口函数的SQL引擎（Hive/Spark SQL/MySQL 8.0+/PostgreSQL等）：

WITH step1 AS (
    SELECT
        month_ID,
        Value,
        -- 对比上一月份的Value，值发生变化则标记为1，相同为0
        CASE WHEN LAG(Value) OVER (ORDER BY month_ID) = Value THEN 0 ELSE 1 END AS is_change
    FROM your_source_table
),
step2 AS (
    SELECT
        month_ID,
        Value,
        -- 累计变化标记，连续同值的行会拿到相同的group_id
        SUM(is_change) OVER (ORDER BY month_ID) AS group_id
    FROM step1
)
SELECT
    MIN(month_ID) AS `From`,
    MAX(month_ID) AS `To`,
    Value
FROM step2
GROUP BY group_id, Value
ORDER BY `From`;

结果验证 #

以9个月份的样例数据为例，输入明细为：

运行上述代码输出结果为：

注意事项 #

• 不需要预先枚举Value的所有可能取值，不管是两个值交替还是多个值随机变化，只要是相邻连续的同值记录都会被正确合并
• 窗口子句中ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW是多数引擎SUM() OVER(ORDER BY ...)的默认行为，不需要额外写，部分老版本引擎可以加上保证逻辑正确
• 如果源表存在月份缺失的情况，需要先补全连续月份再跑上述逻辑，否则会把间隔缺失月份的同值记录错误合并

内容的提问来源于stack exchange，提问作者Curti7