时间戳

首页 > 解决方案 > 计算 SQL Server 中具有部分重叠 StartTime 和 EndTime 值的记录的总时间

sql - 计算 SQL Server 中具有部分重叠 StartTime 和 EndTime 值的记录的总时间

问题描述

我正在努力将一些关于加油站的数据从 SQL 数据库中聚合到易于理解的 KPI 中，以便在 Grafana 中进行可视化。

一个关键是站点在给定时间段内的停机时间。停机时间被认为是“在加油站无法加油时”。影响这一点的组件是泵和分配器（通常两个用于一个站）。

FaultsAndWarnings 表中的记录具有 Station、VarName、StartTime 和 EndTime 作为相关变量。在开始时间和结束时间之间，组件被视为“故障”（无法运行）。

我可以通过查询下表来计算每个单独组件“出现故障”的时间。

  DECLARE @__timeFrom datetime = $__timeFrom();
  DECLARE @__timeTo datetime = $__timeTo(); 
  DECLARE @__duration float = datediff(second, @__timeFrom, @__timeTo); 

    with cte as (
      select [VarName], [Station],
             (case when EndTime < @__timeTo
                   then EndTime
                   else @__timeTo
              end) as endtime,
             (case when StartTime > @__timeFrom
                   then StartTime
                   else @__timeFrom
              end) as starttime
      from [dbo].[FaultsAndWarnings]
      where VarName = 'Pump' 
      OR VarName = 'Dispenser1' 
      OR VarName = 'Dispenser2' 
      AND EndTime > @__timeFrom AND StartTime < @__timeTo 
     )

select 
    (sum(case when VarName = 'Pump' then datediff(second, StartTime, endtime) end) / @__duration) * 100 AS Pump
    (sum(case when VarName = 'Dispenser1' then datediff(second, StartTime, endtime) end) / @__duration) * 100 AS Dispenser1
    (sum(case when VarName = 'Dispenser2' then datediff(second, StartTime, endtime) end) / @__duration) * 100 AS Dispenser2
    SUBSTRING (Station, 16, 30)
from cte
group by Station;

（为便于阅读进行了调整。实际的 VarNames 是稍微复杂的 PLC 标记）

结果给了我在给定时间范围内每个组件出现故障的时间百分比。'$__timeFrom()' 变量是 Grafana 时间范围输入。

但现在我想进一步聚合，事情变得棘手。因为我们想看到的是：

100% 停机为：泵停机或分配器 1 且分配器 2 停机
50% 停机为：分配器 1 或分配器 2 停机且泵未停机

这就是我微不足道的 SQL 技能开始动摇的地方。

感觉就像我想做以下事情（用于计算 100% 停机时间）：

  1. 制作新的中间表，其中包含时间范围内的记录以及泵在哪里或 Dispenser1 和 Dispenser2 重叠的位置。

    示例：
    从 StartTime 14:00 抽空到 EndTime 1800
    Dispenser1 AND Dispenser2 从 StartTime 16:00 到 19:00 向下
    抽气 => 添加记录 StationDown，StartTime 为 14:00 和 EndTime 19:00

  2. 以与我已经对单个组件结果相同的方式聚合重叠的 100%-down-results。

不过，我在将记录与其他记录进行比较时遇到了麻烦，这种方式允许我创建新的重叠记录。任何人都有这种诡计的经验吗？

标签： sqlsql-server

解决方案

您有两个泵的有限数量的停机组合。 S = start和E = end。

[Pump 1]               S...........E  (no overlap)
[Pump 2]  S.......E

[Pump 1]      S...........E           (some overlap)
[Pump 2]  S.......E

[Pump 1]      S...........E           (complete overlap)
[Pump 2]        S.......E

[Pump 1]      S...........E           (complete overlap)
[Pump 2]    S...............E

[Pump 1]      S...........E           (some overlap)
[Pump 2]              S.......E

[Pump 1]      S......E               (no overlap)
[Pump 2]                  S.......E

您可以使用单个case语句测量每个用例的重叠。您无需担心这些no overlap情况。您必须测试代码并查看当单个泵多次中断时它的行为...

select overlap = 
  case
    when S1 >  S2 and E1 >  E2 then datediff(min, S1, E2)
    when S1 <= S2 and E1 >= E2 then datediff(min, S2, E2)
    when S2 <= S1 and E2 >= E1 then datediff(min, S1, E1)
    when S1 <  S2 and E1 <  E2 then datedif(min, S2, E1)
    else 0
  end

推荐阅读