毫無疑問，以前出現過類似的問題，但我無法通過搜索找到它......
我有一個包含時間序列資料的原始資料集，包括“從”和“到”日期欄位。
問題是，加載資料時，即使沒有值發生更改，也會創建新記錄（“到”日期添加到舊記錄，新記錄“從”加載日期）。我想將其轉換為一個表格，該表格僅顯示每個真正更改的一行 - 以及反映這一點的開始/結束日期。
例如，源資料如下所示：
| ID | Col1 | Col2 | Col3 | 從 | 到 |
|---|---|---|---|---|---|
| 測驗1 | 1 | 1 | 1 | 2020 年 1 月 1 日 | 9999 年 12 月 31 日 |
| 測驗2 | 1 | 2 | 3 | 2020 年 1 月 1 日 | 2020 年 6 月 30 日 |
| 測驗2 | 1 | 2 | 3 | 2020 年 1 月 7 日 | 2020 年 9 月 30 日 |
| 測驗2 | 3 | 2 | 1 | 2020 年 1 月 10 日 | 9999 年 12 月 31 日 |
Test2 的前兩條記錄（第 2 行和第 3 行）基本相同 - 在 2020 年 1 月 7 日加載第二行時沒有變化。我想要 2020 年 1 月 1 日至 2020 年 9 月 30 日期間沒有變化的單行：
| ID | Col1 | Col2 | Col3 | 從 | 到 |
|---|---|---|---|---|---|
| 測驗1 | 1 | 1 | 1 | 2020 年 1 月 1 日 | 9999 年 12 月 31 日 |
| 測驗2 | 1 | 2 | 3 | 2020 年 1 月 1 日 | 2020 年 9 月 30 日 |
| 測驗2 | 3 | 2 | 1 | 2020 年 1 月 10 日 | 9999 年 12 月 31 日 |
對于這個簡化的示例，我可以通過按每列（日期除外）分組并使用日期/最大結束日期的 MIN 來實作：
SELECT
ID, Col1, Col2, Col3, MIN(From) AS From, MAX(To) as TO
FROM TABLE
GROUP BY ID, Col1, Col2, Col3
但是，如果值發生變化然后隨后又變回之前的值，這將不起作用，例如
| ID | Col1 | Col2 | Col3 | 從 | 到 |
|---|---|---|---|---|---|
| 測驗1 | 1 | 1 | 1 | 2020 年 1 月 1 日 | 9999 年 12 月 31 日 |
| 測驗2 | 1 | 2 | 3 | 2020 年 1 月 1 日 | 2020 年 4 月 30 日 |
| 測驗2 | 1 | 2 | 3 | 2020 年 1 月 5 日 | 2020 年 6 月 30 日 |
| 測驗2 | 3 | 2 | 1 | 2020 年 1 月 7 日 | 2020 年 10 月 30 日 |
| 測驗2 | 1 | 2 | 3 | 2020 年 1 月 11 日 | 9999 年 12 月 31 日 |
只需在上面的代碼中使用 MIN/MAX 就會回傳這個 - 所以看起來兩組值在 2020 年 1 月 7 日至 2020 年 10 月 30 日期間都有效：
| ID | Col1 | Col2 | Col3 | 從 | 到 |
|---|---|---|---|---|---|
| 測驗1 | 1 | 1 | 1 | 2020 年 1 月 1 日 | 9999 年 12 月 31 日 |
| 測驗2 | 1 | 2 | 3 | 2020 年 1 月 1 日 | 9999 年 12 月 31 日 |
| 測驗2 | 3 | 2 | 1 | 2020 年 1 月 7 日 | 2020 年 10 月 30 日 |
Whereas actually the first set of values were valid before and after that period, but not during. It should return a single row for instead of two for the period from 01/01/2020 - 30/06/2020 when there were no changes for this ID, but then another row for the period when the values were different, and then another row where it reverted to the initial values, but with a new From date.
| ID | Col1 | Col2 | Col3 | From | To |
|---|---|---|---|---|---|
| Test1 | 1 | 1 | 1 | 01/01/2020 | 31/12/9999 |
| Test2 | 1 | 2 | 3 | 01/01/2020 | 30/06/2020 |
| Test2 | 3 | 2 | 1 | 01/07/2020 | 30/10/2020 |
| Test2 | 1 | 2 | 3 | 01/11/2020 | 31/12/9999 |
I'm struggling to conceptualise how to approach this. I'm guessing I need to use LAG somehow but not sure how to apply it - eg rank everything in a staging table first, then use LAG to compare a concatenation of the whole row?
I'm sure I could find a fudged way eventually, but I've no doubt this problem has been solved many times before so hoping somebody can point me to a simpler/ neater solution than I'd inevitably come up with...
uj5u.com熱心網友回復：
先進的差距和島嶼
我相信這是一個高級的“差距和島嶼”問題。使用它作為搜索詞，您會發現大量關于該主題的文獻。唯一的區別是通常只跟蹤一列，但你有 3。
無間隙假設
這個腳本的一個主要假設是重疊日期沒有間隔，或者換句話說，它假設前面的行 ToDate = current FromDate - 1 天。不確定您是否需要考慮差距，只需將條件添加到 IsChanged 即可檢查
多列間隙和孤島解決方案
DROP TABLE IF EXISTS #Grouping
DROP TABLE IF EXISTS #Test
CREATE TABLE #Test (ID INT IDENTITY(1,1),TestName Varchar(10),Col1 INT,Col2 INT,Col3 INT,FromDate Date,ToDate DATE)
INSERT INTO #Test VALUES
('Test1',1,1,1,'2020-01-01','9999-12-31')
,('Test2',1,2,3,'2020-01-01','2020-04-30')
,('Test2',1,2,3,'2020-05-01','2020-06-30')
,('Test2',3,2,1,'2020-07-01','2020-10-30')
,('Test2',1,2,3,'2020-11-01','9999-12-31')
;WITH cte_Prev AS (
SELECT *
,PrevCol1 = LAG(Col1) OVER (PARTITION BY TestName ORDER BY FromDate)
,PrevCol2 = LAG(Col2) OVER (PARTITION BY TestName ORDER BY FromDate)
,PrevCol3 = LAG(Col3) OVER (PARTITION BY TestName ORDER BY FromDate)
FROM #Test
), cte_Compare AS (
SELECT *
,IsChanged = CASE
WHEN Col1 = PrevCol1
AND Col2 = PrevCol2
AND Col3 = PrevCol3
THEN 0 /*No change*/
ELSE 1 /*Iterate so new group created */
END
FROM cte_Prev
)
SELECT *,GroupID = SUM(IsChanged) OVER (PARTITION BY TestName ORDER BY ID)
INTO #Grouping
FROM cte_Compare
/*Raw unformatted data so you can see how it works*/
SELECT *
FROM #Grouping
/*Aggregated results*/
SELECT GroupID,TestName,Col1,Col2,Col3
,FromDate = MIN(FromDate)
,ToDate = MAX(ToDate)
,NumberOfRowsCollapsedIntoOneRow = COUNT(*)
FROM #Grouping
GROUP BY GroupID,TestName,Col1,Col2,Col3
轉載請註明出處，本文鏈接：https://www.uj5u.com/houduan/425365.html