連續3筆異常即發出告警

許多異常告警系統或者SPC統計製程控制（Statistical Process Control）的異常判斷及處理中，常以連續7點出現在管制中心線的一側或連續7點出現持續上升或下降等訊號為異常判定原則，本範例將簡化以連續3點為判定原則，資料及結果如下：

資料		結果
		(1): 5~7 (2): 10~12

以下提供列出二種作法，首先，MSSQL2012或ORACLE可使用LEAD、LAG分析函數以取得前後期資料即可完成，對於連續3點之計算，此方法相當便捷，但對於如SPC統計製程控制之7點需求，則可嘗試另一種作法，分別說明如下：

1.  LEAD、LAG分析函數

利用LEAD分析函數取得次一、二期資料加上本期共三期組成狀況字串，以判斷是否三期均為N。但由於每一期需再增加一組判斷條件式，期(點)數較多時則SQL趨於複雜，則建議使用方法-2之作法

SELECT

     Id St

   , Next_ID2 Ed

   , CONCAT(Id, ',',  Next_Id1, ',',  Next_Id2)

FROM

     (

     SELECT Id

         , Judge      

         , LEAD(Judge, 1) OVER(ORDER BY Id)  Next_Judge1

         , LEAD(Judge, 2) OVER(ORDER BY Id)  Next_Judge2

         , LEAD(Id,    1) OVER(ORDER BY Id)  Next_Id1

         , LEAD(Id,    2) OVER(ORDER BY Id)  Next_Id2

      FROM #Meas

      ) A

WHERE AND CONCAT(Judge, Next_Judge1, Next_Judge2) = 'NNN'

2.  共線概念

以下將利用直線方程式（Y= a + bX）概念，利用直線共線之概念確認資料是否連續，首先將所有測試點依序逐點建立流水序號為X，再者將符合篩選條件者依序逐點建立流水序號為Y，對於直線方程式，重要參數為截距（a）與斜率（b），而由於為自建而產生之一對一對應關係數值，因此可將視斜率固定為1之直線方程式，唯一的變（未知）數為截距（a），方程式修正為 a = Y – X。以下將資料及繪製成二維座標系，如下圖。如前述，截距（a）為直線方程式中唯一變數，當共線時則截距（a）必然相同，因此據以分群即為所求。

資料		座標

SQL及結果如下:

SQL		結果
WITH Meas AS ( SELECT *       , ROW_NUMBER() OVER(ORDER BY Id) X       , ROW_NUMBER() OVER(PARTITION BY Judge ORDER BY Id) Y FROM #Meas --ORDER BY Id ) SELECT MIN(Id) St       , Max(Id) Ed       , COUNT(*) Row_Cnt       , Y-X a FROM Meas WHERE 1=1        AND Judge = 'N' GROUP BY Y-X HAVING(COUNT(*)=3)

此方法相對於LEAD/LAG分析函數作法更為彈性，且MSSQL2005版本即可使用，雖概念上有些許難度，但此方法用途極為廣乏，值得研究。

範例資料

SELECT *   INTO #Meas

FROM (VALUES(1, 'Y')

    , (3, 'N')

    , (4, 'Y')

    , (5, 'N') --

    , (6, 'N') --

    , (7, 'N') --

    , (8, 'Y')

    , (9, 'Y')

    , (10, 'N') --

    , (11, 'N') --

    , (12, 'N') --

) Meas(Id, Judge)