实现SCD Type 2：重复状态回溯的增量更新需求求助

核心逻辑 #

SCD Type 2 的核心目标是完整保留数据的所有历史版本，即使数据回归到之前的状态，也需将其视为一次新的状态变更，执行以下两步操作：

1. 将目标表中对应ID的当前活跃记录标记为失效；
2. 插入源表的最新记录作为新的活跃版本。

分步实现（SQL示例） #

1. 标记当前活跃记录为失效 #

更新目标表中ID匹配且状态为Active的记录，将其End_date设为源数据的日期，Status改为InActive：

-- MySQL 示例（适配日期格式转换）
UPDATE target_table t
JOIN source_table s ON t.ID = s.ID
SET 
    t.End_date = STR_TO_DATE(s.date, '%d-%m-%Y'),
    t.Status = 'InActive'
WHERE t.Status = 'Active';

-- PostgreSQL 示例
UPDATE target_table t
SET 
    t.End_date = TO_DATE(s.date, 'DD-MM-YYYY'),
    t.Status = 'InActive'
FROM source_table s
WHERE t.ID = s.ID AND t.Status = 'Active';

2. 插入新的活跃版本记录 #

将源表的最新记录插入目标表，补充SCD专属字段：

-- MySQL 示例
INSERT INTO target_table (ID, NAME, ADDRESS, Building, Floor, date, Start_date, End_date, Status)
SELECT 
    ID,
    NAME,
    ADDRESS,
    Building,
    Floor,
    STR_TO_DATE(date, '%d-%m-%Y'),
    STR_TO_DATE(date, '%d-%m-%Y'),
    STR_TO_DATE('31-12-9999', '%d-%m-%Y'),
    'Active'
FROM source_table;

-- PostgreSQL 示例
INSERT INTO target_table (ID, NAME, ADDRESS, Building, Floor, date, Start_date, End_date, Status)
SELECT 
    ID,
    NAME,
    ADDRESS,
    Building,
    Floor,
    TO_DATE(date, 'DD-MM-YYYY'),
    TO_DATE(date, 'DD-MM-YYYY'),
    TO_DATE('31-12-9999', 'DD-MM-YYYY'),
    'Active'
FROM source_table;

关键说明 #

• 即使数据回归到历史状态（如Mike回到India的ATT大楼），仍需新增记录而非复用旧版本，这是SCD Type 2追踪完整历史的硬性要求；
• 远未来日期（31-12-9999）用于标记当前活跃记录，后续有新变更时再更新其失效日期；
• 若源表包含多条增量记录，上述SQL可批量处理所有ID的状态变更，无需硬编码ID或日期。

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