如何在SQL Server中查找特定条件连续正确的开始和结束时间

Question

我遇到了一个有趣的问题，我正在尝试将能量用法记录到生产环境中的特定事件。我们的生产数据已经记录了大约6个月，记录的数据每30秒或更改每记录插入记录。在这些日志中，"工厂模式"也记录在以下结构中：

t_stamp             tag     value
----------------------------------
11/12/17 21:00:00   mode    10
11/12/17 21:00:30   mode    10
11/12/17 21:01:00   mode    80
11/12/17 21:01:30   mode    80
11/12/17 21:02:00   mode    80
11/12/17 21:02:30   mode    80
….                  mode    80
12/12/17 03:03:30   mode    80
12/12/17 03:04:00   mode    80
12/12/17 03:04:30   mode    80
12/12/17 03:05:00   mode    10
12/12/17 03:05:30   mode    10
12/12/17 03:06:00   mode    10
12/12/17 03:06:30   mode    10

我想查询以下结果：

row    mode    start_t_stamp        end_time_stamp
------------------------------------------------------
1      10      11/12/17 21:00:00    11/12/17 21:00:30
2      80      11/12/17 21:01:00    12/12/17 03:04:30
3      10      12/12/17 03:05:00    12/12/17 03:06:30

一旦我有了开始和结束时间戳，我将能够为工厂的每种模式提取能量使用数据我陷入了每一个第一步，我看过分区，阅读有关岛屿和空白的文章，但我找不到以这种方式登录数据的示例。任何建议都很棒

Answer 1

这是一个差距和岛屿问题。如果您有可用的窗口功能，则可以使用以下

select value, 
       min(t_stamp) start_t_stamp , 
       max(t_stamp) end_time_stamp 
from
(
    select *, row_number() over (partition by value order by t_stamp) - 
              row_number() over (order by t_stamp) grn
    from data
) t
group by value, grn

基本思想是，具有相同value的随之而来的行将具有相同的grn。当您拥有此功能时，您可以轻松分组。