计算选定行之间的总时间差

Question

我的数据包含ID、状态和时间(hh:mm:ss)。

我需要计算每个“位置”和“出隧道”状态之间的总时间。

当前表

    ID   ||       time       ||     Status
________________________________________________
      1  ||       08:09:14   ||   Out of Tunnel
      2  ||       08:10:59   ||     Location
      3  ||       08:11:42   ||   Out of Tunnel
      4  ||       08:11:55   ||     Location
      5  ||       08:16:36   ||     Location          
      6  ||       09:41:36   ||     Location
      7  ||       09:43:10   ||   Out of Tunnel
      8  ||       09:43:19   ||     Location

“位置”状态标记着我需要开始计数的位置。我需要伯爵在下一次的状态是“出隧道”时停止。如果没有相应的“隧道外”，那就不要计算。

然后将每个总数相加，得出总计。例如：

• 从ID2: 08:10:59开始
• 结束于ID3: 08:11:42
• 共计: 1: 00:43
• 从ID4: 08:11:42开始
• 结束时间: ID7: 09:43:10
• 总计2: 01:31:28
• 总计: Total1 + Total2 = 01:32:11

期望输出

 ______________________
| Total Time in Tunnel |
|______________________|
|                      |
|        01:32:11      |
|______________________|

Answer 1

第二轮从ID 4开始，样本数据中的时间是08:11:55，所以如果确实应该使用这个时间作为起点，那么总数应该是01:31:58。无论如何，这里有一个使用延迟和引导窗口函数的解决方案。如果要防止计划中的排序，请确保创建以下支持索引：

create unique index idx_time_status on dbo.t1(time, status);

如果在2016+上运行，可以通过创建以下虚拟索引来启用批处理：

create nonclustered columnstore index idx_cs on dbo.t1(id) where id = -1 and id = -2;

在Server 2019之前，如果查询中的至少一个参与表上没有列存储索引，则Server将不考虑使用批处理。创建这个虚拟索引，即使它本身实际上是毫无意义的，也可以为窗口函数使用更优化的批处理。查看有索引和没有索引的计划。我用细节在这里来解释这一点。

以下是解决方案代码：

with c1 as
(
  select *,
    case 
      when status = 'location'
        and lag(status) over(order by time) = 'location' then 'no'
      else 'yes'
    end as keeper
  from dbo.t1
  where status in ('location', 'out of tunnel')
),
c2 as
(
  select *, lead(time) over(order by time) as nxt
  from c1
  where keeper = 'yes'
)
select dateadd(second, sum(datediff(second, time, nxt)), cast('00:00:00' as time(0))) as total
from c2
where status = 'location';

假设总时间少于24小时，我将输出格式化为时间。如果可以更多的话，您只需要添加一些格式化逻辑。