基于单日期列生成日期范围：忽略指定列构建SCD Type2

需要将表MITMAS转换为SCD Type2维度表，要求按MMITNO分组，仅追踪MMGRP2、MMGRP3、MMSTAT这三列的变化，忽略MMTST和MMASDF列的变动。

现有表结构及数据 #

CREATE TABLE tmp.MITMAS (
  MMITNO INT,
  MMSTAT INT,
  MMGRP2 VARCHAR(1),
  MMGRP3 VARCHAR(1),
  MMTST VARCHAR(2),
  MMASDF VARCHAR(2),
  ETL_Date DATE
);

INSERT INTO tmp.mitmas (MMITNO, MMSTAT, MMGRP2, MMGRP3, MMTST, MMASDF, ETL_Date)
VALUES 
(333556, 5, 'X', 'A', 'T1', 'H1', '2023-02-01'),
(333556, 5, 'X', 'A', 'T2', 'H1', '2023-02-02'),
(333556, 5, 'X', 'A', 'T1', 'H1', '2023-02-03'),
(333556, 5, 'Y', 'A', 'T2', 'H2', '2023-02-04'),
(333556, 6, 'Y', 'A', 'T1', 'H2', '2023-02-05'),
(333556, 6, 'Y', 'A', 'T1', 'H2', '2023-02-06'),
(333556, 6, 'Y', 'A', 'T1', 'H2', '2023-02-07'),
(324261, 5, 'X', 'A', 'T1', 'H1', '2023-02-01'),
(324261, 5, 'Y', 'A', 'T1', 'H2', '2023-02-02'),
(324261, 5, 'Y', 'A', 'T2', 'H2', '2023-02-03'),
(324261, 5, 'Y', 'A', 'T2', 'H1', '2023-02-04'),
(324261, 5, 'Y', 'B', 'T2', 'H2', '2023-02-05'),
(324261, 5, 'X', 'B', 'T1', 'H2', '2023-02-06'),
(324261, 5, 'X', 'B', 'T2', 'H1', '2023-02-07'),
(324261, 6, 'X', 'B', 'T2', 'H2', '2023-02-08'),
(324261, 6, 'X', 'B', 'T2', 'H2', '2023-02-09');

预期输出的SCD Type2表结构及数据 #

CREATE TABLE tmp.MITMAS_SCD (
  MMITNO INT NOT NULL,
  MMSTAT INT NOT NULL,
  MMGRP2 VARCHAR(50) NOT NULL,
  MMGRP3 VARCHAR(50) NOT NULL,
  StartDate DATE NOT NULL,
  EndDate DATE NOT NULL
);
INSERT INTO tmp.MITMAS_SCD (MMITNO, MMSTAT, MMGRP2, MMGRP3, StartDate, EndDate)
VALUES 
(333556, 5, 'X', 'A', '2023-02-01', '2023-02-04'),
(333556, 5, 'Y', 'A', '2023-02-04', '2023-02-05'),
(333556, 6, 'Y', 'A', '2023-02-05', '9999-12-31'),
(324261, 5, 'X', 'A', '2023-02-01', '2023-02-02'),
(324261, 5, 'Y', 'A', '2023-02-02', '2023-02-05'),
(324261, 5, 'Y', 'B', '2023-02-05', '2023-02-06'),
(324261, 5, 'X', 'B', '2023-02-06', '9999-12-31');

尝试的SQL及问题 #

当前尝试的SQL会把MMTST和MMASDF的变动也当作需要追踪的变更，生成过多不必要的时间区间：

SELECT
  MM.MMITNO,
  MM.MMSTAT,
  MM.MMGRP2,
  MM.MMGRP3,
  MM.ETL_Date AS StartDate,
  COALESCE(LEAD(MM.ETL_Date) OVER (PARTITION BY MM.MMITNO ORDER BY MM.ETL_Date), '9999-12-31') AS EndDate
FROM tmp.MITMAS MM;

解决方案 #

可以通过两种方式实现需求，核心是先过滤掉关键列无变化的重复行，再计算时间区间：

方法一：分组去重后计算区间 #

先按MMITNO、MMSTAT、MMGRP2、MMGRP3分组，取每个版本的最早日期作为开始日期，再通过LEAD获取下一个版本的开始日期作为当前版本的结束日期：

WITH deduplicated_data AS (
    SELECT 
        MMITNO,
        MMSTAT,
        MMGRP2,
        MMGRP3,
        MIN(ETL_Date) AS StartDate
    FROM tmp.MITMAS
    GROUP BY MMITNO, MMSTAT, MMGRP2, MMGRP3
    ORDER BY MMITNO, StartDate
),
ranked_data AS (
    SELECT 
        *,
        LEAD(StartDate) OVER (PARTITION BY MMITNO ORDER BY StartDate) AS NextStartDate
    FROM deduplicated_data
)
SELECT 
    MMITNO,
    MMSTAT,
    MMGRP2,
    MMGRP3,
    StartDate,
    COALESCE(NextStartDate, '9999-12-31') AS EndDate
FROM ranked_data;

方法二：标记变更行后过滤 #

通过LAG函数对比前后行的关键列组合，只保留首次出现或发生变更的行，再计算时间区间：

WITH flagged_changes AS (
    SELECT 
        MMITNO,
        MMSTAT,
        MMGRP2,
        MMGRP3,
        ETL_Date,
        -- 标记当前行与上一行关键列是否不同
        CASE 
            WHEN LAG(CONCAT(MMSTAT, MMGRP2, MMGRP3)) OVER (PARTITION BY MMITNO ORDER BY ETL_Date) 
                 != CONCAT(MMSTAT, MMGRP2, MMGRP3)
            THEN 1
            ELSE 0
        END AS is_change,
        -- 标记分组内的第一行
        ROW_NUMBER() OVER (PARTITION BY MMITNO ORDER BY ETL_Date) AS rn
    FROM tmp.MITMAS
),
filtered_data AS (
    SELECT 
        MMITNO,
        MMSTAT,
        MMGRP2,
        MMGRP3,
        ETL_Date AS StartDate
    FROM flagged_changes
    WHERE is_change = 1 OR rn = 1
)
SELECT 
    MMITNO,
    MMSTAT,
    MMGRP2,
    MMGRP3,
    StartDate,
    COALESCE(LEAD(StartDate) OVER (PARTITION BY MMITNO ORDER BY StartDate), '9999-12-31') AS EndDate
FROM filtered_data;

两种方法都能得到符合预期的SCD Type2结果，忽略MMTST和MMASDF的无关变动。

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