如何对连续的行进行分组？

Question

所以，我有一个包含如下行的表格：

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 链接

- z_temp_string

---

你如何获取“报警停止时间”的值？ - techspider

这是“扫描警报”行的时间戳，该时间戳没有与其相同的警报，表明在该扫描期间未发现警报。 - z_temp_string

我根据请求增加了 Ev_Message 列的重要性。 - z_temp_string

Answer 1

如果我正确理解了发布的问题，那么你的CTE有效地确定了所有警报的时间段（或间隔）。你的最终选择子句将实际警报信息与警报间隔连接起来。你的问题之一是，如果警报保持活动状态时间过长（我假设比警报扫描周期长），则警报系统将继续记录“已扫描警报”条目，这实际上会导致活动警报被拆分。 如果你使用的是SQL Server 2012或更高版本，则相对容易确定警报事件是否被拆分。你只需要检查一个警报的结束时间是否等于下一个相同类型警报的开始时间即可。你可以使用2012年的LAG窗口函数来实现这一点。
下一步是生成一个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

- Edmond Quinton

---

原来我正在使用SQL Server 2012。我正在通过Microsoft Visual Studio 2010使用SQL Server Reporting Services进行查询。我完全复制了你的答案，它给了我想要的结果。 - z_temp_string

我错了。你没有考虑到第一列Ev_Message。这一列保存了机器ID，让我可以将来自不同机器的警报分开。 （你喜欢任意的列名吗？）你的查询没有查看那一列。我会看看能否自己调整它。 - z_temp_string

@TylerLillemo请更新您的原始帖子以反映EV_Message的重要性，因为这是您第一次提到它。 - Edmond Quinton

谢谢，我相信通过在第一个“inner join”和“group by”之间添加WHERE语句，我已经解决了它。 - z_temp_string

@TylerLillemo，你还需要更新窗口函数调用，从OVER (PARTITION BY Alarm.Ev_Message改为OVER (PARTITION BY Alarm.Ev_Custom1,Alarm.Ev_Message。我已经更新了我的答案以反映这个变化。 - Edmond Quinton

谢谢你的帮助。我已经更新了我的查询以匹配，现在它运行得非常好。因为我测试的几个机器之间的警报文本略有不同，所以这一步对我来说并不必要。 - z_temp_string

Answer 2

我已经根据以下更新更新了您的sqlfiddle链接。在最终结果集中，您需要设置一个行号并在EV_CUSTOM1上连接回它，START_TIME = END_TIME（正如您所猜测的那样），还要行号=行号+1。这是您可以确定两个事件是否在同一时期的方法。如果您使用Sql Server 2012+，则会更加简单，因为您可以使用LAG/LEAD函数，就像@EdmondQuinton在他的答案中指出的那样。

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
          ),

T2 AS(SELECT T_1.start_time, T_1.end_time, A.Ev_Custom1,
             ROW_NUMBER() OVER (PARTITION BY EV_CUSTOM1 ORDER BY T_1.START_TIME) RN
      FROM  A 
      INNER JOIN
      T AS T_1 
      ON A.Ev_Comment LIKE T_1.start_time
      WHERE (A.Ev_Custom1 <> 'Alarms Scanned')
      )

select 
  coalesce(b.START_TIME, a.START_TIME) START_TIME, 
  max(a.END_TIME) END_TIME, 
  a.EV_CUSTOM1
from T2 a
left outer join T2 b
on a.EV_CUSTOM1 = b.EV_CUSTOM1
and a.START_TIME = b.END_TIME
and a.RN = b.RN+1
group by coalesce(b.START_TIME, a.START_TIME), 
         a.EV_CUSTOM1

- msheikh25

---

谢谢你的回答。它确实也有效。我需要在第一个“inner join”和“group by”之间添加一个WHERE语句，类似于@EdmondQuinton的帖子。非常抱歉我没有表明我需要那个条件。 - z_temp_string

由于某种原因，我得到了两个单独的警报线，持续时间超过一天。@EdmondQuinton的解决方案没有这个问题。 - z_temp_string

我无法复制那个。从您的样本数据来看，如果我将最终警报从“2016年5月23日上午11:02:00”更改为“2016年5月24日上午11:02:00”，它似乎仍然可以正常工作。如果您有其他数据，我可以帮您查看，但听起来您已经有了一个可行的解决方案。 - msheikh25