sql-server - 展平/合并重叠的时间间隔

Question

我有一个包含数百万行的“服务”表。每行对应于工作人员在给定日期和时间间隔内提供的服务(每行都有一个唯一的 ID)。在某些情况下，工作人员可能会在重叠的时间范围内提供服务。我需要编写一个查询来合并重叠的时间间隔并以如下所示的格式返回数据。

我尝试按 StaffID 和日期字段进行分组，并获取 BeginTime 的最小值和 EndTime 的最大值，但这并没有考虑到非重叠的时间范围。我怎样才能做到这一点？同样，该表包含数百万条记录，因此递归 CTE 方法可能会存在性能问题。提前致谢。

服务表

ID    StaffID  Date        BeginTime EndTime
1     101      2014-01-01  08:00     09:00
2     101      2014-01-01  08:30     09:30
3     101      2014-01-01  18:00     20:30
4     101      2014-01-01  19:00     21:00

输出

StaffID Date        BeginTime EndTime
101     2014-01-01  08:00     09:30
101     2014-01-01  18:00     21:00

这是另一个示例数据集，其中包含贡献者提出的查询。 http://sqlfiddle.com/#!6/bfbdc/3

结果集中的前两行应合并为一行 (06:00-08:45)，但它会生成两行 (06:00-08:30 和 06:00-08:45)

Answer 1

我只提出了 CTE 查询，因为问题是可能存在一系列重叠时间，例如记录 1 与记录 2 重叠，记录 2 与记录 3 重叠，依此类推。如果没有 CTE 或其他类型的循环等，这个问题很难解决。无论如何，请尝试一下。

CTE 查询的第一部分获取启动新组的服务，并且启动时间与其他服务不同(我只需要一个启动组的记录)。第二部分获取那些开始一组的人，但有多个具有相同开始时间的人 - 再说一次，我只需要其中一个。最后一部分在起始组上递归构建，获取所有重叠的服务。

这里是SQLFiddle添加了更多记录来演示不同类型的重叠和重复时间。

我无法使用 ServiceID，因为它必须以与 BeginTime 相同的方式排序。

;with flat as
(
 select StaffID, ServiceDate, BeginTime, EndTime, BeginTime as groupid 
 from services S1
 where not exists (select * from services S2 
 where S1.StaffID = S2.StaffID 
 and S1.ServiceDate = S2.ServiceDate 
 and S2.BeginTime <= S1.BeginTime and S2.EndTime <> S1.EndTime
 and S2.EndTime > S1.BeginTime)

  union all

  select StaffID, ServiceDate, BeginTime, EndTime, BeginTime as groupid 
  from services S1
 where exists (select * from services S2 
 where S1.StaffID = S2.StaffID 
 and S1.ServiceDate = S2.ServiceDate 
 and S2.BeginTime = S1.BeginTime and S2.EndTime > S1.EndTime)
   and not exists (select * from services S2 
 where S1.StaffID = S2.StaffID 
 and S1.ServiceDate = S2.ServiceDate 
 and S2.BeginTime < S1.BeginTime
 and S2.EndTime > S1.BeginTime)

 union all

 select S.StaffID, S.ServiceDate, S.BeginTime, S.EndTime, flat.groupid 
 from flat
 inner join services S 
 on flat.StaffID = S.StaffID
 and flat.ServiceDate = S.ServiceDate
 and flat.EndTime > S.BeginTime
 and flat.BeginTime < S.BeginTime and flat.EndTime < S.EndTime
)

select StaffID, ServiceDate, MIN(BeginTime) as begintime, MAX(EndTime) as endtime 
from flat
group by StaffID, ServiceDate, groupid
order by StaffID, ServiceDate, begintime, endtime