gpt4 book ai didi

sql - 如何对连续的行进行分组?

转载 作者:行者123 更新时间:2023-12-04 22:41:12 25 4
gpt4 key购买 nike

所以,我有一个包含如下行的表格:

Ev_Message       Ev_Comment             EV_Custom1           Ev_Time_Ms     
-------------------------------------------------------------------------------------
Machine 1 Alarm 5/23/2016 11:02:00 AM Alarms Scanned 25
Machine 1 Alarm 5/23/2016 11:00:00 AM Alarms Scanned 686
Machine 1 Alarm 5/23/2016 11:00:00 AM Light curtain 537
Machine 1 Alarm 5/23/2016 11:00:00 AM Guard door open 346
Machine 1 Alarm 5/23/2016 11:00:00 AM No control voltage 135
Machine 1 Alarm 5/23/2016 10:38:34 AM Alarms Scanned 269
Machine 1 Alarm 5/23/2016 10:38:29 AM Alarms Scanned 378
Machine 1 Alarm 5/23/2016 10:38:29 AM Guard door open 156
Machine 1 Alarm 5/23/2016 10:38:25 AM Alarms Scanned 654
Not an Alarm 5/23/2016 10:38:25 AM Not an Alarm 467
Machine 1 Alarm 5/23/2016 10:38:25 AM Guard door open 234
Machine 1 Alarm 5/23/2016 10:38:25 AM No control voltage 67
Machine 1 Alarm 5/23/2016 10:38:23 AM Alarms Scanned 124
Machine 1 Alarm 5/23/2016 10:38:23 AM No control voltage 100

每次扫描警报时都会添加“已扫描的警报”行,即每次触发或清除警报时。任何警报都将添加具有特定 Ev_Custom1 的行。第一列 Ev_Message 包含一个机器 ID,可以让我从不同的机器中分离出警报。 (您不喜欢任意列名吗?)有超过 900 条独特的警报消息。

我希望我的查询返回的是这样的:

Alarm Message       Alarm Start Time       Alarm Stop Time  
----------------------------------------------------------------
No control voltage 5/23/2016 10:38:23 AM 5/23/2016 10:38:29 AM
Guard door open 5/23/2016 10:38:25 AM 5/23/2016 10:38:34 AM
No control voltage 5/23/2016 11:00:00 AM 5/23/2016 11:02:00 AM
Guard door open 5/23/2016 11:00:00 AM 5/23/2016 11:02:00 AM
Light curtain 5/23/2016 11:00:00 AM 5/23/2016 11:02:00 AM

这将是在两个日期之间过滤的查询。我有一些能力可以更改进入表中的数据,但是有 900 个警报,我的自由是有限的。

在一些帮助下,我当前的查询是这样的:

WITH T AS (
SELECT s.Ev_Comment AS start_time,
MIN(COALESCE (e.Ev_Comment, s.Ev_Comment)) AS end_time
FROM A AS s
INNER JOIN A AS e
ON s.Ev_Comment < e.Ev_Comment
AND s.Ev_Custom1 = 'Alarms Scanned'
AND e.Ev_Custom1 = 'Alarms Scanned'
GROUP BY s.Ev_Comment)
SELECT T_1.start_time,
T_1.end_time,
A.Ev_Custom1
FROM A
INNER JOIN T AS T_1
ON A.Ev_Comment LIKE T_1.start_time
WHERE (A.Ev_Custom1 <> 'Alarms Scanned')

我还有一个问题。如果警报持续时间超过一个时间段,例如从 10:38:25 到 10:38:34 的“防护门打开”,那么它将显示在两个单独的行中,如下所示:

start_time             end_time               EV_Custom1   
--------------------- --------------------- -------------
5/23/2016 10:38:25 AM 5/23/2016 10:38:29 AM Guard door open
5/23/2016 10:38:29 AM 5/23/2016 10:38:34 AM Guard door open

理想情况下,我想要的是:

start_time             end_time               EV_Custom1   
--------------------- --------------------- -------------
5/23/2016 10:38:25 AM 5/23/2016 10:38:34 AM Guard door open

我想我需要按 ((Ev_custom1) 和 (when end_time = start_time)) 分组(请原谅我的伪代码),但我对此所需的语法知之甚少。

Here is an SQLFiddle

最佳答案

如果我正确理解发布的问题,那么您的 CTE 可以有效地确定所有警报的时间段(或间隔)。您的最终选择子句将实际警报信息与您的警报间隔结合起来。您的部分问题是,如果您的警报长时间保持事件状态(我假设比您的警报扫描周期更长),您的警报系统将继续记录“已扫描警报”条目,这会有效地导致事件警报被拆分。如果您有 SQL Server 2012 或更高版本,那么确定警报事件是否拆分相对容易。您只需要检查一个警报的结束时间是否等于下一个相同警报类型的警报的开始时间。您可以使用 2012 年的 LAG 窗口函数实现此目的。
下一步是生成一个 ID,您可以根据该 ID 对警报进行分组,以便您可以组合拆分事件。这是通过 SUM OVER 子句实现的。以下示例显示了如何实现这一点:

;WITH AlarmTimeBuckets
AS
(
SELECT EventStart.Ev_Comment AS StartDateTime
,MIN(COALESCE (EventEnd.Ev_Comment, EventStart.Ev_Comment)) AS EndDateTime
,EventStart.Ev_Message As Machine
FROM A EventStart
INNER JOIN A EventEnd ON EventStart.Ev_Comment < EventEnd.Ev_Comment AND EventStart.Ev_Custom1 = 'Alarms Scanned' AND EventEnd.Ev_Custom1 = 'Alarms Scanned' AND EventStart.Ev_Message = EventEnd.Ev_Message
GROUP BY EventStart.Ev_Message, EventStart.Ev_Comment
),
AlarmsByTimeBucket
AS
(
SELECT AlarmTimeBuckets.Machine
,AlarmTimeBuckets.StartDateTime
,AlarmTimeBuckets.EndDateTime
,Alarm.Ev_Custom1 AS Alarm
,(
CASE
WHEN LAG(AlarmTimeBuckets.EndDateTime, 1, NULL) OVER (PARTITION BY Alarm.Ev_Custom1,Alarm.Ev_Message ORDER BY AlarmTimeBuckets.StartDateTime) = AlarmTimeBuckets.StartDateTime THEN 0
ELSE 1
END
) AS IsNewEvent
FROM A Alarm
INNER JOIN AlarmTimeBuckets ON Alarm.Ev_Message = AlarmTimeBuckets.Machine AND Alarm.Ev_Comment = AlarmTimeBuckets.StartDateTime
WHERE (Alarm.Ev_Custom1 <> 'Alarms Scanned')
)
,
AlarmsByGroupingID
AS
(
SELECT Machine
,StartDateTime
,EndDateTime
,Alarm
,SUM(IsNewEvent) OVER (ORDER BY Machine, Alarm, StartDateTime) AS GroupingID
FROM AlarmsByTimeBucket
)
SELECT MAX(Machine) AS Machine
,MIN(StartDateTime) AS StartDateTime
,MAX(EndDateTime) AS EndDateTime
,MAX(Alarm) AS Alarm
FROM AlarmsByGroupingID
GROUP BY GroupingID
ORDER BY StartDateTime

关于sql - 如何对连续的行进行分组?,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/37468189/

25 4 0
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com