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

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小时)。最简单、最快的方法是什么？

Answer 1

在交叉应用程序的帮助下。

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

Answer 2

您可以执行LEAD函数来查找重叠部分，该函数属于window语句。我创建了一个名为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
;

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