展平/合并重叠的时间间隔 [英] Flatten/merge overlapping time intervals
问题描述
我有一个包含数百万行的服务"表.每行对应于员工在给定的日期和时间间隔内提供的服务(每行具有唯一的ID).在某些情况下，员工可能会在重叠的时间范围内提供服务.我需要编写一个查询，该查询合并重叠的时间间隔并以如下所示的格式返回数据.
I have a 'Service' table with millions of rows. Each row corresponds to a service provided by a staff in a given date and time interval (Each row has a unique ID). There are cases where a staff might provide services in overlapping time frames. I need to write a query that merges overlapping time intervals and returns the data in the format shown below.
我尝试按StaffID和Date字段进行分组，并获取BeginTime的最小值和EndTime的最大值，但这不考虑不重叠的时间范围.我该怎么做?同样，该表包含数百万条记录，因此递归CTE方法可能会出现性能问题.预先感谢.
I tried grouping by StaffID and Date fields and getting the Min of BeginTime and Max of EndTime but that does not account for the non-overlapping time frames. How can I accomplish this? Again, the table contains several million records so a recursive CTE approach might have performance issues. Thanks in advance.
服务表
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
Here is another sample data set with a query proposed by a contributor. http://sqlfiddle.com/#!6/bfbdc/3
结果集中的前两行应合并为一行(06:00-08:45)，但会生成两行(06:00-08:30&06:00-08:45)
The first two rows in the results set should be merged into one row (06:00-08:45) but it generates two rows (06:00-08:30 & 06:00-08:45)
推荐答案
我仅提出了CTE查询，因为问题可能是一连串的重叠时间，例如记录1与记录2重叠，记录2与记录3重叠，依此类推.如果没有CTE或其他某种循环，则很难解决这个问题.无论如何，请尝试一下.
I only came up with a CTE query as the problem is there may be a chain of overlapping times, e.g. record 1 overlaps with record 2, record 2 with record 3 and so on. This is hard to resolve without CTE or some other kind of loops, etc. Please give it a go anyway.
CTE查询的第一部分获取启动一个新组的服务，并且这些启动服务的启动时间与其他服务的启动时间不同(我只需要一个记录即可启动一个组).第二部分是那些开始一组的人，但是有更多的人具有相同的开始时间-同样，我只需要其中一个.最后一部分递归地建立在起始组上，并采用所有重叠的服务.
The first part of the CTE query gets the services that start a new group and are do not have the same starting time as some other service (I need to have just one record that starts a group). The second part gets those that start a group but there's more then one with the same start time - again, I need just one of them. The last part recursively builds up on the starting group, taking all overlapping services.
这里是 SQLFiddle ，其中添加了更多记录以演示不同种类的重叠和重复时间
Here is SQLFiddle with more records added to demonstrate different kinds of overlapping and duplicate times.
我不能使用ServiceID，因为它必须以与BeginTime相同的方式进行订购.
I couldn't use ServiceID as it would have to be ordered in the same way as 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
这篇关于展平/合并重叠的时间间隔的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
