时间戳

首页 > 解决方案 > SQL 计数未确认的警报

sql - SQL 计数未确认的警报

问题描述

我正在寻找在某个时间范围内激活但未确认的警报数量以及未清除的警报数量。三种类型的事件都存储在同一个表中：警报激活、确认和清除。

我正在查询的表看起来像这样。

ID 警报名称 事件类型 活跃 已确认 约会时间
1 警报 1 已确认 真的 真的 2020 年 12 月 22 日 06:00:00
2 警报 1 清除 错误的 真的 2020 年 12 月 22 日 06:30:00
3 警报 2 积极的 真的 错误的 2020 年 12 月 22 日 07:00:00
4 警报 3 积极的 真的 错误的 2020 年 12 月 22 日 07:30:00
5 警报 2 已确认 真的 真的 2020 年 12 月 22 日 08:00:00
6 警报 2 连接丢失 真的 真的 2020 年 12 月 22 日 08:30:00
7 警报 2 恢复连接 真的 真的 2020 年 12 月 22 日 09:00:00
8 警报 3 清除 错误的 错误的 2020 年 12 月 22 日 09:30:00
9 警报 2 清除 错误的 真的 2020 年 12 月 22 日 10:00:00
10 警报 3 积极的 真的 错误的 2020 年 12 月 22 日 10:30:00

该表的结果将是 2 个警报未确认和 1 个警报未清除。应该忽略前两行，因为初始 Active 事件不在时间范围内。警报 2 激活、确认然后清除。警报 3 激活并在被确认之前被清除。警报 3 再次激活，但没有确认或清除事件。

我不得不对不同的数据库做类似的事情，其中​​同一个警报的每个行事件都有一个 id。在那种情况下，我使用该 ID 将确认事件和清除事件加入到活动事件中，然后计算连接为空的位置。但是在这里，我没有那个警报 ID。我写了一个查询，它会添加一个唯一的 ID。这里是：

SELECT AlarmName,
SUM(CASE WHEN EventType = 'Active' THEN 1 ELSE 0 END) OVER (PARTITION BY AlarmName ORDER BY DateTime) as InstanceID
FROM AllEvent
ORDER BY AlarmName, InstanceID

有任何想法吗？

标签： sqlsql-serverdatetimecountgaps-and-islands

解决方案

对于“活动”>“已确认”>“已清除”的固定预期序列，您可以使用lead()：

select 
    sum(case when lead_event1 = 'Acknowledged' then 0 else 1 end) as cnt_not_ack,
    sum(case when lead_event2 = 'Cleared' then 0 else 1 end) as cnt_not_cleared
from (
    select t.*,
        lead(event_type, 1) over(partition by alarm_name order by date_time) as lead_event1
        lead(event_type, 2) over(partition by alarm_name order by date_time) as lead_event1
    from mytable t
) t
where event_type = 'Active'

对于每一行，lead()检索相同警报编号的下一个和下一个事件类型）。然后我们可以只过滤“活动”事件，并检查以下事件是否是确认和清除。最后一步是聚合。

推荐阅读