基于SCD Type 2表生成快照表的行拆分实现咨询

需求说明 #

我有一个SQL Server数据库，需要从SCD Type 2表创建快照表，要求：

• 每个SCD条目在其存在的每个月末都生成一条快照记录
• 当前月份的快照日期使用DATEADD(day, -1, CAST(GETDATE() AS date))的结果

现有SCD Type 2数据 #

期望快照结果 #

实现方案 #

思路概述 #

1. 生成所需日期序列：包含所有需要生成快照的月末日期，以及当前月份的指定日期（DATEADD(day, -1, CAST(GETDATE() AS date))）
2. 调整SCD表日期范围：将表示"当前有效"的9999-12-31替换为当前日期前一天，统一判断逻辑
3. 关联生成快照：将日期序列与调整后的SCD表关联，筛选出每个条目覆盖的日期，输出最终快照记录

SQL脚本实现 #

WITH DateSeries AS (
    -- 生成从最早DateFrom到上个月的所有月末日期
    SELECT 
        EOMONTH(DateVal) AS SnapshotDate
    FROM (
        SELECT DATEADD(month, n, MIN(DateFrom)) AS DateVal
        FROM (
            -- 生成足够数量的连续月份数
            SELECT TOP (DATEDIFF(month, (SELECT MIN(DateFrom) FROM SCDTable), GETDATE()) + 1) 
                n = ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) - 1
            FROM sys.all_columns
        ) AS Numbers
        CROSS JOIN (SELECT MIN(DateFrom) FROM SCDTable) AS MinDate
    ) AS MonthDates
    UNION ALL
    -- 添加当前月份的指定日期（昨天）
    SELECT DATEADD(day, -1, CAST(GETDATE() AS date)) AS SnapshotDate
),
AdjustedSCD AS (
    -- 处理SCD表中永久有效标记，替换为当前日期前一天
    SELECT 
        ID,
        data1,
        data2,
        DateFrom,
        CASE 
            WHEN DateTo = '9999-12-31' THEN DATEADD(day, -1, CAST(GETDATE() AS date))
            ELSE DateTo 
        END AS DateTo
    FROM SCDTable
)
SELECT 
    a.ID,
    a.data1,
    a.data2,
    d.SnapshotDate
FROM AdjustedSCD a
JOIN DateSeries d 
    ON d.SnapshotDate >= a.DateFrom 
    AND d.SnapshotDate <= a.DateTo
ORDER BY a.ID, d.SnapshotDate;

脚本说明 #

• DateSeries CTE：通过系统表生成连续月份序列，计算对应月末日期，再合并当前月份的指定日期；若时间跨度极大，可改用递归CTE生成日期序列避免行数限制
• AdjustedSCD CTE：统一有效日期范围，避免9999-12-31与实际日期的判断冲突
• 关联查询：匹配快照日期与条目有效区间，确保每个存在的月末都生成对应记录
• 注意将脚本中的SCDTable替换为你的实际SCD表名称

扩展建议 #

• 若需定期生成快照，可将脚本封装为存储过程，配合SQL Server代理作业自动执行
• 可根据业务需求调整日期序列的范围，不必强制从最早DateFrom开始

内容的提问来源于stack exchange，提问作者Jacek Jastrzębski