时间戳

首页 > 解决方案 > 合并 T-SQL 中当前记录的结束时间和下一条记录的开始时间之间的差小于 x 分钟的记录

sql - 合并 T-SQL 中当前记录的结束时间和下一条记录的开始时间之间的差小于 x 分钟的记录

问题描述

我想根据当前行结束日期和下一行开始日期之间 5 分钟的时间间隔来合并记录。我怎样才能实现这个 T-SQL？

合并前的表格如下 -

+---------+------------------+------------------+
| EventId |    StartTime     |     EndTime      |
+---------+------------------+------------------+
|       1 | 2020-11-05 10:05 | 2020-11-05 10:08 |
|       2 | 2020-11-05 10:15 | 2020-11-05 10:17 |
|       3 | 2020-11-05 10:20 | 2020-11-05 10:24 |
|       4 | 2020-11-05 10:25 | 2020-11-05 10:30 |
|       5 | 2020-11-05 10:40 | 2020-11-05 10:48 |
|       6 | 2020-11-05 10:58 | 2020-11-05 11:05 |
+---------+------------------+------------------+

合并后应该如下所示 -

| Consolidated StartTime | Consolidated EndTime |
|------------------------|----------------------|
| 2020-11-05 10:05       | 2020-11-05 10:08     |
| 2020-11-05 10:15       | 2020-11-05 10:30     |
| 2020-11-05 10:40       | 2020-11-05 10:48     |
| 2020-11-05 10:58       | 2020-11-05 11:05     |

标签： sqlsql-servertsql

解决方案

这是一种差距和孤岛问题。对于这个问题，使用lag()和 一个累积和：

select min(starttime), max(endtime)
from (select t.*,
             sum(case when prev_endtime >= dateadd(minute, 5, starttime) then 0 else 1 end) over
                 (order by eventid) as island_num
      from (select t.*,
                   lag(endtime) over (order by eventid) as prev_endtime
            from t
           ) t
      ) t
group by island_num;

lag()获取前一个进行endtime比较。中间查询确定新“岛”何时开始。当上一个endtime和当前之间的时间超过 5 分钟时，就会发生这种情况starttime。外部查询然后聚合以获得最终结果。

推荐阅读