多个日期范围之间的分钟总和

小编典典

多个日期范围之间的分钟总和

sql

场景是用户指定何时可用，这些指定的时间可以 彼此重叠 。我正在尝试获取可用的总时间。SQL Fiddle示例：

--Available--
ID  userID  availStart          availEnd
1   456     '2012-11-19 16:00'  '2012-11-19 17:00'
2   456     '2012-11-19 16:00'  '2012-11-19 16:50'
3   456     '2012-11-19 18:00'  '2012-11-19 18:30'
4   456     '2012-11-19 17:30'  '2012-11-19 18:10'
5   456     '2012-11-19 16:00'  '2012-11-19 17:10'
6   456     '2012-11-19 16:00'  '2012-11-19 16:50'

输出应为130分钟：

1: 60
2: 0 as falls inside 1
3: 30
4: 30 as the last 10 mins is covered by 3
5: 10 as first 60 mins is covered by 1
6: 0 as falls inside 1

我可以得到总的重叠分钟数，但是这比可用分钟数的总和还多：

SQL小提琴

有什么想法可以实现这一目标吗？

编辑11月21日，11：到目前为止，感谢每个人的解决方案-从某种程度上来说，我很高兴看到这并不是编写一个“简单”的查询。

编辑11月23日 ：一切都是伟大的工作。在内部，我们认为最好是确保用户不能输入重叠的时间（例如，强迫他们修改现有的输入）！

阅读 193

2021-03-23

共1个答案

小编典典

Gordon Linoff有基于CTE的答案

我已经对所有可用算法进行了性能分析。空白值表示此过程花费了太长时间。这是在单个Core
i7 X920 @ 2GHz芯片上测试的，并带有几个SSD。创建的唯一索引是UserID
AvailStart上的群集。如果您认为可以改善任何性能，请告诉我。

此CTE版本比线性版本差，SQL Server无法以有效的方式执行RN = RN +
1联接。我在下面的一种混合方法中对此进行了纠正，在该方法中，我将第一个CTE保存并索引到表变量中。这仍然需要比基于游标的方法多十倍的IO。

With OrderedRanges as (
  Select
    Row_Number() Over (Partition By UserID Order By AvailStart) AS RN,
    AvailStart,
    AvailEnd
  From
    dbo.Available
  Where
    UserID = 456
),
AccumulateMinutes (RN, Accum, CurStart, CurEnd) as (
  Select
    RN, 0, AvailStart, AvailEnd
  From
    OrderedRanges
  Where 
    RN = 1
  Union All
  Select
    o.RN, 
    a.Accum + Case When o.AvailStart <= a.CurEnd Then
        0
      Else 
        DateDiff(Minute, a.CurStart, a.CurEnd)
      End,
    Case When o.AvailStart <= a.CurEnd Then 
        a.CurStart
      Else
        o.AvailStart
      End,
    Case When o.AvailStart <= a.CurEnd Then
        Case When a.CurEnd > o.AvailEnd Then a.CurEnd Else o.AvailEnd End
      Else
        o.AvailEnd
      End
  From
    AccumulateMinutes a
        Inner Join 
    OrderedRanges o On 
        a.RN = o.RN - 1
)

Select Max(Accum + datediff(Minute, CurStart, CurEnd)) From AccumulateMinutes

http://sqlfiddle.com/#!6/ac021/2

经过性能分析后，这里是混合CTE /表变量版本，其性能比除基于游标的方法外要好

Create Function dbo.AvailMinutesHybrid(@UserID int) Returns Int As
Begin

Declare @UserRanges Table (
  RN int not null primary key, 
  AvailStart datetime, 
  AvailEnd datetime
)
Declare @Ret int = Null

;With OrderedRanges as (
  Select
    Row_Number() Over (Partition By UserID Order By AvailStart) AS RN,
    AvailStart,
    AvailEnd
  From
    dbo.Available
  Where
    UserID = @UserID
)
Insert Into @UserRanges Select * From OrderedRanges


;With AccumulateMinutes (RN,Accum, CurStart, CurEnd) as (
  Select
    RN, 0, AvailStart, AvailEnd
  From
    @UserRanges
  Where 
    RN = 1
  Union All
  Select
    o.RN, 
    a.Accum + Case When o.AvailStart <= a.CurEnd Then
        0
      Else 
        DateDiff(Minute, a.CurStart, a.CurEnd)
      End,
    Case When o.AvailStart <= a.CurEnd Then 
        a.CurStart
      Else
        o.AvailStart
      End,
    Case When o.AvailStart <= a.CurEnd Then
        Case When a.CurEnd > o.AvailEnd Then a.CurEnd Else o.AvailEnd End
      Else
        o.AvailEnd
      End
  From
    AccumulateMinutes a
        Inner Join 
    @UserRanges o On 
        a.RN + 1 = o.RN
)

Select 
  @Ret = Max(Accum + datediff(Minute, CurStart, CurEnd)) 
From 
  AccumulateMinutes 
Option
  (MaxRecursion 0)

Return @Ret

End

http://sqlfiddle.com/#!6/bfd94

2021-03-23