SQL Server历史表数据清洗：构建精准的StartDate/EndDate历史记录

问题分析 #

需要合并连续时段内同一产品、相同价格的记录，但不能将被不同价格时段分隔开的相同价格记录错误合并。此前的错误合并是直接按产品和价格聚合，忽略了时段的连续性，导致非连续的相同价格记录被错误合并成一条。

解决方案 #

使用窗口函数LAG()识别价格变化的分界点，通过累计求和生成连续价格组的唯一ID，最终按组聚合得到精准的历史记录。

完整SQL代码 #

WITH RankedData AS (
    -- 标记价格变化的分界点
    SELECT 
        Product,
        ProductPrice,
        StartDate,
        EndDate,
        -- 当前价格与上一条不同时标记为新组起点
        CASE 
            WHEN LAG(ProductPrice) OVER (PARTITION BY Product ORDER BY StartDate) <> ProductPrice
            THEN 1
            ELSE 0
        END AS PriceChangeFlag
    FROM YourOriginalTable
),
GroupedData AS (
    -- 生成连续价格组的ID
    SELECT 
        Product,
        ProductPrice,
        StartDate,
        EndDate,
        SUM(PriceChangeFlag) OVER (PARTITION BY Product ORDER BY StartDate ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS PriceGroupId
    FROM RankedData
)
-- 按组聚合得到最终结果
SELECT 
    Product,
    ProductPrice,
    MIN(StartDate) AS StartDate,
    MAX(EndDate) AS EndDate
FROM GroupedData
GROUP BY Product, ProductPrice, PriceGroupId
ORDER BY Product, StartDate;

代码说明 #

1. RankedData 公用表表达式：
   • 按产品分组、开始日期排序，通过LAG()函数获取上一条记录的产品价格。
   • 当当前价格与上一条不同时，PriceChangeFlag设为1，标记为新价格组的起点。
2. GroupedData 公用表表达式：
   • 对PriceChangeFlag做累计求和，生成PriceGroupId，同一连续价格时段的记录会拥有相同的ID。
3. 最终聚合查询：
   • 按产品、价格和组ID分组，取组内最小的开始日期和最大的结束日期，得到连续时段的合并记录。

执行结果 #

针对你的示例数据，执行上述SQL后将得到预期结果：

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