如何在SQL中合并/拆分停机时间并按规则计算有效时长？

转换规则 #

• 两台机器（ID：333、334）同时停机时，该时段时长按100%计入，用machineID=999标识
• 仅单台机器停机时，该时段时长按50%计入

原始数据定义 #

DECLARE @Data TABLE (DTID integer, machineID integer, startTime datetime, endTime datetime, duration float)

INSERT INTO @Data (DTID, machineID, startTime, endTime, duration)
VALUES
(1,333,'2023-09-21 11:00:00','2023-09-21 11:15:00',15.0),
(2,334,'2023-09-21 11:05:00','2023-09-21 11:10:00',5.0),
(3,333,'2023-09-21 11:17:00','2023-09-21 11:18:00',1.0),
(4,334,'2023-09-21 11:16:00','2023-09-21 11:20:00',4.0)

目标转换结果 #

INSERT INTO @Data (DTID, machineID, startTime, endTime, duration)
VALUES
(1,333,'2023-09-21 11:00:00','2023-09-21 11:05:00',2.5),
(2,999,'2023-09-21 11:05:00','2023-09-21 11:10:00',5.0),
(3,333,'2023-09-21 11:10:00','2023-09-21 11:15:00',2.5),
(4,334,'2023-09-21 11:16:00','2023-09-21 11:17:00',0.5),
(5,999,'2023-09-21 11:17:00','2023-09-21 11:18:00',1.0),
(6,334,'2023-09-21 11:18:00','2023-09-21 11:20:00',1.0)

实现SQL逻辑 #

以下是基于SQL Server的实现代码，核心思路是提取所有关键时间点生成连续时间区间，再判断每个区间内的停机机器数量，最后按规则计算时长：

WITH AllTimePoints AS (
    -- 提取所有停机事件的开始、结束时间作为区间拆分节点
    SELECT startTime AS TimePoint FROM @Data
    UNION
    SELECT endTime AS TimePoint FROM @Data
),
OrderedTimePoints AS (
    -- 对时间点排序，生成连续的时间区间
    SELECT 
        TimePoint,
        LEAD(TimePoint) OVER (ORDER BY TimePoint) AS NextTimePoint
    FROM AllTimePoints
),
ValidIntervals AS (
    -- 过滤无效空区间，计算区间分钟数
    SELECT 
        TimePoint AS IntervalStart,
        NextTimePoint AS IntervalEnd,
        DATEDIFF(MINUTE, TimePoint, NextTimePoint) AS IntervalMinutes
    FROM OrderedTimePoints
    WHERE NextTimePoint IS NOT NULL AND DATEDIFF(MINUTE, TimePoint, NextTimePoint) > 0
),
IntervalMachineStatus AS (
    -- 判断每个区间内两台机器的停机状态
    SELECT
        vi.IntervalStart,
        vi.IntervalEnd,
        vi.IntervalMinutes,
        CASE WHEN EXISTS (
            SELECT 1 FROM @Data d 
            WHERE d.machineID = 333 
            AND d.startTime <= vi.IntervalStart 
            AND d.endTime >= vi.IntervalEnd
        ) THEN 1 ELSE 0 END AS Is333Down,
        CASE WHEN EXISTS (
            SELECT 1 FROM @Data d 
            WHERE d.machineID = 334 
            AND d.startTime <= vi.IntervalStart 
            AND d.endTime >= vi.IntervalEnd
        ) THEN 1 ELSE 0 END AS Is334Down
    FROM ValidIntervals vi
),
FinalResult AS (
    -- 按规则生成最终结果
    SELECT
        ROW_NUMBER() OVER (ORDER BY IntervalStart) AS DTID,
        CASE 
            WHEN Is333Down = 1 AND Is334Down = 1 THEN 999
            WHEN Is333Down = 1 THEN 333
            WHEN Is334Down = 1 THEN 334
        END AS machineID,
        IntervalStart AS startTime,
        IntervalEnd AS endTime,
        CASE 
            WHEN Is333Down = 1 AND Is334Down = 1 THEN IntervalMinutes * 1.0
            ELSE IntervalMinutes * 0.5
        END AS duration
    FROM IntervalMachineStatus
    WHERE Is333Down = 1 OR Is334Down = 1 -- 仅保留有机器停机的区间
)
-- 查询结果（如需插入目标表可替换为INSERT语句）
SELECT * FROM FinalResult;

逻辑说明 #

1. AllTimePoints：收集所有停机事件的时间节点，为拆分区间做准备
2. OrderedTimePoints：通过LEAD函数将排序后的时间点拼接成连续区间
3. ValidIntervals：过滤掉无时长的无效区间，计算每个有效区间的分钟数
4. IntervalMachineStatus：检查每个区间内333、334机器是否处于停机状态
5. FinalResult：根据停机状态分配对应的machineID，按规则计算时长，生成最终结果

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