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

Question

场景是用户指定他们何时有空，这些指定的时间可以彼此重叠。我正在尝试获取他们可用的总时间。使用 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 Fiddle

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

编辑 12 年 11 月 21 日:到目前为止，感谢大家提供的解决方案 - 在某种程度上，我很高兴看到这不是一个“容易”编写的查询。

编辑 2012 年 11 月 23 日:这都是伟大的工作。在内部，我们认为最好确保用户不能输入重叠的时间(例如强制他们修改现有条目)!

Answer 1

Gordon Linoff 有一个 CTE based answer

我对所有工作算法做了一些performance analysis空白值意味着花费的时间太长。这是在单个 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