Oracle SQL中合并两个SCD Type2缓慢变化维度表

要整合两张独立维护的SCD Type2表（dt_cust客户表和dt_adre地址表）的联合历史状态，核心思路是提取所有数据变更的时间节点，生成连续的时间区间，再匹配每个区间内有效的客户和地址记录。

假设表结构 #

首先明确两张SCD2表的典型结构（实际字段可根据业务调整）：

• dt_cust：cust_id（客户ID）、cust_name（客户名称）、start_dt（有效起始日）、end_dt（有效结束日，9999-12-31表示当前有效）
• dt_adre：cust_id（关联客户ID）、adre_line1（地址行1）、adre_line2（地址行2）、start_dt、end_dt

解决方案SQL #

WITH all_change_dates AS (
    -- 收集两张表所有的有效起始/结束日期，去重得到所有变更时间点
    SELECT start_dt AS change_dt FROM dt_cust
    UNION
    SELECT end_dt AS change_dt FROM dt_cust
    UNION
    SELECT start_dt AS change_dt FROM dt_adre
    UNION
    SELECT end_dt AS change_dt FROM dt_adre
),
time_intervals AS (
    -- 生成连续的时间区间，每个区间对应一段客户+地址的稳定状态
    SELECT
        change_dt AS interval_start,
        LEAD(change_dt) OVER (ORDER BY change_dt) AS interval_end
    FROM all_change_dates
)
SELECT
    c.cust_id,
    c.cust_name,
    a.adre_line1,
    a.adre_line2,
    ti.interval_start AS effective_start_dt,
    -- 处理最后一个区间的结束日，转换为SCD2标准的当前有效标记
    CASE
        WHEN ti.interval_end IS NULL THEN DATE '9999-12-31'
        ELSE ti.interval_end - INTERVAL '1' DAY
    END AS effective_end_dt,
    -- 标记当前有效状态
    CASE
        WHEN ti.interval_end IS NULL THEN 'Y'
        ELSE 'N'
    END AS is_current
FROM time_intervals ti
-- 关联区间内有效的客户记录
LEFT JOIN dt_cust c
    ON c.start_dt <= ti.interval_start
    AND c.end_dt >= COALESCE(ti.interval_end - INTERVAL '1' DAY, DATE '9999-12-31')
-- 关联对应客户的有效地址记录
LEFT JOIN dt_adre a
    ON a.cust_id = c.cust_id
    AND a.start_dt <= ti.interval_start
    AND a.end_dt >= COALESCE(ti.interval_end - INTERVAL '1' DAY, DATE '9999-12-31')
-- 过滤无客户的无效地址记录（若需保留可删除此条件）
WHERE c.cust_id IS NOT NULL
ORDER BY c.cust_id, ti.interval_start;

关键逻辑说明 #

1. 收集变更时间点：通过UNION合并两张表的所有start_dt和end_dt，确保不会遗漏任何客户或地址的变更节点。
2. 生成时间区间：利用LEAD()函数将相邻的变更时间点组合成连续区间，每个区间对应一段客户和地址都未发生变化的稳定状态。
3. 关联有效记录：通过区间起始日匹配客户/地址表中处于有效期内的记录，确保每个区间返回的是该时间段内的真实数据状态。
4. 处理当前有效状态：对最后一个无结束日的区间，将其结束日设为9999-12-31，并标记为当前有效（is_current='Y'）。

注意事项 #

• 若表中日期字段包含时间部分，需用TRUNC()函数截断为纯日期（如TRUNC(start_dt)），避免时间精度导致的匹配错误。
• 若存在同一客户在同一时间点有多条有效记录（数据异常），需先通过ROW_NUMBER()等方式清理数据，确保每个客户在任意时间点仅存一条有效记录。
• 若需保留无客户的地址记录或无地址的客户记录，可调整JOIN类型（如改用FULL JOIN）或删除WHERE过滤条件。

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