Sql-Server

合併更新的日期範圍

  • October 4, 2017

給定一個日期範圍表，例如Start Date, End Date，我在網上看到了一些關於如何即時合併範圍的範例，但我能夠在使用者進行更新時合併它們。

因此，例如，如果01/01to01/02存在並且使用者插入15/01to 15/02，那麼原始範圍可以更新為01/01to 15/02。

有沒有一種眾所周知的方法來處理這個問題，還是我需要把它全部解決並將它放在一個儲存過程中？

桌子長這樣…

CREATE TABLE [dbo].[dateRange](
  [Id] [int] NOT NULL,
  [Start] [datetime] NOT NULL,
  [Finish] [datetime] NOT NULL
)

給定兩個範圍…

INSERT INTO dateRange (Id, Start, Finish) VALUES (1, '01-01-17', '01-02-17');
INSERT INTO dateRange (Id, Start, Finish) VALUES (2, '03-01-17', '01-04-17');

使用者可以刪除、更新和插入新範圍。刪除很簡單。我認為插入有三種情況（更新有類似的可能性）……

  1. 新範圍涵蓋現有範圍…
INSERT INTO dateRange (Id, Start, Finish) VALUES (3, '15-12-16', '15-02-17');

導致刪除 1。 2. 新範圍被現有範圍覆蓋。所以…

INSERT INTO dateRange (Id, Start, Finish) VALUES (4, '15-01-17', '16-01-17');

.. 被忽略。 3. 新範圍與新範圍部分重疊。

INSERT INTO dateRange (Id, Start, Finish) VALUES (5, '15-12-16', '15-01-17');

再次，插入被忽略，重疊的行被擴展。如果新範圍的兩端重疊，則更複雜，因為需要刪除重疊的範圍之一。

視窗函式聽起來很酷，但我正在考慮用 C# 來做。

對於每個使用者，我想要零重疊日期範圍。我正在使用 SQL Server 2014。

在我給你的一條評論中

我正在考慮使用 DML 觸發器解決您的問題。這將允許在數據庫引擎中完成工作，而無需將所有數據返回到 C# 進行評估 - 您是否願意接受一種解決方案，該解決方案可以清除目前數據並以正確的開始/佈置一組新的行/根據最近的 DML 操作完成日期？它不會像更新現有行那樣優雅。

你說（解釋）

我認為值得一看，但我傾向於避免以這種方式洩露數據……您能否至少簡要介紹一下您的解決方案？

好吧，今天是一個緩慢的工作日，因為還沒有其他人上台回答，所以我會走到麥克風前，為質問者做準備。這是一個沒有任何性能考慮（索引等）的原型。即使有適當的索引，它也可能不適合您的需要。當然，如果我誤解了你的問題（或者更糟糕的是，有一個致命的缺陷），請輕鬆投反對票；）

我試圖用KISS原則來解決這個問題。我的方法依賴於一個表觸發器，它刪除現有數據並在執行插入、更新和刪除時佈置新的時間線。它在很大程度上依賴於我從 Itzik Ben-Gan 的題為“包裝間隔問題的新解決方案”的文章中得到的邏輯。您絕對應該閱讀 Itzik 的文章以完全理解我的觸發器中的邏輯。

您最初的問題表明“對於每個使用者，我想要零重疊日期範圍。 ”。我冒昧地在您的表中添加了一個 UserID 列。我的表還使用 IDENTITY 作為 Id 列。由於我的解決方案會清除現有數據並插入新的時間線，因此我沒有在插入時指定 Id 列。

IF OBJECT_ID('dbo.DateRange') IS NOT NULL 
   DROP TABLE dbo.DateRange

CREATE TABLE [dbo].[DateRange](
  [Id] [int] IDENTITY(1,1) NOT NULL,
  [UserId] VARCHAR(10) NOT NULL,
  [Start] [datetime] NOT NULL,
  [Finish] [datetime] NOT NULL
)
go

這是真正有趣的觸發器

CREATE TRIGGER dbo.DateRange_Merge_Ranges 
  ON  dbo.DateRange 
  AFTER INSERT,DELETE,UPDATE
AS 
BEGIN
   SET NOCOUNT ON;

   --Dropping temp tables used in the trigger
   IF OBJECT_ID('tempdb..DataToInsert') IS NOT NULL 
       DROP TABLE #DataToInsert;
   IF OBJECT_ID('tempdb..UniqueUserIdsAffected') IS NOT NULL 
       DROP TABLE #UniqueUserIdsAffected;

   --Get all UserId's affected by the Insert, Update, Delete
   SELECT * INTO #UniqueUserIdsAffected FROM
   (
   select UserId from inserted
   union
   select UserId from deleted
   ) a

   --Calculate a new column called prvend which contains the 'previous' rows Finish date
;   WITH C1 AS
   (
       SELECT a.*,
           MAX(Finish) OVER(PARTITION BY a.UserId
                           ORDER BY a.start, a.finish
                           ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING) AS prvend
       FROM dbo.DateRange a join
       #UniqueUserIdsAffected b on 
           b.UserId = a.UserId
   ),

   --Determine whether each row starts a new 'packed-interval' - call the column grp
   C2 AS
   (
   SELECT *,
       SUM(isstart) OVER(PARTITION BY C1.UserId
                       ORDER BY C1.Start, C1.Finish
                       ROWS UNBOUNDED PRECEDING) AS grp
   FROM C1
   CROSS APPLY ( VALUES(CASE WHEN Start <= prvend THEN 0 ELSE 1 END) ) AS A(isstart)
   )

   --Insert the results into a local temp table because we're going to delete
   --all rows fromthe real table matching the UserId's affected in the Insert, Update, Delete
   SELECT C2.UserId, MIN(C2.Start) AS Start, MAX(C2.Finish) AS Finish into #DataToInsert
   FROM C2 
   group by C2.UserId, C2.grp;

   --Delete all rows fromthe real table matching the UserId's affected in the Insert, Update, Delete
   DELETE a from
       dbo.DateRange a join
       #UniqueUserIdsAffected b on b.UserId = a.UserId

   --Insert into the real table the newly calculated time slices
   INSERT INTO dbo.DateRange(UserId, Start, Finish)
       SELECT UserId, Start, Finish from #DataToInsert

END
GO

現在我們已經定義了觸發器的表，讓我們插入一些行，我正在為 TOM 和 JOE 插入行。我的 DML 範例處理的是 TOM，但我想讓您看到，在保持更新屬於誰的隔離方面，我並沒有什麼魔法。

INSERT INTO DateRange (UserId, Start, Finish) VALUES ('TOM', '2017-01-01', '2017-02-01');
INSERT INTO DateRange (UserId, Start, Finish) VALUES ('TOM', '2017-03-01', '2017-04-01');
INSERT INTO DateRange (UserId, Start, Finish) VALUES ('JOE', '2017-01-01', '2017-02-01');
INSERT INTO DateRange (UserId, Start, Finish) VALUES ('JOE', '2017-03-01', '2017-04-01');
select * from DateRange order by UserId, Start, Finish

| UserId | Start                   | Finish                  |
|--------|-------------------------|-------------------------|
| JOE    | 2017-01-01 00:00:00.000 | 2017-02-01 00:00:00.000 |
| JOE    | 2017-03-01 00:00:00.000 | 2017-04-01 00:00:00.000 |
| TOM    | 2017-01-01 00:00:00.000 | 2017-02-01 00:00:00.000 |
| TOM    | 2017-03-01 00:00:00.000 | 2017-04-01 00:00:00.000 |

以下每個案例都建立在前一個案例的基礎上（如果存在）

案例1：新範圍覆蓋現有範圍……

INSERT INTO DateRange (UserId, Start, Finish) VALUES ('TOM', '2016-12-15', '2017-02-15');

| UserId | Start                   | Finish                  |
|--------|-------------------------|-------------------------|
| JOE    | 2017-01-01 00:00:00.000 | 2017-02-01 00:00:00.000 |
| JOE    | 2017-03-01 00:00:00.000 | 2017-04-01 00:00:00.000 |
| TOM    | 2016-12-15 00:00:00.000 | 2017-02-15 00:00:00.000 |
| TOM    | 2017-03-01 00:00:00.000 | 2017-04-01 00:00:00.000 |

情況2：新範圍被現有範圍覆蓋。所以…

INSERT INTO DateRange (UserId, Start, Finish) VALUES ('TOM', '2017-01-15', '2017-01-16');

| UserId | Start                   | Finish                  |
|--------|-------------------------|-------------------------|
| JOE    | 2017-01-01 00:00:00.000 | 2017-02-01 00:00:00.000 |
| JOE    | 2017-03-01 00:00:00.000 | 2017-04-01 00:00:00.000 |
| TOM    | 2016-12-15 00:00:00.000 | 2017-02-15 00:00:00.000 |
| TOM    | 2017-03-01 00:00:00.000 | 2017-04-01 00:00:00.000 |

案例 3：新範圍與新範圍部分重疊。

INSERT INTO DateRange (UserId, Start, Finish) VALUES ('TOM', '2016-12-15', '2017-01-15');

| UserId | Start                   | Finish                  |
|--------|-------------------------|-------------------------|
| JOE    | 2017-01-01 00:00:00.000 | 2017-02-01 00:00:00.000 |
| JOE    | 2017-03-01 00:00:00.000 | 2017-04-01 00:00:00.000 |
| TOM    | 2016-12-15 00:00:00.000 | 2017-02-15 00:00:00.000 |
| TOM    | 2017-03-01 00:00:00.000 | 2017-04-01 00:00:00.000 |

案例 4：更新現有行

UPDATE DateRange SET Finish = '2017-03-02' WHERE UserId = 'TOM' and Start = '2016-12-15';

| UserId | Start                   | Finish                  |
|--------|-------------------------|-------------------------|
| JOE    | 2017-01-01 00:00:00.000 | 2017-02-01 00:00:00.000 |
| JOE    | 2017-03-01 00:00:00.000 | 2017-04-01 00:00:00.000 |
| TOM    | 2016-12-15 00:00:00.000 | 2017-04-01 00:00:00.000 |

案例 5：插入新範圍

INSERT INTO DateRange (UserId, Start, Finish) VALUES ('TOM', '2017-06-01', '2017-07-01');

| UserId | Start                   | Finish                  |
|--------|-------------------------|-------------------------|
| JOE    | 2017-01-01 00:00:00.000 | 2017-02-01 00:00:00.000 |
| JOE    | 2017-03-01 00:00:00.000 | 2017-04-01 00:00:00.000 |
| TOM    | 2016-12-15 00:00:00.000 | 2017-04-01 00:00:00.000 |
| TOM    | 2017-06-01 00:00:00.000 | 2017-07-01 00:00:00.000 |

案例 6：新範圍與新範圍部分重疊。

INSERT INTO DateRange (UserId, Start, Finish) VALUES ('TOM', '2017-04-01', '2017-05-01');

| UserId | Start                   | Finish                  |
|--------|-------------------------|-------------------------|
| JOE    | 2017-01-01 00:00:00.000 | 2017-02-01 00:00:00.000 |
| JOE    | 2017-03-01 00:00:00.000 | 2017-04-01 00:00:00.000 |
| TOM    | 2016-12-15 00:00:00.000 | 2017-05-01 00:00:00.000 |
| TOM    | 2017-06-01 00:00:00.000 | 2017-07-01 00:00:00.000 |

案例 7：新範圍與新範圍部分重疊。

INSERT INTO DateRange (UserId, Start, Finish) VALUES ('TOM', '2017-05-05', '2017-06-05');

| UserId | Start                   | Finish                  |
|--------|-------------------------|-------------------------|
| JOE    | 2017-01-01 00:00:00.000 | 2017-02-01 00:00:00.000 |
| JOE    | 2017-03-01 00:00:00.000 | 2017-04-01 00:00:00.000 |
| TOM    | 2016-12-15 00:00:00.000 | 2017-05-01 00:00:00.000 |
| TOM    | 2017-05-05 00:00:00.000 | 2017-07-01 00:00:00.000 |

案例 8：刪除現有行

delete from DateRange WHERE UserId = 'TOM' and Start = '2016-12-15';

| UserId | Start                   | Finish                  |
|--------|-------------------------|-------------------------|
| JOE    | 2017-01-01 00:00:00.000 | 2017-02-01 00:00:00.000 |
| JOE    | 2017-03-01 00:00:00.000 | 2017-04-01 00:00:00.000 |
| TOM    | 2017-05-05 00:00:00.000 | 2017-07-01 00:00:00.000 |

如果您可以在 C# 中以更簡單的方式獲得相同的結果，那麼您應該堅持下去。當然，這個網站上的“聰明人”可能有更好的解決方案。

由於對范圍類型的額外要求，我認為這對於 Scott Hodgin 的答案中的查詢類型來說太複雜了。

例如，如果將類型為 one 的範圍插入到另一種類型的範圍內，則不僅現有範圍的開頭需要修剪其結尾，而且需要在新範圍的另一側添加新範圍- 原來的範圍打了一個洞。因此，即使可以動態修剪範圍，您仍然需要跟踪需要添加的範圍。而且您需要一個循環來為每個打孔的範圍進行插入。

您可以將使用者的範圍劃分為類型，將它們連接起來，然後針對其他類型解決它們，但是隨後出現了一個新問題，即哪種類型的範圍優先於其他類型。如果一個類型的範圍已經連接在一起成為連續的塊，那麼關於範圍的一部分是否剛剛添加並因此具有優先級的資訊已經失去。

我認為有一種優雅的方法可以通過多次通過，在範圍集之間進行差異來做到這一點。但是一次處理一個範圍操作似乎更容易。

對於多種類型的範圍，我認為這個問題就像為單音合成器排序音符一樣。類型成為註釋。

此外，如果在應用程序而不是數據庫中執行 CRUD，則有一個強大的優化，即對於任何新的或更新的範圍，直接在數據庫中刪除它下面的所有內容。然後，您最多檢索兩個範圍進行編輯。包含新行的行或在每一端重疊的兩行。

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

相關問答

Sql-Server

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

  • October 7, 2021
檢視問答
Sql-Server

按 2 行結果排序導致 800% 的性能下降

  • January 15, 2018
檢視問答
Sql-Server

按不間斷日期對行進行分組

  • January 9, 2018
檢視問答
Sql-Server

如何用日曆表、計數和分組來填補空白

  • March 27, 2017
檢視問答
Sql-Server

如何將日期列表轉換為按條件分組的日期範圍列表

  • December 5, 2016
檢視問答
Sql-Server

計數會話 - TSQL

  • November 17, 2016
檢視問答