SQL将事件列表转换为持续时间(MSSQL 2008)

Question

假设我有一个带有各种事件及其开始的表：

EventTime              EventCode         EventDescription
---------              ---------         ----------------
2014-01-23 00:03:32    430               Running
2014-01-23 00:04:35    430               Running
2014-01-23 00:06:37    430               Running
2014-01-23 00:11:45    430               Running
2014-01-23 00:32:46    721               Stopped
2014-01-23 00:34:49    721               Stopped
2014-01-23 00:41:52    430               Running
2014-01-23 01:03:54    721               Stopped    
2014-01-23 01:04:55    430               Running

从这些数据中，我想获得所有间隔的开始时间和持续时间。意思是，使用Event Code 721的记录的启动时间以及与Event Code 430一起连续的事件的开始时间，另一个是：

START                 END                   STATUS
-----------           ----------            ----------------
2014-01-23 00:03:32   2014-01-23 00:32:46   Running
2014-01-23 00:32:46   2014-01-23 00:41:52   Stopped
2014-01-23 00:41:52   2014-01-23 01:03:54   Running
2014-01-23 01:03:54   2014-01-23 01:04:55   Stopped

我已经看到了"超级语法"的用法，但是在我看到的示例中，它被用于数据集中的连续行。该表A具有彼此之后的许多相同状态。

如示例中，哪种转换该数据的最佳（也是最出色的）方法是什么？谢谢！

Answer 1

做到这一点的一种方法

WITH cte AS 
(
  SELECT eventcode, eventdescription, MIN(eventtime) starttime, MAX(eventtime) endtime,
         ROW_NUMBER() OVER (ORDER BY MIN(eventtime)) rnum
    FROM
  (
    SELECT t.*,
           ROW_NUMBER() OVER (ORDER BY eventtime) rnum,
           ROW_NUMBER() OVER (PARTITION BY eventcode ORDER BY eventtime) grnum
      FROM table1 t
  ) q
   GROUP BY eventcode, eventdescription, rnum - grnum
)
SELECT t1.starttime, t2.starttime endtime, t1.eventdescription status
  FROM cte t1 JOIN cte t2
    ON t1.rnum = t2.rnum - 1
 ORDER BY starttime, endtime

输出：

|开始时间|末日|状态|| -------------------------------------------------------------------------------------------------- | ------------------------- ||2014年1月23日00：03：32 0000 |2014年1月23日00：32：46 0000 |运行||2014年1月23日00：32：46 0000 |2014年1月23日00：41：52 0000 |停止||2014年1月23日00：41：52 0000 |2014年1月23日01：03：54 0000 |运行||2014年1月23日01：03：54 0000 |2014年1月23日01：04：55 0000 |停止|

这是 sqlfiddle demo

Answer 2

这与Peterm的答案相似，只有一个少一个row_number ...

您可能会将左联接更改为内部：

WITH cte (EventTime, EventCode, EventDescription, rn, rn2) AS 
 (
   SELECT
      EventTime, EventCode, EventDescription,
      ROW_NUMBER() OVER (ORDER BY EventTime) AS rn,
      ROW_NUMBER() OVER (PARTITION BY EventCode ORDER BY EventTime) AS rn2
   FROM Table1
 )
SELECT MIN(t1.EventTime), MAX(t2.EventTime), t1.EventDescription
FROM cte AS t1 LEFT JOIN cte AS t2
ON t2.rn = t1.rn+1
GROUP BY t2.rn-t1.rn2, t1.EventDescription
ORDER BY 1