視窗函式獲取記錄對併計算時間差

我正在使用以下範例中的事件數據集：

Event_Type  Event_Timestamp         Is_Active
A           2010-10-01 00:00:00     1
B           2010-10-01 00:00:01     1
A           2010-10-01 00:00:02     0
D           2010-10-01 00:00:03     1
B           2010-10-01 00:00:04     0
C           2010-10-01 00:00:05     1
A           2010-10-01 00:00:06     1
A           2010-10-01 00:00:07     1
A           2010-10-01 00:00:08     0

數據集按事件的時間戳排序，數據隨著事件的實時發生而增長。數據集中可以隨時包含更多事件類型（不僅限於範例中的 A、B、C、D），並且同一事件類型可以多次出現在表中。Is_Active 布爾欄位用作指示事件是否仍處於活動狀態 (1) 或不處於活動狀態 (0) 的一種方式。

從這個意義上說，我嘗試使用 SQL 視窗函式對這些數據進行一些轉換。我不一定限於特定的產品或技術，因此請隨時告訴您如何解決以下問題。

我想要做的是當它們具有相反的 Is_Active 值時動態配對相同類型的每個事件，然後獲取該事件的活動時間。換句話說，給定一個事件 X，我需要在 Is_Active 第一次為 1 時獲取它的 Event_Timestamp（Begin_Timestamp），然後使用 Is_Active 1 忽略此事件 X 的其餘行，直到我得到 Is_Active 0 ，所以我可以再次獲取 Event_Timestamp (End_Timestamp)。然後，當我再次在 Is_Active 列中發現事件 X 為 1 時，我將繼續應用此邏輯。

結果表的一個範例是：

Event_Type  Begin_Timestamp         End_Timestamp           Duration
A           2010-10-01 00:00:00     2010-10-01 00:00:02     2 seconds
B           2010-10-01 00:00:01     2010-10-01 00:00:04     3 seconds
D           2010-10-01 00:00:03     null                    null
C           2010-10-01 00:00:05     null                    null
A           2010-10-01 00:00:06     2010-10-01 00:00:08     2 seconds

是否有任何視窗函式可以幫助我獲取這些事件對，以便我可以計算每個事件的持續時間？

你需要類似的東西

-- get previous status, use -1 value if no previous record (because NULL value needs more complex condition in cte2)
WITH cte1 AS (SELECT Event_Type, 
                    Event_Timestamp, 
                    Is_Active, 
                    COALESCE(LAG(Is_Active) OVER (PARTITION BY Event_Type 
                                                  ORDER BY Event_Timestamp ASC), -1) prev_event
             FROM datatable),
    -- remove records where status not altered
    cte2 AS (SELECT Event_Type, 
                    Event_Timestamp, 
                    Is_Active
             FROM cte1
             WHERE Is_Active != prev_event)
-- get data we need
SELECT Event_Type, 
      Event_Timestamp Begin_Timestamp, 
      LEAD(Event_Timestamp) OVER (PARTITION BY Event_Type 
                                  ORDER BY Event_Timestamp ASC) End_Timestamp, 
      LEAD(Event_Timestamp) OVER (PARTITION BY Event_Type 
                                  ORDER BY Event_Timestamp ASC) - Event_Timestamp Duration
FROM cte2
WHERE Is_Active = 1

當然，您可以使用一個計算所有視窗值的 CTE 將其組合到查詢中。上面的long way是用來解釋算法的。

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