T-SQL：基於時間切換週期的分組

我從類型 2 漸變維度中提取了下表。我想將行分組以僅按時間順序具有不同的分類及其各自的“切換”時間段。一個簡單的 GROUP BY 將不起作用，因為可以“返回”的分類，例如 RowNum 5 中“返回”的分類“K”。

目前數據：

所需數據：

我厭倦了使用 SUM OVER PARTITION BY 等視窗函式，但找不到一種優雅的方法。任何幫助，將不勝感激。

SQLFiddle 似乎下線了，我使用 StackExchangeDE創建了帶有遊樂場值的表

嘗試這樣的方法。

首先使用 LAG 辨識已更改的行。對這些變化點進行匯總。一旦你知道你可以進行分組，但包括組。

DROP TABLE IF EXISTS #Source
CREATE TABLE #Source (RowNum_Helper INT, ItemID CHAR(1), Classification CHAR(1), Start_Date DATETIME2, End_Date DATETIME2)

INSERT INTO #Source
SELECT 1, 'X','K','2021-01-07 02:02:44.060', '2021-01-08 02:03:45.980' UNION ALL
SELECT 2, 'X','K','2021-01-08 02:03:45.980', '2021-01-31 20:03:38.993' UNION ALL
SELECT 3, 'X','N','2021-01-31 20:03:38.993', '2021-03-26 14:44:08.617' UNION ALL
SELECT 4, 'X','N','2021-03-26 14:44:08.617', '2021-04-08 02:02:51.013' UNION ALL
SELECT 5, 'X','K','2021-04-08 02:02:51.013', '2021-10-13 02:02:51.013' UNION ALL
SELECT 6, 'X','N','2021-10-13 02:02:51.013', '2021-10-14 02:02:51.013' UNION ALL
SELECT 7, 'X','K','2021-10-14 02:02:51.013', '9999-12-31 23:59:59.999' UNION ALL
SELECT 8, 'A','K','2021-06-14 02:02:51.013', '9999-12-31 23:59:59.999'
Go

WITH IdentifyChangedRows
AS (SELECT RowNum_Helper,
          ItemID,
          Classification,
          Start_Date,
          End_Date,
          LAG(Classification) OVER (PARTITION BY ItemID ORDER BY Start_Date) AS Classification_Lag
   FROM #Source S
  ),
    CreateGroups
AS (SELECT RowNum_Helper,
          ItemID,
          Classification,
          Start_Date,
          End_Date,
          SUM(   CASE
                     WHEN Classification_Lag = Classification
                          OR Classification_Lag IS NULL THEN
                         0
                     ELSE
                         1
                 END
             ) OVER (ORDER BY RowNum_Helper
                     ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
                    ) AS ChangeGroup
   FROM IdentifyChangedRows
  )
SELECT ItemID,
      Classification,
      MIN(Start_Date) Start_Date,
      MAX(End_Date) End_Date
FROM CreateGroups G
GROUP BY ItemID,
        Classification,
        ChangeGroup
ORDER BY ItemID DESC,
        Start_Date

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