想象一下，您有一個資料源，例如：
| ID | 瓦爾 | 資料_日期 |
|---|---|---|
| 1 | 一個 | 2022-01-01 |
| 2 | 乙 | 2022-01-05 |
| 3 | C | 2022-01-09 |
| 4 | D | 2022-01-31 |
| 5 | 乙 | 2022-02-01 |
用這種方式匹配值的參考表：
| 目標值 | 匹配值 | Valid_Start | Valid_End |
|---|---|---|---|
| 乙 | 一個 | 2022-01-04 | 2022-01-06 |
| C | 乙 | 2022-01-09 | 2022-01-09 |
| D | 一個 | 2022-01-31 | 2022-01-31 |
想象一下，您想創建一個表，將值分組在一起，其中 X 天內參考表中存在匹配項，例如 4。并且您希望遞回地應用此匹配項。
輸出將是這樣的：
| Group_Id | ID |
|---|---|
| 1 | 1 |
| 1 | 2 |
| 1 | 3 |
| 2 | 4 |
| 3 | 5 |
這里的邏輯是 C 在適當的日期范圍內與 B 匹配，B 在適當的日期范圍內與 A 匹配，因此它們都是一組。但是盡管 D 與 A 匹配，但相距太遠（大于 4 天）。E 不匹配任何東西。
可以有任何深度（A > B > C > D ...）
SQL 中是否有適當的演算法來完成此任務？組 ID 的值并不重要，僅用于將資料點組合在一起。
uj5u.com熱心網友回復：
這是我的嘗試。您確實需要遞回 CTE，但您需要將源表連接到組表，然后再連接回源表，以確保孩子適合父母的 4 天視窗。例如，在 D 和 A 的情況下，正如您所提到的，它們匹配，但它們還不夠接近，無法計算。
然后我添加了一個計算來確定哪些行是有效的層次結構并將其用于遞回連接，因為我們可以排除不屬于層次結構的任何內容。
之后，我們需要按記錄的深度對記錄進行排序，以便我們知道哪個父記錄在前，例如在 A > B > C 的情況下。
然后對結果進行 DENSE_RANK 以獲得您的最終組。雖然這需要一些更深層次的遞回測驗，但這應該為您指明正確的方向：
CREATE TABLE SourceData
(
Id INTEGER,
Val CHAR(1),
Data_Date DATE
);
CREATE TABLE Groups
(
Target_Val CHAR(1),
Matching_Val CHAR(1),
Valid_Start DATE,
Valid_End DATE
);
INSERT INTO SourceData (Id, Val, Data_Date) VALUES (1,'A','2022-01-01');
INSERT INTO SourceData (Id, Val, Data_Date) VALUES (2,'B','2022-01-05');
INSERT INTO SourceData (Id, Val, Data_Date) VALUES (3,'C','2022-01-09');
INSERT INTO SourceData (Id, Val, Data_Date) VALUES (4,'D','2022-01-31');
INSERT INTO SourceData (Id, Val, Data_Date) VALUES (5,'E','2022-02-01');
INSERT INTO Groups (Target_Val, Matching_Val, Valid_Start, Valid_End ) VALUES ('B','A','2022-01-04','2022-01-06');
INSERT INTO Groups (Target_Val, Matching_Val, Valid_Start, Valid_End ) VALUES ('C','B','2022-01-09','2022-01-09');
INSERT INTO Groups (Target_Val, Matching_Val, Valid_Start, Valid_End ) VALUES ('D','A','2022-01-31','2022-01-31');
WITH sourceCTE AS
(
SELECT sd.Id, sd.Val, sd.Data_Date, g.Valid_Start, g.Valid_End, IIF(s.Val IS NULL, sd.Val, g.Matching_Val) [ParentVal], CAST(NULL AS DATE) [start], CAST(NULL AS DATE) [end], 1 [Depth],
IIF(s.Val IS NULL, 0, 1) IsHeirarchy
FROM SourceData sd
LEFT JOIN Groups g ON g.Target_Val = sd.Val AND sd.Data_Date BETWEEN g.Valid_Start AND g.Valid_End
LEFT JOIN SourceData s ON s.Val = g.Matching_Val AND ABS(DATEDIFF(DAY, s.Data_Date, sd.Data_Date)) < 5
UNION ALL
SELECT s.Id, s.Val, s.Data_Date, g.Valid_Start, g.Valid_End, g.Matching_Val, g.Valid_Start, g.Valid_End, s.[Depth] 1, 1
FROM sourceCTE s
INNER JOIN Groups g ON g.Target_Val = s.[ParentVal] AND s.IsHeirarchy = 1
),
ResultCTE AS
(
SELECT *, ROW_NUMBER() OVER (PARTITION BY Id ORDER BY [Depth] DESC) [RNum]
FROM sourceCTE
)
SELECT DENSE_RANK() OVER (ORDER BY ParentVal) [Group_Id], Id
FROM ResultCTE
WHERE [RNum] = 1
這是一個作業小提琴。
我不能保證這是最好的解決方案，因為就像我在大約 2 小時后放棄的查詢優化器一樣，哈。
此外，對于任何未來的問題，請以腳本格式提供示例資料，以節省創建結構的時間。
轉載請註明出處，本文鏈接：https://www.uj5u.com/houduan/492901.html