基于MASTER与ALOG表构建SCD2表的SQL逻辑优化咨询

问题背景 #

现有MASTER主表及记录其增删改操作的ALOG活动日志表，需基于二者构建SCD2（缓慢变化维度2型）表。当前SQL可正确处理日期字段，但其他列值更新逻辑异常，要求在不使用MERGE或UPDATE语句的前提下优化查询，得到正确的历史快照输出。

表结构详情 #

MASTER主表 #

ALOG活动日志表 #

说明：PRIMARY_KEY_BUFFER为BANK_ID与CCY_NUMB_CODE的组合值

预期输出（SCD2表） #

当前异常查询语句 #

SELECT 
    p.BANK_ID, 
    CCY_NUMB_CODE, 
    CCY_ALPHA_CODE,
    (REQUEST_DATE -1) AS ISIN_CLOSE_DATE,
    ISIN_CLOSE_PRICE, 
    REQUEST_DATE AS LAST_MAN_MDFCN, 
    REQUEST_DATE AS START_DATE,
    NEW_VALUE,
    OLD_VALUE,
    DATE_LAST_MDFCN,
    LEAD(REQUEST_DATE -1 ) OVER (PARTITION BY p.BANK_ID, p.CCY_NUMB_CODE ORDER BY REQUEST_DATE) AS END_DATE
    --ROW_NUMBER() OVER (PARTITION BY p.BANK_ID, p.CCY_NUMB_CODE ORDER BY REQUEST_DATE) AS RN
  FROM MASTER p
  LEFT JOIN ALOG l
  ON l.PRIMARY_KEY_BUFFER = p.BANK_ID ||','|| p.CCY_NUMB_CODE
    where
    TABLE_NAME = 'MASTER' AND p.BANK_ID=1 AND p.CCY_NUMB_CODE=9
    order by insert_date

优化后的查询方案 #

逻辑思路 #

1. 从ALOG日志中提取所有操作事件，按REQUEST_DATE排序，优先标记初始插入操作
2. 对每个字段使用LAST_VALUE()窗口函数，按时间回溯获取各时间段的字段值：优先取历史更新的NEW_VALUE，无更新时取日志中的OLD_VALUE，最终 fallback 到主表当前值
3. 生成每个版本的时间区间：START_DATE为操作生效日期，END_DATE为下一次操作日期减1，最后一个版本用31-12-9999作为永久有效日期

优化后的SQL语句 #

WITH log_events AS (
    SELECT 
        BANK_ID,
        REGEXP_SUBSTR(PRIMARY_KEY_BUFFER, '[^,]+', 1, 2) AS CCY_NUMB_CODE,
        REQUEST_DATE,
        ORA_FIELD_NAME,
        NEW_VALUE,
        OLD_VALUE,
        ROW_NUMBER() OVER (PARTITION BY BANK_ID, PRIMARY_KEY_BUFFER ORDER BY REQUEST_DATE, CASE WHEN DESCRIPTION = 'NEW RECORD INSERTED' THEN 0 ELSE 1 END) AS event_seq
    FROM ALOG
    WHERE TABLE_NAME = 'MASTER' 
      AND BANK_ID = 1 
      AND PRIMARY_KEY_BUFFER = '1,9'
),
field_snapshots AS (
    SELECT 
        le.BANK_ID,
        le.CCY_NUMB_CODE,
        m.CCY_ALPHA_CODE,
        -- 回溯ISIN_CLOSE_DATE历史值
        COALESCE(
            LAST_VALUE(CASE WHEN le.ORA_FIELD_NAME = 'ISIN_CLOSE_DATE' THEN le.NEW_VALUE ELSE NULL END IGNORE NULLS) OVER (PARTITION BY le.BANK_ID, le.CCY_NUMB_CODE ORDER BY le.event_seq),
            MAX(CASE WHEN le.ORA_FIELD_NAME = 'ISIN_CLOSE_DATE' THEN le.OLD_VALUE END) OVER (PARTITION BY le.BANK_ID, le.CCY_NUMB_CODE),
            m.ISIN_CLOSE_DATE
        ) AS ISIN_CLOSE_DATE,
        -- 回溯ISIS_CLOSE_PRICE历史值
        COALESCE(
            LAST_VALUE(CASE WHEN le.ORA_FIELD_NAME = 'ISIS_CLOSE_PRICE' THEN le.NEW_VALUE ELSE NULL END IGNORE NULLS) OVER (PARTITION BY le.BANK_ID, le.CCY_NUMB_CODE ORDER BY le.event_seq),
            MAX(CASE WHEN le.ORA_FIELD_NAME = 'ISIS_CLOSE_PRICE' THEN le.OLD_VALUE END) OVER (PARTITION BY le.BANK_ID, le.CCY_NUMB_CODE),
            m.ISIS_CLOSE_PRICE
        ) AS ISIS_CLOSE_PRICE,
        -- 回溯ISIN_STATUS历史值
        COALESCE(
            LAST_VALUE(CASE WHEN le.ORA_FIELD_NAME = 'ISIN_STATUS' THEN le.NEW_VALUE ELSE NULL END IGNORE NULLS) OVER (PARTITION BY le.BANK_ID, le.CCY_NUMB_CODE ORDER BY le.event_seq),
            MAX(CASE WHEN le.ORA_FIELD_NAME = 'ISIN_STATUS' THEN le.OLD_VALUE END) OVER (PARTITION BY le.BANK_ID, le.CCY_NUMB_CODE),
            m.ISIN_STATUS
        ) AS ISIN_STATUS,
        -- 回溯CCY_SHORT_NAME历史值
        COALESCE(
            LAST_VALUE(CASE WHEN le.ORA_FIELD_NAME = 'CCY_SHORT_NAME' THEN le.NEW_VALUE ELSE NULL END IGNORE NULLS) OVER (PARTITION BY le.BANK_ID, le.CCY_NUMB_CODE ORDER BY le.event_seq),
            MAX(CASE WHEN le.ORA_FIELD_NAME = 'CCY_SHORT_NAME' THEN le.OLD_VALUE END) OVER (PARTITION BY le.BANK_ID, le.CCY_NUMB_CODE),
            m.CCY_SHORT_NAME
        ) AS CCY_SHORT_NAME,
        -- 回溯DEL_FLAG历史值
        COALESCE(
            LAST_VALUE(CASE WHEN le.ORA_FIELD_NAME = 'DEL_FLAG' THEN le.NEW_VALUE ELSE NULL END IGNORE NULLS) OVER (PARTITION BY le.BANK_ID, le.CCY_NUMB_CODE ORDER BY le.event_seq),
            MAX(CASE WHEN le.ORA_FIELD_NAME = 'DEL_FLAG' THEN le.OLD_VALUE END) OVER (PARTITION BY le.BANK_ID, le.CCY_NUMB_CODE),
            m.DEL_FLAG
        ) AS DEL_FLAG,
        le.REQUEST_DATE AS LAST_MAN_MDFCN,
        le.REQUEST_DATE AS START_DATE,
        le.event_seq
    FROM log_events le
    CROSS JOIN MASTER m
    WHERE m.BANK_ID = le.BANK_ID 
      AND m.CCY_NUMB_CODE = le.CCY_NUMB_CODE
    UNION ALL
    -- 新增初始状态虚拟行，还原插入后的第一个版本
    SELECT 
        m.BANK_ID,
        m.CCY_NUMB_CODE,
        m.CCY_ALPHA_CODE,
        MAX(CASE WHEN le.ORA_FIELD_NAME = 'ISIN_CLOSE_DATE' THEN le.OLD_VALUE END) AS ISIN_CLOSE_DATE,
        MAX(CASE WHEN le.ORA_FIELD_NAME = 'ISIS_CLOSE_PRICE' THEN le.OLD_VALUE END) AS ISIS_CLOSE_PRICE,
        MAX(CASE WHEN le.ORA_FIELD_NAME = 'ISIN_STATUS' THEN le.OLD_VALUE END) AS ISIN_STATUS,
        MAX(CASE WHEN le.ORA_FIELD_NAME = 'CCY_SHORT_NAME' THEN le.OLD_VALUE END) AS CCY_SHORT_NAME,
        MAX(CASE WHEN le.ORA_FIELD_NAME = 'DEL_FLAG' THEN le.OLD_VALUE END) AS DEL_FLAG,
        MIN(le.REQUEST_DATE) AS LAST_MAN_MDFCN,
        MIN(le.REQUEST_DATE) AS START_DATE,
        0 AS event_seq
    FROM log_events le
    CROSS JOIN MASTER m
    WHERE m.BANK_ID = le.BANK_ID 
      AND m.CCY_NUMB_CODE = le.CCY_NUMB_CODE
      AND EXISTS (SELECT 1 FROM log_events le2 WHERE le2.BANK_ID = le.BANK_ID AND le2.PRIMARY_KEY_BUFFER = le.PRIMARY_KEY_BUFFER AND le2.DESCRIPTION = 'NEW RECORD INSERTED')
    GROUP BY m.BANK_ID, m.CCY_NUMB_CODE, m.CCY_ALPHA_CODE
),
final_snapshots AS (
    SELECT 
        BANK_ID,
        CCY_NUMB_CODE,
        CCY_ALPHA_CODE,
        ISIN_CLOSE_DATE,
        ISIS_CLOSE_PRICE,
        ISIN_STATUS,
        CCY_SHORT_NAME,
        DEL_FLAG,
        LAST_MAN_MDFCN,
        START_DATE,
        COALESCE(LEAD(START_DATE - 1) OVER (PARTITION BY BANK_ID, CCY_NUMB_CODE ORDER BY event_seq), TO_DATE('31-12-9999', 'DD-MM-YYYY')) AS END_DATE
    FROM field_snapshots
)
SELECT DISTINCT
    BANK_ID,
    CCY_NUMB_CODE,
    CCY_ALPHA_CODE,
    ISIN_CLOSE_DATE,
    ISIS_CLOSE_PRICE,
    ISIN_STATUS,
    CCY_SHORT_NAME,
    DEL_FLAG,
    LAST_MAN_MDFCN,
    START_DATE,
    END_DATE
FROM final_snapshots
ORDER BY START_DATE;

关键说明 #

• 用WITH子句拆分逻辑，分步整理日志事件、生成字段快照、计算时间区间，可读性更强
• 通过LAST_VALUE()实现字段值的时间回溯，确保每个时间段的字段状态准确
• 新增初始状态虚拟行，补全记录插入后的第一个版本快照
• 用DISTINCT去除重复行，保证每个时间区间仅保留一条有效记录

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