如何在月度快照表上实现SCD Type 2缓慢变化维度？

针对你的月度快照数据表，要实现SCD并生成valid_from和valid_to列，核心是识别同一ID下连续属性相同的记录组，再为每个组计算有效起止日期。以下是具体实现步骤和SQL示例：

实现逻辑 #

1. 分组识别：按ID分组，对比每行与上一行的所有目标列（ID、other、Team），当属性发生变化时，开启新的分组。
2. 聚合生成SCD记录：对每个分组，取最早的Period作为valid_from；下一个分组的起始日期减1天作为当前组的valid_to，最后一组的valid_to设为永久有效日期（如9999-12-31）。

通用SQL示例 #

WITH ranked_data AS (
    SELECT 
        ID,
        other,
        Team,
        Period,
        -- 标记属性变化的分组：属性与上一行不同时，组号递增
        SUM(CASE WHEN prev_attr = curr_attr THEN 0 ELSE 1 END) OVER (PARTITION BY ID ORDER BY Period) AS group_id
    FROM (
        SELECT 
            ID,
            other,
            Team,
            Period,
            -- 拼接所有需对比的列，作为当前行的属性标识
            CONCAT(ID, '|', other, '|', Team) AS curr_attr,
            -- 获取上一行的属性标识
            LAG(CONCAT(ID, '|', other, '|', Team)) OVER (PARTITION BY ID ORDER BY Period) AS prev_attr
        FROM your_table
    ) t
),
scd_records AS (
    SELECT 
        ID,
        other,
        Team,
        MIN(Period) AS valid_from,
        -- 计算当前组的有效结束日期：下一组起始日减1天，无下一组则设为永久有效
        COALESCE(
            LEAD(MIN(Period)) OVER (PARTITION BY ID ORDER BY MIN(Period)) - INTERVAL '1 day',
            '9999-12-31'::DATE
        ) AS valid_to
    FROM ranked_data
    GROUP BY ID, other, Team, group_id
    ORDER BY ID, valid_from
)
SELECT * FROM scd_records;

数据库适配说明 #

• 若使用SQL Server，将日期计算部分改为：DATEADD(day, -1, LEAD(MIN(Period)) OVER (...))
• 若使用MySQL，改为：DATE_SUB(LEAD(MIN(Period)) OVER (...), INTERVAL 1 DAY)

示例输出 #

针对你提供的数据，执行后会得到3条SCD记录：

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