有一个现有问题该问题询问如何查找多个日期范围内有多少分钟,忽略重叠。
给出的示例数据是(userID 不是特别相关)
--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'
我可以使用游标解决问题,但我认为它应该适用于 CTE,但我不知道该怎么做。
方法是按开始时间排列每个范围,然后我们构建一个范围,按顺序合并范围,直到找到一个不与我们合并范围重叠的范围。然后我们计算合并范围内有多少分钟,并记住这一点我们继续下一个范围,再次合并任何重叠的部分。每次获得不重叠的起点时,我们都会累积分钟数最后,我们将累积的分钟数添加到最后一个范围的长度上
很容易看出,由于顺序的原因,一旦某个范围与之前的范围不同,那么其他范围就不能与之前的范围重叠,因为它们的开始日期都更大。
Declare
@UserID int = 456,
@CurStart datetime, -- our current coalesced range start
@CurEnd datetime, -- our current coalesced range end
@AvailStart datetime, -- start or range for our next row of data
@AvailEnd datetime, -- end of range for our next row of data
@AccumMinutes int = 0 -- how many minutes so far accumulated by distinct ranges
Declare MinCursor Cursor Fast_Forward For
Select
AvailStart, AvailEnd
From
dbo.Available
Where
UserID = @UserID
Order By
AvailStart
Open MinCursor
Fetch Next From MinCursor Into @AvailStart, @AvailEnd
Set @CurStart = @AvailStart
Set @CurEnd = @AvailEnd
While @@Fetch_Status = 0
Begin
If @AvailStart <= @CurEnd -- Ranges Overlap, so coalesce and continue
Begin
If @AvailEnd > @CurEnd
Set @CurEnd = @AvailEnd
End
Else -- Distinct range, coalesce minutes from previous range
Begin
Set @AccumMinutes = @AccumMinutes + DateDiff(Minute, @CurStart, @CurEnd)
Set @CurStart = @AvailStart -- Start coalescing a new range
Set @CurEnd = @AvailEnd
End
Fetch Next From MinCursor Into @AvailStart, @AvailEnd
End
Select @AccumMinutes + DateDiff(Minute, @CurStart, @CurEnd) As TotalMinutes
Close MinCursor
Deallocate MinCursor;
让 CTE 正常工作,只是递归中的一个愚蠢错误。查询计划的爆炸式增长令人印象深刻:
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
这是否适用于 CTE,是否有以这种方式累积列表的通用模式?
http://sqlfiddle.com/#!6/ac021/2