所以,我有一个包含如下行的表格:
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,让我可以将不同机器的警报分开。(你喜欢这种任意的列名吗?) 有超过九百个独特的警报消息。
我想要查询返回的结果类似于这样:
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) and (when end_time = start_time)) 进行分组(请原谅我的伪代码),但我不知道所需的语法。 这里有一个 SQLFiddle 链接