如何在SQL Server中将带时间戳的日志表转换为有效区间表？

针对10万+条记录的日志表，最优方案是利用SQL Server的窗口函数结合合理的索引设计，避免低效的循环或自连接操作，确保处理性能。

核心实现代码 #

WITH LogSorted AS (
    SELECT 
        RecordId,
        DateStamp,
        OldValue,
        NewValue,
        -- 获取下一条变更日期，用于计算当前状态的截止时间
        LEAD(DateStamp) OVER (PARTITION BY RecordId ORDER BY DateStamp) AS NextDateStamp,
        -- 标记当前RecordId的第一条日志，用于提取初始状态
        ROW_NUMBER() OVER (PARTITION BY RecordId ORDER BY DateStamp) AS RowNum
    FROM Log
),
AllStates AS (
    -- 加载初始状态：每个RecordId的第一个OldValue
    SELECT 
        RecordId,
        OldValue AS Value,
        CAST('1900-01-01' AS DATE) AS ValidFromDate,
        DATEADD(DAY, -1, DateStamp) AS ValidToDate
    FROM LogSorted
    WHERE RowNum = 1

    UNION ALL

    -- 加载所有变更后的状态：每条日志的NewValue
    SELECT 
        RecordId,
        NewValue AS Value,
        DateStamp AS ValidFromDate,
        -- 最后一条状态的截止日期设为9999-12-31，否则为下一次变更的前一天
        CASE 
            WHEN NextDateStamp IS NULL THEN CAST('9999-12-31' AS DATE)
            ELSE DATEADD(DAY, -1, NextDateStamp)
        END AS ValidToDate
    FROM LogSorted
)
SELECT 
    RecordId,
    Value,
    ValidFromDate,
    ValidToDate
FROM AllStates
ORDER BY RecordId, ValidFromDate;

代码逻辑说明 #

1. LogSorted CTE：

   • 按RecordId分组、DateStamp排序，用LEAD函数提前获取下一次变更的日期，省去后续自连接查询的开销。
   • 用ROW_NUMBER标记每个分组的第一条记录，用于提取初始状态。

2. AllStates CTE：

   • 第一部分提取每个RecordId的初始状态，起始日期设为1900-01-01，截止日期为第一次变更的前一天。
   • 第二部分提取所有变更后的状态，起始日期为变更当日，截止日期根据是否为最后一次变更，分别设为9999-12-31或下一次变更的前一天。
   • 使用UNION ALL而非UNION，避免不必要的去重操作，提升性能。

性能优化建议 #

为了适配10万+数据量的高效处理，必须创建复合覆盖索引：

CREATE NONCLUSTERED INDEX IX_Log_RecordId_DateStamp 
ON Log (RecordId, DateStamp) 
INCLUDE (OldValue, NewValue);

该索引可以让窗口函数直接利用有序的索引数据，避免额外的排序操作，大幅降低查询的CPU和IO开销。

示例输出 #

针对提供的测试数据，输出结果如下：

内容的提问来源于stack exchange，提问作者Garamvölgyi Mihály