缓慢变化维度数据去重问题及SQL查询优化需求

需要将缓慢变化维度（Slowly Changing Dimension）数据去重合并，把连续具有相同Value1、Value2的记录合并为单一时间区间。实际数据包含更多ID与属性值，希望避免使用#Dates辅助表。当前SQL查询无法得到预期结果，以下是问题复现、当前尝试及期望输出：

问题复现代码 #

IF OBJECT_ID(N'tempdb..#Dates') IS NOT NULL DROP TABLE #Dates;
IF OBJECT_ID(N'tempdb..#Haves') IS NOT NULL DROP TABLE #Haves;
IF OBJECT_ID(N'tempdb..#Wants') IS NOT NULL DROP TABLE #Wants;

DECLARE @FromDate DATETIME, @ToDate DATETIME;
SET @FromDate = '2020-01-01';
SET @ToDate = '2020-01-31';

-- 生成日期辅助表（希望避免使用）
SELECT TOP (DATEDIFF(DAY, @FromDate, @ToDate)+1)
  TheDate = DATEADD(DAY, number, @FromDate)
INTO #Dates
FROM [master].dbo.spt_values
WHERE [type] = N'P' ORDER BY number;

-- 原始数据
SELECT
    *
INTO #Haves
FROM (SELECT 1 ID, '2020-01-01' AS StartDate, '2020-01-03' AS EndDate, 1 Value1, 1 Value2
      UNION
      SELECT 1 ID, '2020-01-03' AS StartDate, '2020-01-05' AS EndDate, 1 Value1, 1 Value2
      UNION
      SELECT 1 ID, '2020-01-05' AS StartDate, '2020-01-07' AS EndDate, 3 Value1, 1 Value2
      UNION
      SELECT 1 ID, '2020-01-07' AS StartDate, '2999-01-01' AS EndDate, 1 Value1, 1 Value2
) AS IQ1;

-- 当前尝试的查询（结果不符合预期）
SELECT
    ID
    , Value1
    , Value2
    , MIN(#Dates.TheDate) AS StartDate
    , MAX(#Dates.TheDate) AS EndDate
FROM #Dates
INNER JOIN #Haves ON #Dates.TheDate BETWEEN #Haves.StartDate AND #Haves.EndDate
GROUP BY
    ID
    , Value1
    , Value2

-- 期望输出数据
SELECT
    *
INTO #Wants
FROM (SELECT 1 ID, '2020-01-01' AS StartDate, '2020-01-05' AS EndDate, 1 Value1, 1 Value2
      UNION
      SELECT 1 ID, '2020-01-05' AS StartDate, '2020-01-07' AS EndDate, 3 Value1, 1 Value2
      UNION
      SELECT 1 ID, '2020-01-07' AS StartDate, '2999-01-01' AS EndDate, 1 Value1, 1 Value2
) AS IQ1;

原始数据 #

期望输出 #

解决方案（无需日期辅助表） #

使用窗口函数标记属性变化并分组，实现连续相同属性记录的合并：

WITH AttributeChanges AS (
    SELECT 
        ID,
        StartDate,
        EndDate,
        Value1,
        Value2,
        -- 标记当前行与上一行属性是否不同
        CASE 
            WHEN LAG(Value1) OVER (PARTITION BY ID ORDER BY StartDate) = Value1 
                 AND LAG(Value2) OVER (PARTITION BY ID ORDER BY StartDate) = Value2 
            THEN 0 
            ELSE 1 
        END AS IsAttributeChanged
    FROM #Haves
),
GroupedRecords AS (
    SELECT 
        *,
        -- 累计求和生成分组ID，连续相同属性的记录归为同一组
        SUM(IsAttributeChanged) OVER (PARTITION BY ID ORDER BY StartDate ROWS UNBOUNDED PRECEDING) AS GroupID
    FROM AttributeChanges
)
SELECT 
    ID,
    MIN(StartDate) AS StartDate,
    MAX(EndDate) AS EndDate,
    Value1,
    Value2
FROM GroupedRecords
GROUP BY ID, GroupID, Value1, Value2
ORDER BY ID, StartDate;

逻辑说明 #

1. AttributeChanges：用LAG函数对比当前行与前一行的Value1、Value2，标记属性是否发生变化；
2. GroupedRecords：通过累计求和SUM(IsAttributeChanged)生成分组ID，连续相同属性的记录会被分配到同一个分组；
3. 最终分组聚合：按ID、分组ID、属性值分组，取每组的最小StartDate和最大EndDate，得到合并后的连续时间区间记录。

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