如何計算匹配上一個事件狀態的連續事件數？

在 MS-SQL Server 2008 中，我有一個以下格式的事件表：

Controller   |  ExecutionTime        |  Result
1            |  2012-09-24 09:00:00  | 0
1            |  2012-09-24 09:01:00  | 0
2            |  2012-09-24 09:02:00  | 1

我需要分析此表以產生以下結果：

• 辨識其最新事件是錯誤的每個控制器（結果=1）
• 辨識控制器連續失敗的次數（自最近一次成功事件以來的 # 個事件）

我在這項任務上取得了一些進展，我可以辨識最近事件失敗的控制器 - 請參閱**此 SQL Fiddle**。但是我可以使用一些幫助來找到一種方法來計算導致這種狀態的連續失敗的數量。

謝謝！約翰

這是一個解決方案。恐怕不是很有效率：

SELECT 
   t.Controller,
   MIN(t.ExecutionTime) AS FirstFaultInSeries,
   MAX(t.ExecutionTime) AS LatestFault,
   COUNT(*) AS CntFaults
FROM
 ( SELECT 
       Controller, 
       COALESCE(MAX(CASE WHEN Result = 0 THEN ExecutionTime END)
               ,'19000101')
         AS LatestGood
   FROM t    
   GROUP BY Controller
 ) g
 JOIN t
   ON t.Controller = g.Controller
  AND t.ExecutionTime > g.LatestGood
GROUP BY
   t.Controller ;

測試：小提琴

還有一個：

WITH cte AS
 ( SELECT 
       Controller, ExecutionTime, Result,
       ROW_NUMBER() OVER (PARTITION BY Controller
                          ORDER BY ExecutionTime DESC
                         )
         AS Rn,
       ROW_NUMBER() OVER (PARTITION BY Controller, Result
                          ORDER BY ExecutionTime DESC
                         )
         AS RnResult
   FROM t
 ) 
SELECT 
   Controller,
   MIN(ExecutionTime) AS FirstFaultInSeries,
   MAX(ExecutionTime) AS LatestFault,
   COUNT(*) AS CntFaults
FROM
   cte 
WHERE
   Result = 1
 AND
   Rn = RnResult
GROUP BY
   Controller ;

引用自：https://dba.stackexchange.com/questions/44499