如何实现三个Type 2缓慢变化维度关联并按所有起始日期列排序

原写法直接对三个SCD2表做笛卡尔积关联，仅通过row_number过滤每个id+日期的第一条记录，会丢失大量有效维度组合，无法输出所有时间区间的对应属性。

首先提取每个员工所有维度的时间边界点，生成连续的待校验时间区间，再针对每个区间分别匹配三个维度表中同时有效的历史版本，即可得到符合预期的时序结果。

WITH all_dates AS (
    -- 汇总所有维度的时间边界点
    SELECT id, start_date_dep AS dt FROM #Subsidiaries
    UNION
    SELECT id, end_date_dep AS dt FROM #Subsidiaries WHERE end_date_dep IS NOT NULL
    UNION
    SELECT id, start_date_fun AS dt FROM #Functions
    UNION
    SELECT id, end_date_fun AS dt FROM #Functions WHERE end_date_fun IS NOT NULL
    UNION
    SELECT id, end_date_grad AS dt FROM #Graduations
),
date_ranges AS (
    -- 生成每个员工的连续时间区间
    SELECT 
        id,
        dt AS range_start,
        LEAD(dt) OVER(PARTITION BY id ORDER BY dt) AS range_end
    FROM all_dates
)
SELECT 
    dr.id,
    sb.name,
    sb.subsidiary,
    sb.department,
    fc.[function],
    gd.university_graduation,
    dr.range_start,
    -- 可根据需求将当前记录的结束日期替换为null
    CASE WHEN dr.range_end = '9999-12-31' THEN NULL ELSE dr.range_end END AS range_end,
    CASE WHEN dr.range_end IS NULL OR dr.range_end = '9999-12-31' THEN 1 ELSE 0 END AS last_record_flg
FROM date_ranges dr
LEFT JOIN #Subsidiaries sb 
    ON dr.id = sb.id
    AND sb.start_date_dep <= dr.range_start
    AND ISNULL(sb.end_date_dep, '9999-12-31') > dr.range_start
LEFT JOIN #Functions fc
    ON dr.id = fc.id
    AND fc.start_date_fun <= dr.range_start
    AND ISNULL(fc.end_date_fun, '9999-12-31') > dr.range_start
LEFT JOIN #Graduations gd
    ON dr.id = gd.id
    AND gd.end_date_grad <= dr.range_start
    AND ISNULL(LEAD(gd.end_date_grad) OVER(PARTITION BY gd.id ORDER BY gd.end_date_grad), '9999-12-31') > dr.range_start
-- 若不需要员工入职前的空白区间，可开启下面的过滤条件
-- WHERE sb.id IS NOT NULL
ORDER BY dr.id, dr.range_start

执行上述代码会按员工id、时间区间升序排列，每行对应三个维度属性同时有效的时间区间，符合SCD2多表关联的时序输出要求。

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