如何将时间间隔分割成不相交的时间间隔并计算其中的行数?

Question

我有人们访问某个地方的记录数据。访问是有时间间隔的。我想数一数在什么时间有多少人在那里。

例如玩具数据:

create table data ( id int, start_time datetime, end_time datetime);
insert into data values (1, '2019-11-06 09:32:00', '2019-11-06 09:41:00');
insert into data values (2, '2019-11-06 09:35:00', '2019-11-06 09:43:00');

我期望得到

start_time              end_time           count 
2019-11-06 09:32:00   2019-11-06 09:35:00        1
2019-11-06 09:35:00   2019-11-06 09:41:00        2
2019-11-06 09:41:00   2019-11-06 09:43:00        1

如何在SQL Server中有效地实现这一点?

Answer 1

似乎你需要做的是解开你的时间轴，使用LEAD，得到下一个时间，然后用新的"桶"重新将JOIN放到你的表中。对于我们拥有的示例数据，这是有效的:

WITH Times AS(
SELECT V.[Time] AS Start_Time,
LEAD(V.Time) OVER (ORDER BY V.Time) AS End_Time
FROM dbo.data d
CROSS APPLY (VALUES(d.start_time),(d.end_time))V([Time]))
SELECT T.Start_Time,
T.End_Time,
COUNT(d.id) AS [Count]
FROM Times T
LEFT JOIN dbo.data d ON d.start_time < T.End_Time
AND d.end_time > T.Start_Time
WHERE T.End_Time IS NOT NULL
GROUP BY T.Start_Time,
T.End_Time;