查找忽略重叠的总分钟数(将基于光标的答案转换为 CTE) [英] Find Total Minutes Ignoring Overlap (Convert Cursor based Answer to CTE)

问题描述

有一个现有问题询问如何找到如何许多分钟存在于多个日期范围内，忽略重叠.

There is an existing question that asked how to find how many minutes there are in multiple date ranges, ignoring overlaps.

给出的示例数据是(userID 不是特别相关)

The example data given is (userID isn't particularly relevant)

--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，但是我不知道如何去做.

I can solve the problem using a cursor, but I think it should be adaptable to a CTE, however I can't figure out how to do it.

方法是按开始时间排列每个范围然后我们建立一个范围，按顺序合并范围，直到我们找到一个不与我们合并的范围重叠的范围.然后我们计算合并范围内有多少分钟，并记住这一点.我们继续下一个范围，再次合并任何重叠.每次我们得到一个非重叠的起点时我们累积分钟最后我们将累积的分钟添加到最后一个范围的长度上

The method is to arrange each range by start time Then we build a range that coalesces ranges in order, until we find a range that doesn't overlap our coalesced range. We then calculate how many minutes are in the coalesced range, and remember this We carry on with the next ranges, again coalesing any that overlap. We accumulate minutes each time we get a non overlapping start point At the end we add the accumulated minutes onto the length of the last range

很容易看出，由于顺序的原因，一旦一个范围与之前发生的事情不同，那么其他范围就不会与之前发生的事情重叠，因为它们的开始日期都更大.

It's fairly easy to see that because of the order, once a range is distinct from what's gone before then no further ranges could overlap what's gone before, as their start dates are all greater.

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 工作，只是递归中的一个愚蠢的错误.查询计划爆炸令人印象深刻:

Got the CTE working, was just a silly error in the recursion. The query plan explosion is quite impressive:

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，是否有以这种方式累积列表的通用模式?

Is this adaptable to a CTE, and is there a general pattern for accumulating over a list in thie way?

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

推荐答案

以下查询根据您的定义查找数据中的句点.它首先使用相关子查询来确定记录是否是一个时期的开始(即，与较早的时期没有重叠).然后将periodStart"指定为最近的开始，即非重叠期间的开始.

The following query finds the periods in the data, according to your definition. It uses correlated subqueries first to determine whether a record is the start of a period (that is, no overlap with earlier time periods). It then assigns the "periodStart" as the most recent start that is the beginning of a non-overlapping period.

以下(未经测试的)查询采用这种方法:

The following (untested) query takes this approach:

with TimeWithOverlap as (
     select t.*,
            (case when exists (select * from dbo.Available tbefore where t.availStart > tbefore.availStart and tbefore.availEnd >= t.availStart)
                  then 0
                  else 1
             end) as IsPeriodStart
     from dbo.Available t 
    ),
    TimeWithPeriodStart as (
     select two.*,
            (select MAX(two1.AvailStart) from TimeWithOverlap two1 where IsPeriodStart = 1 and two1.AvailStart <= two.AvailStart
            ) as periodStart
     from TimeWithOverlap two
    )
select periodStart, MAX(AvailEnd) as periodEnd
from TimeWithPeriodStart twps
group by periodStart;

http://sqlfiddle.com/#!6/3483c/20 (第二个查询)

如果两个时间段同时开始，那么它仍然有效，因为 AvailStart 值是相同的.由于相关的子查询，即使在中等规模的数据集上也可能表现不佳.

If two periods both start at the same time, then it still works, because the AvailStart values are the same. Because of the correlated subqueries, this might not perform very well on even medium sized data sets.

还有其他方法可以解决这个问题.例如，如果您有 SQL Server 2012，您将能够使用累积求和函数，它提供了一种更简单的方法.

There are other methods for approaching this. For instance, if you had SQL Server 2012, you would be able to use cumulative sum functions, which offer a simpler method.

这篇关于查找忽略重叠的总分钟数(将基于光标的答案转换为 CTE)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！