如何编写SQL提取事件的多次开闭时间区间？

核心逻辑拆解 #

先把需求里的规则转成SQL可执行的逻辑：

• 每个事件的修订记录必须按rev_num排序，这是唯一能确定修订顺序的字段
• 把所有ClosedDateTime非空的行标记为「关闭点」，这些点是拆分活跃区间的关键
• 按关闭点拆分区间：
  • 第一个区间：从事件最早的修订时间（最小rev_num对应的时间），到第一个关闭点的上一条修订时间
  • 中间区间：每个关闭点的下一条修订时间作为下一个区间的开启时间，到下一个关闭点的上一条修订时间
  • 最后一个区间：如果最后一条记录不是关闭点，就从最后一个关闭点的下一条修订时间，到最新的修订时间；如果最后一条是关闭点，最新修订时间就是最终关闭时间

具体SQL代码实现 #

假设你的事件表名为EventTable，字段包括EventID（事件编号）、RevDateTime（修订时间）、rev_num（修订号）、ClosedDateTime（关闭时间），以下代码可直接生成结果存入表变量@CEvents：

-- 声明存储结果的表变量
DECLARE @CEvents TABLE (
    EventID INT,
    CreatedOrReopenedDT DATETIME,
    ClosedDT DATETIME
);

WITH EventRevisions AS (
    -- 给每个事件的修订记录排序，同时计算前后记录时间、标记是否为关闭点
    SELECT 
        EventID,
        RevDateTime,
        rev_num,
        ClosedDateTime,
        ROW_NUMBER() OVER (PARTITION BY EventID ORDER BY rev_num) AS RowNum,
        COUNT(*) OVER (PARTITION BY EventID) AS TotalRevs,
        LEAD(RevDateTime) OVER (PARTITION BY EventID ORDER BY rev_num) AS NextRevDT,
        LAG(RevDateTime) OVER (PARTITION BY EventID ORDER BY rev_num) AS PrevRevDT,
        CASE WHEN ClosedDateTime IS NOT NULL THEN 1 ELSE 0 END AS IsClosePoint
    FROM EventTable
),
ClosePoints AS (
    -- 提取所有关闭点的关键信息：上一条时间为区间关闭时间，下一条时间为下一个区间的开启时间
    SELECT 
        EventID,
        RowNum,
        RevDateTime AS ClosePointDT,
        PrevRevDT AS ClosedDT,
        NextRevDT AS NextOpenDT
    FROM EventRevisions
    WHERE IsClosePoint = 1
),
EventActivePeriods AS (
    -- 生成第一个活跃区间：从最早修订时间到第一个关闭点的上一条时间
    SELECT 
        er.EventID,
        er.RevDateTime AS CreatedOrReopenedDT,
        cp.ClosedDT
    FROM EventRevisions er
    LEFT JOIN ClosePoints cp 
        ON er.EventID = cp.EventID 
        AND cp.RowNum = (SELECT MIN(RowNum) FROM ClosePoints WHERE EventID = er.EventID)
    WHERE er.RowNum = 1
    UNION ALL
    -- 生成中间活跃区间：每个关闭点之后的开启到下一个关闭点的上一条时间
    SELECT 
        cp1.EventID,
        cp1.NextOpenDT AS CreatedOrReopenedDT,
        cp2.ClosedDT
    FROM ClosePoints cp1
    JOIN ClosePoints cp2 
        ON cp1.EventID = cp2.EventID 
        AND cp2.RowNum = (SELECT MIN(RowNum) FROM ClosePoints WHERE EventID = cp1.EventID AND RowNum > cp1.RowNum)
    UNION ALL
    -- 生成最后一个活跃区间：处理无关闭点或最后一个关闭点后的剩余记录
    SELECT 
        er.EventID,
        CASE 
            WHEN (SELECT MAX(RowNum) FROM ClosePoints WHERE EventID = er.EventID) IS NULL THEN er.RevDateTime
            ELSE (SELECT NextOpenDT FROM ClosePoints WHERE EventID = er.EventID AND RowNum = (SELECT MAX(RowNum) FROM ClosePoints WHERE EventID = er.EventID))
        END AS CreatedOrReopenedDT,
        er.RevDateTime AS ClosedDT
    FROM EventRevisions er
    WHERE er.RowNum = er.TotalRevs
)
-- 插入所有有效区间到表变量
INSERT INTO @CEvents (EventID, CreatedOrReopenedDT, ClosedDT)
SELECT 
    EventID,
    CreatedOrReopenedDT,
    ClosedDT
FROM EventActivePeriods
WHERE CreatedOrReopenedDT IS NOT NULL;

-- 示例：查询指定时间段内的活跃事件，替换@StartDT和@EndDT为你的参数
-- SELECT * FROM @CEvents
-- WHERE CreatedOrReopenedDT <= @EndDT AND ClosedDT >= @StartDT;

优化建议 #

• 索引优化：给EventTable创建复合索引IX_EventTable_EventID_rev_num，包含ClosedDateTime和RevDateTime字段，大幅提升窗口函数的计算效率
• 增量处理：数据量较大时，避免全表扫描，只处理新增的事件或修订记录
• 边界校验：补充逻辑处理仅单条记录的场景（比如事件刚创建未修订，此时开启和关闭时间为同一条记录的时间）
• 性能调优：超大规模数据下，用临时表替代CTE，或拆分步骤分步执行，降低内存占用

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