如何基于全量月载源表生成含Valid_from、Valid_to时间区间的结果表

核心采用岛屿（Gaps and Islands）算法处理同主键下连续相同属性的合并，天然适配属性可回退的场景，不会出现历史相同属性错误合并的问题。

前提说明 #

• 假设业务源表名为src_monthly_full
• etl_id为周期标识，取值越大代表数据加载时间越晚（例：格式为yyyymm的数值，202401代表2024年1月加载的全量数据）
• pk为业务主键，code_paym、code_terms为需要校验是否变更的业务属性

实现代码 #

WITH step1_mark_change AS (
    -- 第一步：标记同主键下当前行属性相比上一个周期是否发生变化
    SELECT 
        pk,
        code_paym,
        code_terms,
        etl_id,
        CASE WHEN 
            code_paym = LAG(code_paym, 1) OVER (PARTITION BY pk ORDER BY etl_id)
            AND code_terms = LAG(code_terms, 1) OVER (PARTITION BY pk ORDER BY etl_id)
        THEN 0 ELSE 1 END AS is_change
    FROM src_monthly_full
),
step2_calc_island_id AS (
    -- 第二步：计算连续相同属性的分组ID（岛屿ID），累加变化标记即可得到分组标识
    SELECT 
        pk,
        code_paym,
        code_terms,
        etl_id,
        SUM(is_change) OVER (PARTITION BY pk ORDER BY etl_id ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS island_id
    FROM step1_mark_change
)
-- 第三步：按主键+岛屿ID分组，合并连续周期生成拉链
SELECT 
    pk,
    code_paym,
    code_terms,
    MIN(etl_id) AS valid_from,
    -- 最新生效的周期用业务约定的最大日期标记为未失效
    CASE WHEN MAX(etl_id) = MAX(etl_id) OVER () THEN '999912' ELSE MAX(etl_id) END AS valid_to
FROM step2_calc_island_id
GROUP BY pk, code_paym, code_terms, island_id
ORDER BY pk, valid_from;

适配调整说明 #

• 如果etl_id为日期类型，把最终valid_to的默认值改为'9999-12-31'即可
• 如果需要校验的属性字段有增减，仅需要修改第一步is_change的判断逻辑即可
• 若业务要求结束日期为下一周期的前一天，可将MAX(etl_id)替换为对应日期计算逻辑（例：DATE_SUB(DATE_ADD(STR_TO_DATE(MAX(etl_id), '%Y%m'), INTERVAL 1 MONTH), INTERVAL 1 DAY)）

效果验证示例 #

源表测试数据：

pk	code_paym	code_terms	etl_id
1	A01	T01	202401
1	A01	T01	202402
1	A02	T01	202403
1	A01	T01	202404

输出拉链结果：

pk	code_paym	code_terms	valid_from	valid_to
1	A01	T01	202401	202402
1	A02	T01	202403	202403
1	A01	T01	202404	999912

可以看到202404属性回退到历史值A01+T01时，生成了独立的拉链段，没有和202401-202402的历史段错误合并，完全符合需求。

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