计算重叠时间范围内的分钟总和

Question

我需要计算术语的分钟总和。重叠的术语不应多次计算。

Declare @Terms table (Start DATETIME, Finish DATETIME)
INSERT INTO @Terms values
    ('2016-8-3 08:00','2016-8-3 09:00'),
    ('2016-8-3 09:00','2016-8-3 10:00'),
    ('2016-8-3 08:00','2016-8-3 09:30'),   -- overlapping term
    ('2016-8-3 11:00','2016-8-3 12:00')

从上面的数据得出的结果应该是180分钟（3小时）。最简单和最快的方法是什么？

- brenkdar

---

你有尝试过任何事情吗？ - dfundako

@dfundako 不，我真的需要一个提示。当然，你可以尝试提取重叠术语，但是有多种重叠方式，这使得问题变得复杂。 - brenkdar

查找间隙和岛屿。这是一个经典的岛屿情况。 - Sean Lange

@brenkdar - 抱歉，我看到你提到了“多重重叠”问题，所以你知道寻找匹配的开始和结束时间是行不通的。不过尝试一下我的WITH语句，它在有限的时间段内可能会有用，例如一天。 - Cato

@brenkdar - 你的重叠时间是8:00到9:30——但你想将其计算为180分钟吗？所以你想同时计算两个时间段的时间，但不想计算第三个时间段的时间？ - Cato

Answer 1

通过使用CROSS APPLY进行辅助处理。

Declare @Terms table (Start DATETIME, Finish DATETIME)
INSERT INTO @Terms values
    ('2016-8-3 08:00','2016-8-3 09:00'),
    ('2016-8-3 09:00','2016-8-3 10:00'),
    ('2016-8-3 08:00','2016-8-3 09:30'),   -- overlapping term
    ('2016-8-3 11:00','2016-8-3 12:00')

Select Minutes = sum(Minutes)
 From (
        Select Distinct 
               B.DateR1
              ,B.DateR2
              ,Minutes = DateDiff(Minute,B.DateR1,B.DateR2)
         From @Terms A
         Cross Apply (
                      Select DateR1=Min(Start)
                            ,DateR2=max(Finish) 
                       From  @Terms 
                       Where Start <= A.Finish and Finish >= A.Start
                     ) B
 ) A

返回

Minutes
180

子查询返回结果。

DateR1                     DateR2                   Minutes
2016-08-03 08:00:00.000    2016-08-03 10:00:00.000  120
2016-08-03 11:00:00.000    2016-08-03 12:00:00.000  60

- John Cappelletti

---

11:00到12:00与什么时间段重叠？除非我理解错了，因为我看到答案是90。 - Cato

@AndrewDeighton 11-12是独立的。在这种情况下，只有前3行重叠，因此具有不同日期的DateR1，DateR2。 - John Cappelletti

@AndrewDeighton 编辑了我的答案，使 SQL 更易读。 - John Cappelletti

谢谢你，我曾经做过类似的事情，但我不确定他在某些方面的意思，但他显然已经删除了整个问题！ - Cato

不错的解决方案。我只会修改一下where部分以获得重叠（我认为这只是一个外观问题）：Where Start <= A.Finish and Finish >= A.Start。 - Cetin Basoz

Answer 2

您可以执行窗口语句下的LEAD函数来查找重叠部分。我创建了一个名为TIME_TEST的表，并插入了您的值，当我对差异求和时，它返回三个小时。Lead Function Documentation

SELECT SUM(PREV_TIME - END_TIME) FROM (
SELECT *, LEAD(END_TIME) OVER(ORDER BY START_TIME) PREV_TIME
FROM TIME_TEST
ORDER BY 1
)FOO
;

- J_Arthur

Answer 3

DECLARE @MAX as datetime;

SELECT @MAX = MAX(finish) FROM TERMS;

;WITH MINS AS (SELECT MIN(Start) N FROM TERMS
                UNION ALL
                SELECT dateadd(mi, 1, N) FROM MINS WHERE N < @MAX),
      OVERLAPMINS AS (
                        SELECT Mins.N from MINS 
                                            JOIN Terms T 
                                                    ON   T.Start <= MINS.N AND MINS.N < T.Finish 
                                                        GROUP BY Mins.N 
                                                            HAVING COUNT(0) > 1
                        )
      SELECT COUNT(0) totalmins FROM OVERLAPMINS 
option(maxrecursion 0)

除了我得到90，如果重叠的时间段不重复计算，你怎么让它变成3小时呢？你似乎把重叠的部分计算了两次。如果有第三个时间段与1和2个其他时间段在不同时间重叠，那该怎么办？你是说重叠的时间段总是被计算两次（这没问题，因为任何重叠都有>=2个时间段在任意一分钟内重叠），所以你可以将我的计数乘以2。

...SELECT 2 * COUNT(0) totalmins..

如果您想将重叠计为2，但不超过2。

- Cato