主页 / dba / 问题 / 333229
Accepted
J.D.
Asked: 2023-11-18 01:36:46 +0800 CST

如何总结事件的不同总时间，忽略时间中的重复重叠？

  • 772

我有下EventTimes表：

DROP TABLE IF EXISTS dbo.EventTimes;
CREATE TABLE dbo.EventTimes
(
  EventTimeKeyId INT IDENTITY(1,1) PRIMARY KEY,
  EventId INT NOT NULL,
  StartTime TIME NOT NULL,
  EndTime TIME NOT NULL
);

具有以下数据：

-- Event 1
INSERT INTO dbo.EventTimes (EventId, StartTime, EndTime)
VALUES 
  (1, '04:00:00', '14:00:00'), 
  (1, '06:00:00', '11:00:00'), 
  (1, '09:00:00', '12:00:00'), 
  (1, '13:00:00', '14:00:00'), -- Gap between this row and the next row
  (1, '02:30:00', '04:00:00'); -- Notice the half-hour on this one

-- Event 2
INSERT INTO dbo.EventTimes (EventId, StartTime, EndTime)
VALUES 
  (2, '00:00:00', '06:00:00'), -- Gap between this row and the next row
  (2, '09:00:00', '13:00:00'), 
  (2, '11:00:00', '15:00:00');

注意：

  • 同一Event时间范围可以有两个相互重叠的时间范围。例如，Event从凌晨 4 点到下午 2 点以及早上 6 点到上午 11 点也是如此。
  • 两个时间范围之间也可能存在间隙。例如Event，下午 1 点至 2 点以及下午 2:30 至 4 点也是如此。

最终目标：

我正在尝试计算给TotalTime定的Event忽略重复的重叠时间。例如，对于上午 9 点到下午 1 点和上午 11 点到下午 3 点的范围集，不同的TotalTime时间应该是 6 小时（上午 9 点到下午 3 点）。相反，我也不想计算两个时间范围之间的间隙时间。因此，对于下午 1 点到 2 点以及下午 2:30 到 4 点的范围集，TotalTime应该是 2.5 小时。（请注意，这些只是上面完整示例的子集，最终结果应该是每个 的所有这些唯一时间的总和Event。）

不应TotalTime超过 24 小时，这些时间都在一天之内（TIME数据类型也是如此）。

上面脚本中提供的示例的预期最终结果：

示例最终结果

dbfiddle.uk 供参考。

其他信息：

  • 如果使用日期时间更容易，请随意将数据类型从 更改TIME为DATETIME。我可以将结果转换回来，没问题。

  • 我相信需要某种递归来解决这个问题。我觉得我已经非常接近解决方案，但还没有完全实现。

在 2:30 - 4:00 和 4:00 - 14:00 等情况下，我预计总时间为 11.5 小时。

sql-server

2 个回答

  1. Best Answer

    所有的复杂性都是由重叠的间隔造成的。

    如果我们打包间隔以使其不存在重叠，则解决方案就是简单的分组和求和。

    以下代码使用 Itzik Ben-Gan 的间隔打包解决方案：

    打包间隔是一个经典的 T-SQL 问题，涉及将相交间隔组打包到各自的连续间隔中。我给自己设定了一个挑战，尝试找到一种优雅的解决方案，只需使用一个支持索引和一次数据扫描即可完成任务，我找到了一个。

    -- Itzik Ben-Gan's interval packing solution
-- https://www.itprotoday.com/sql-server/new-solution-packing-intervals-problem
WITH 
    C1 AS
    (
        SELECT 
            *,
            prvend = MAX(ET.EndTime) OVER (
                PARTITION BY ET.EventId
                ORDER BY ET.StartTime, ET.EndTime
                ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING)
      FROM dbo.EventTimes AS ET
    ),
    C2 AS
    (
        SELECT 
            *,
            grp = SUM(isstart) OVER (
                PARTITION BY C1.EventId
                ORDER BY C1.StartTime, C1.EndTime
                ROWS UNBOUNDED PRECEDING)
        FROM C1
        CROSS APPLY
        (
            VALUES
            (
                CASE 
                    WHEN C1.StartTime <= C1.prvend 
                    THEN NULL 
                    ELSE 1 
                END
            )
        ) AS A (isstart)
    ),
    Packed AS
    (
        SELECT 
            C2.EventId, 
            StartTime = MIN(C2.StartTime), 
            EndTime = MAX(C2.EndTime)
        FROM C2
        GROUP BY 
            C2.EventId, 
            C2.grp
    )
-- Sum the packed intervals by EventId
SELECT 
    P.EventId, 
    TotalTime = SUM(DATEDIFF(MINUTE, P.StartTime, P.EndTime)) / 60.0
FROM Packed AS P
GROUP BY 
    P.EventId;
    事件ID 总时间
    1 11.500000
    2 12.000000

    使用 上的索引可以避免初始排序(EventId, StartTime, EndTime)。

    • 3

  2. 这是一个使用运行长度编码来压缩重叠时间范围的解决方案 -

    模式和期望的输出

    DROP TABLE IF EXISTS dbo.EventTimes;
CREATE TABLE dbo.EventTimes
(
  EventTimeKeyId INT IDENTITY(1,1) PRIMARY KEY,
  EventId INT NOT NULL,
  StartTime TIME NOT NULL,
  EndTime TIME NOT NULL
);

-- Event 1
INSERT INTO dbo.EventTimes (EventId, StartTime, EndTime)
VALUES 
  (1, '04:00:00', '14:00:00'), 
  (1, '06:00:00', '11:00:00'), 
  (1, '09:00:00', '12:00:00'), 
  (1, '13:00:00', '14:00:00'), -- Gap between this row and the next row
  (1, '02:30:00', '04:00:00'); -- Notice the half-hour on this one

-- Event 2
INSERT INTO dbo.EventTimes (EventId, StartTime, EndTime)
VALUES 
  (2, '00:00:00', '06:00:00'), -- Gap between this row and the next row
  (2, '09:00:00', '13:00:00'), 
  (2, '11:00:00', '15:00:00');

-- Final Results expected for these 2 examples
SELECT 1 AS EventId, 11.5 AS TotalTime
UNION ALL
SELECT 2 AS EventId, 12 AS TotalTime;
    事件ID 总时间
    1 11.5
    2 12.0

    解决方案

    WITH x
AS
(
    SELECT EventID
         , StartTime
         , EndTime
         , IIF(
               starttime <= MAX(EndTime) OVER (PARTITION BY EventID
                                               ORDER BY StartTime
                                               ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING
                                              )
               OR endtime >= MIN(starttime) OVER (PARTITION BY EventID
                                                  ORDER BY StartTime
                                                  ROWS BETWEEN 1 FOLLOWING AND UNBOUNDED FOLLOWING
                                                 )
             , 1
             , 0) AS grp
    FROM dbo.EventTimes
)
   , j
AS
(
    SELECT EventID
         , MIN(starttime) AS stID
         , MAX(endtime) AS eID
    FROM x
    WHERE x.grp = 1
    GROUP BY x.EventID
           , x.grp
    UNION ALL
    SELECT x.EventID
         , x.starttime AS stID
         , x.endtime AS eID
    FROM x
    WHERE x.grp = 0
)
SELECT EventID
     , CAST(SUM(DATEDIFF(mi, j.stID, j.eID)) AS DECIMAL(5)) / 60 AS EventTime
FROM j
GROUP BY EventID;
    事件ID 事件时间
    1 11.500000
    2 12.000000

    DB Fiddle 供参考

    • 2

相关问题