在兩個不同的列中按順序查找並刪除間隙
我嘗試使用基於集合的方法來解決這個問題。但是,由於我需要查看每一行,我認為我必須使用游標;如果我錯了,請糾正我。
桌子:
Project, item, method, start, end
該表包含多個項目、多個項目,但為了簡單起見,我將其簡化為一個項目、一個項目:
數據如下所示:
ABC, widget1, XY, 1000, 1033 ABC, widget1, XY, 1033, 1062 ABC, widget1, XY, 1062, 1112 ABC, widget1, XY, 1112, 1163 ABC, widget1, WW, 1163, 1223 ABC, widget1, WW, 1223, 1288 ABC, widget1, WW, 1288, 1334 ABC, widget1, XY, 1334, 1383 ABC, widget1, XY, 1383, 1425
我想編寫一個返回此結果的查詢:
ABC, widget1, XY, 1000, 1163 ABC, widget1, WW, 1163, 1334 ABC, widget1, XY, 1334, 1425
做這個的最好方式是什麼?
您可以儲存預先計算的差距,並使用約束來確保您預先計算的數據始終是最新的:
這是表格和第一個區間
CREATE TABLE dbo.IntegerSettings(SettingID INT NOT NULL, IntValue INT NOT NULL, StartedAt DATETIME NOT NULL, FinishedAt DATETIME NOT NULL, PreviousFinishedAt DATETIME NULL, CONSTRAINT PK_IntegerSettings_SettingID_FinishedAt PRIMARY KEY(SettingID, FinishedAt), CONSTRAINT UNQ_IntegerSettings_SettingID_PreviousFinishedAt UNIQUE(SettingID, PreviousFinishedAt), CONSTRAINT FK_IntegerSettings_SettingID_PreviousFinishedAt FOREIGN KEY(SettingID, PreviousFinishedAt) REFERENCES dbo.IntegerSettings(SettingID, FinishedAt), CONSTRAINT CHK_IntegerSettings_PreviousFinishedAt_NotAfter_StartedAt CHECK(PreviousFinishedAt <= StartedAt), CONSTRAINT CHK_IntegerSettings_StartedAt_Before_FinishedAt CHECK(StartedAt < FinishedAt) ); GO INSERT INTO dbo.IntegerSettings(SettingID, IntValue, StartedAt, FinishedAt, PreviousFinishedAt) VALUES(1, 1, '20070101', '20070103', NULL);
它有五個約束共同執行業務規則。讓我展示更複雜的工作原理。當然,有些約束很簡單,因此不需要任何解釋。
**
- 一個設置只能有一個第一個間隔
**
約束 UNQ_IntegerSettings_SettingID_PreviousFinishedAt 確保了這一點。第一個間隔沒有前一個,這意味著 PreviousFinishedAt IS NULL。UNIQUE 約束保證每個設置只能有一個這樣的行。你自己看:
INSERT INTO dbo.IntegerSettings(SettingID, IntValue, StartedAt, FinishedAt, PreviousFinishedAt) VALUES(1, 1, '20070104', '20070105', NULL); /* Server: Msg 2627, Level 14, State 2, Line 1 Violation of UNIQUE KEY constraint 'UNQ_IntegerSettings_SettingID_PreviousFinishedAt'. Cannot insert duplicate key in object 'dbo.IntegerSettings'. The statement has been terminated. */
**
- 下一個視窗必須在上一個視窗結束之後開始。
**
約束 CHK_IntegerSettings_PreviousFinishedAt_NotAfter_StartedAt 完全保證了這一點。你自己看:
INSERT INTO dbo.IntegerSettings(SettingID, IntValue, StartedAt, FinishedAt, PreviousFinishedAt) VALUES(1, 2, '20070104', '20070109', '20070105') /* Server: Msg 547, Level 16, State 1, Line 1 INSERT statement conflicted with TABLE CHECK constraint 'CHK_IntegerSettings_PreviousFinishedAt_NotAfter_StartedAt'. The conflict occurred in database 'RiskCenter', table 'IntegerSettings'. The statement has been terminated. */
**
- 兩個不同的視窗不能指代同一個視窗作為它們的前一個視窗。
**
同樣,相同的約束 UNQ_IntegerSettings_SettingID_PreviousFinishedAt 準確地保證了這一點,如下所示:
INSERT INTO dbo.IntegerSettings(SettingID, IntValue, StartedAt, FinishedAt, PreviousFinishedAt) VALUES(1, 3, '20070104', '20070115', '20070103') Msg 2627, Level 14, State 1, Line 1 Violation of UNIQUE KEY constraint 'UNQ_IntegerSettings_SettingID_PreviousFinishedAt'. Cannot insert duplicate key in object 'dbo.IntegerSettings'. The statement has been terminated.
這意味著不能有重疊。
如您所見,對於每個時間視窗,最多可以有一個在它之前,最多有一個在它之後。下一個間隔不能在其前一個結束之前開始。這兩個陳述一起意味著不能有重疊。
**
- 處理差距。
**
您可以完全禁止間隙,只需替換以下約束:
CONSTRAINT CHK_IntegerSettings_PreviousFinishedAt_NotAfter_StartedAt CHECK(PreviousFinishedAt <= StartedAt),
更嚴格的,如下:
CONSTRAINT CHK_IntegerSettings_PreviousFinishedAt_EqualTo_StartedAt CHECK(PreviousFinishedAt = StartedAt),
但是,如果您允許間隙,則檢索它們的查詢非常簡單且高效,如下所示:
SELECT PreviousFinishedAt AS GapStart, StartedAt AS GapEnd FROM dbo.IntegerSettings WHERE StartedAt > PreviousFinishedAt;
在這種情況下,您不需要游標,而且我敢打賭,CTE 將比任何基於游標或循環的方法來解決這個問題要好得多。
以下查詢為您提供了您所需要的確切資訊。我在 SQL Server 2008 上對其進行了測試,但如果您忽略設置塊並替換
@table
為目標表的名稱,您應該能夠在任何支持 CTE(如 Oracle、SQL Server 或 PostgreSQL)的平台上執行它。-- setup DECLARE @table TABLE ( project VARCHAR(10) NOT NULL , item VARCHAR(20) NOT NULL , method CHAR(2) NOT NULL , start INT NOT NULL , [end] INT NOT NULL ); INSERT INTO @table VALUES ('ABC', 'widget1', 'XY', 1000, 1033) , ('ABC', 'widget1', 'XY', 1033, 1062) , ('ABC', 'widget1', 'XY', 1062, 1112) , ('ABC', 'widget1', 'XY', 1112, 1163) , ('ABC', 'widget1', 'WW', 1163, 1223) , ('ABC', 'widget1', 'WW', 1223, 1288) , ('ABC', 'widget1', 'WW', 1288, 1334) , ('ABC', 'widget1', 'XY', 1334, 1383) , ('ABC', 'widget1', 'XY', 1383, 1425) ; -- query WITH connected_ranges AS ( SELECT right_range.project , right_range.method , right_range.item , right_range.start , right_range.[end] FROM @table left_range RIGHT OUTER JOIN @table right_range ON right_range.project = left_range.project AND right_range.item = left_range.item AND right_range.method = left_range.method AND right_range.start = left_range.[end] WHERE left_range.project IS NULL UNION ALL SELECT right_range.project , right_range.method , right_range.item , left_range.start , right_range.[end] FROM connected_ranges left_range INNER JOIN @table right_range ON right_range.project = left_range.project AND right_range.item = left_range.item AND right_range.method = left_range.method AND right_range.start = left_range.[end] ) --SELECT * --FROM connected_ranges --ORDER BY -- project -- , method -- , item -- , start -- , [end] --; SELECT project , method , item , start , MAX([end]) AS [end] FROM connected_ranges GROUP BY project , method , item , start ;
總結一下我所做的:我使用遞歸 CTE 將所有連續的段連接在一起,從最左邊開始,一直到右邊。然後,在決賽中,
SELECT
我只拉出最大的、不重疊的部分。