在Server 2012+中选择连续期间的最小开始和最大结束

Question

我在Server 2012中有一个表，其中的事件日志格式如下：

+=====+=============================+=============================+======+
| ID1 |       start_time_utc        |        end_time_utc         | ID2  |
+=====+=============================+=============================+======+
|  57 | 2018-11-11 11:00:00.0000000 | 2018-11-11 11:00:28.0012900 |   15 |
|  57 | 2018-11-11 11:00:28.0012900 | 2018-11-11 11:01:29.0543947 | 1020 |
|  57 | 2018-11-11 11:01:29.0543947 | 2018-11-11 11:02:28.1923079 |   16 |
|  57 | 2018-11-11 11:02:28.1923079 | 2018-11-11 11:04:28.3367626 |   16 |
|  57 | 2018-11-11 11:04:28.3367626 | 2018-11-11 11:05:28.5307626 | 1020 |
| 103 | 2018-11-10 20:00:00.0000000 | 2018-11-11 03:00:00.0000000 |   15 |
| 103 | 2018-11-11 03:00:00.0000000 | 2018-11-11 10:57:00.8175737 |   15 |
| 103 | 2018-11-11 10:57:00.8175737 | 2018-11-11 10:57:27.8322749 | 1017 |
| 103 | 2018-11-11 10:57:27.8322749 | 2018-11-11 11:00:00.0000000 |   15 |
| 103 | 2018-11-11 11:00:00.0000000 | 2018-11-11 11:00:31.9916890 |   15 |
+-----+-----------------------------+-----------------------------+------+

对于给定的ID1，结束日期具有下一个事件的匹配开始日期。我希望通过匹配ID1和ID2列来划分数据，并为连续事件的每个分区选择开始日期和结束日期。因此，结果应该是：

+=====+=============================+=============================+======+
| ID1 |       start_time_utc        |        end_time_utc         | ID2  |
+=====+=============================+=============================+======+
|  57 | 2018-11-11 11:00:00.0000000 | 2018-11-11 11:00:28.0012900 |   15 |
|  57 | 2018-11-11 11:00:28.0012900 | 2018-11-11 11:01:29.0543947 | 1020 |
|  57 | 2018-11-11 11:01:29.0543947 | 2018-11-11 11:04:28.3367626 |   16 |
|  57 | 2018-11-11 11:04:28.3367626 | 2018-11-11 11:05:28.5307626 | 1020 |
| 103 | 2018-11-10 20:00:00.0000000 | 2018-11-11 10:57:00.8175737 |   15 |
| 103 | 2018-11-11 10:57:00.8175737 | 2018-11-11 10:57:27.8322749 | 1017 |
| 103 | 2018-11-11 10:57:27.8322749 | 2018-11-11 11:00:31.9916890 |   15 |
+-----+-----------------------------+-----------------------------+------+

显然，我不能使用简单的group by，也不知道如何通过查询编写分区。谢谢你帮忙。

Answer 1

这是一个更新的版本，它适用于给定的数据集，并处理在所有我测试过的情况下，id1序列、id2大于2的情况。这比我原来的答案简单得多。用您的表名替换tstGrouping。

;with p as
(
  select
   ROW_NUMBER () over (order by id1, start_time_utc) as row_num,
   ROW_NUMBER () over (order by id1,id2, start_time_utc) as row_num2,
    *
  from
    tstgrouping x1
)
select 
  id1,
  min(start_time_utc) as start_time_utc,
  max(end_time_utc) as end_time_utc,
  id2
from p
  group by
row_num - row_num2,id1,id2
order by 
id1, start_time_utc

Answer 2

;with base as
(
Select *
,row_number()over(partition by id1, 1d2 order by start_time_utc asc) ordstart
,row_number()over(partition by id1, id2, order by end_date_utc desc) ordend
)

Select * from 
base b1
inner join base b2 on b1.id1 = b2.id1 and b1.id2 = b2.id2 ans b1.ordstart = b2.ordend
Where b1.ordstart = 1