忽略重叠时间，找到总分钟数（将基于光标的答案转换为CTE）

Question

有一个已存在的问题，询问如何找到多个日期范围内的分钟数，忽略重叠部分。

所提供的示例数据为（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

- Laurence

---

似乎与以下链接中的内容相同： https://dev59.com/wmvXa4cB1Zd3GeqPFQ-x#13464594 - bummi

@bummi，我在第一行中链接了那个问题。我对具体的做法很感兴趣，以及是否可以用标准的CTE方式累积列表。 - Laurence

抱歉，没看到……今天不是我的日子 :-( - bummi

Answer 1

以下查询根据您的定义找到数据中的时间段。它首先使用相关子查询来确定记录是否为时间段的开始（即与较早时间段没有重叠）。然后，将“periodStart”分配为最近的开始时间，该时间是非重叠时期的开始。

以下是采用此方法的（未经测试）查询：

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值相同。由于相关子查询的存在，即使在中等大小的数据集上，性能可能也不会很好。

还有其他方法可以处理这个问题。例如，如果您使用的是SQL Server 2012，则可以使用累积和函数，这提供了一种更简单的方法。

- Gordon Linoff

---

Gordon，非常感谢您的帮助，我已经修正了几个错别字并进行了测试。它确实有效（并输出了实际的句号，这很好）。我有点惊讶光标算法不能更直接地翻译。它本质上是一种一次遍历的累加器方法（其中累加器的下一个值仅取决于当前值和表中的当前行）。这就像是CTE对于这样一个平凡的算法来说太过强大了。 - Laurence

@Laurence . . . 当你有累积和时，光标算法可以更直接地应用。它可能仍然不是一遍完成，但是光标通常非常慢（本质上是单线程的，从数据库移动数据到应用程序），因此基于数据库的替代方案通常执行得更好。 - Gordon Linoff

Gordon，我终于让我的原始CTE（在问题中更新）工作了。很想听听您对性能的看法。我认为查询计划看起来还不错，userid、availstart上有一个索引。 - Laurence

递归CTE可能会表现更好。值得比较这两者的性能以确保。 - Gordon Linoff

Answer 2

我通过创建一个简单的表来解决这个问题（某种程度上），其中一列包含日期和时间（精确到分钟）（PK），第二列是一个位。'1'表示用户可用，'0'表示不可用。

其余部分非常简单。我厌倦了编写无尽复杂的查询，试图获取部分重叠时间范围内的分钟数。

实际上，这是为计算机效率而设计的。

我知道这不是真正的解决方案，但这是我想出的最简单的解决方案。您可以创建一个函数/SP来创建该表。

- alzaimar