主页 / dba / 问题 / 249598
Accepted
Mrparkin
Asked: 2019-09-26 07:43:58 +0800 CST

Count 所选行之间的总时间差

  • 772

我的数据包含 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

“位置”状态标记我需要开始计数的位置。我需要在下次状态为“Out Of Tunnel”时停止计数。如果没有给出相应的'Out of Tunnel'，那就不要算了。

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

  • 从ID2开始：08:10:59
  • 在ID3结束：08:11:42
  • 总计 1: 00:00:43
  • 从ID4开始：08:11:42
  • 在ID7结束：09:43:10
  • 总计 2: 01:31:28
  • 总计：总计 1 + 总计 2 = 01:32:11

期望的输出

 ______________________
| Total Time in Tunnel |
|______________________|
|                      |
|        01:32:11      |
|______________________|
sql-server window-functions

2 个回答

  1. Best Answer

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

    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;

    在 SQL Server 2019 之前，如果查询中的至少一个参与表上没有列存储索引，SQL 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 小时，将输出格式化为 TIME。如果可能更多，您只需要添加一些格式化逻辑。

    • 7 
  2. WITH cte AS (
    SELECT t2.time - MIN(t1.time) delta
    FROM test t1
    JOIN test t2 ON t1.time < t2.time 
                AND t2.status = 'Out of Tunnel'
    LEFT JOIN test t3 ON t1.time < t3.time
                     AND t3.time < t2.time
                     AND t3.status = 'Out of Tunnel'
    WHERE t1.status = 'Location'
      AND t3.id IS NULL
    GROUP BY t2.time
)
SELECT SUM(delta) "Total Time in Tunnel"
FROM cte

    唯一的缺陷 - 自己将time字段转换为可比较、可减去和可求和的数据形式/类型（或在适当的地方使用适当的函数）。

    • 0

相关问题