SQL Server 识别时间戳之间的差距并分别标记它们

Question

我有一组带有id和时间戳的数据。

我想在自己的开始和结束时输出间隙大于 20 分钟的每个间隙。

示例数据：

IDID timerecord
1    2018-02-26 06:40:28.483
2    2018-02-26 06:42:03.967
3    2018-02-26 06:44:07.277
4    2018-02-26 06:47:25.913
5    2018-02-26 07:04:23.290
6    2018-02-26 10:19:25.063
7    2018-02-26 10:19:57.750
8    2018-02-26 10:21:45.547
9    2018-02-26 10:24:14.297
10   2018-02-26 10:28:17.967
11   2018-02-26 10:30:10.907
12   2018-02-26 10:30:20.627
13   2018-02-26 10:41:39.717
14   2018-02-26 10:43:00.247
15   2018-02-26 10:45:00.120
16   2018-02-26 10:47:13.867
17   2018-02-26 10:49:36.727
18   2018-02-26 17:06:30.333
19   2018-02-26 17:07:55.550
20   2018-02-26 17:09:37.520
21   2018-02-26 17:16:49.487

SQL 小提琴：http://sqlfiddle.com/#!18/42efe/1/0

预期输出：

timestart               timeend
2018-02-26 06:40:28.483 2018-02-26 07:04:23.290
2018-02-26 10:19:25.063 2018-02-26 10:49:36.727
2018-02-26 17:06:30.333 2018-02-26 17:16:49.487

Answer 1

使用lag()来标识组的起点。 然后执行累积总和以分配组。 和聚合：

select min(timerecord), max(timerecord)
from (select t.*,
sum(case when timerecord < dateadd(minute, 20, prev_tr)
then 0 else 1
end) over (order by timerecord) as grp
from (select t.*, lag(timerecord) over (order by timerecord) as prev_tr
from t
) t
) t
group by grp
order by min(timerecord);

这是SQL小提琴。

Answer 2

将lag()与累积方法一起使用：

SELECT MIN(timerecord), MAX(timerecord)
FROM (SELECT *, SUM(CASE WHEN DIFF_MN > 20 THEN 1 ELSE 0 END) OVER (ORDER BY IDID) GRP
FROM (SELECT *, DATEDIFF(MINUTE, LAG(timerecord) OVER (ORDER BY IDID), timerecord) AS DIFF_MN 
FROM Mytable 
) T
) T
GROUP BY GRP;

这是一个演示。