從連續和非連續日期跨度創建日期跨度
我正在嘗試創建一個查詢，該查詢將根據“BEG_DT”和“END_DT”返回一系列日期。結果應顯示基於“BEG_DT”的系列日期，並且每個日期之間的跨度等於“PLAN_LENGTH”值。例如，“BEG_DT”為 2020-07-01，“END_DT”為 2100-06-30，“PLAN_LENGTH”為 12，因此它將返回日期“2020-07-01”和“2021-07-” 01’。它應該生成的最後一個日期是 2021-07-01，因為“2022-07-01”的下一個日期是未來 12 個月以上。
邏輯要求如下所示：
- 開始日期應等於 BEG_DT
- 每個日期跨度應等於相隔 PLAN_LENGTH 個月
- 不要超過 END_DT
- 不要返回從目前執行日期起超過 PLAN_LENGTH 個月的日期。
- 每個 GROUP_ID 和 PLAN_ID 都可以有自己的開始和結束日期。
這是我嘗試使用的邏輯。我無法讓它認識到不同的組和計劃組合可以有不同的開始月份和日期。
DECLARE @RUN_DATE DATETIME = CURRENT_TIMESTAMP Create table #DATE_SPANS ( GROUP_ID VARCHAR(8) , PLAN_ID VARCHAR(8) , BEG_DT DATE , END_DT DATE , PLAN_LENGTH INT ) Insert Into #DATE_SPANS (GROUP_ID, PLAN_ID, BEG_DT, END_DT, PLAN_LENGTH) values ('82551399','AMT00001','2020-01-01','2020-12-31','12') , ('82551399','AMT00002','2020-01-01','2100-12-31','12') , ('82551399','AMT00003','2020-01-01','2021-12-31','12') , ('75491773','AMT00004','2020-01-01','2021-06-30','18') , ('32198498','AMD00001','2020-10-01','2021-09-30','12') , ('32198498','AMD00001','2021-10-01','2022-09-30','12') , ('32198498','AMD00002','2020-10-01','2022-09-30','12') , ('32198498','AMD00003','2020-10-01','2100-09-30','12') declare @BEGIN_DT_1 DATETIME = (SELECT MIN(DISTINCT(BEG_DT)) FROM #DATE_SPANS) declare @END_DT_1 DATETIME = (SELECT MAX(DISTINCT(CASE WHEN END_DT = '9999-12-31' then @RUN_DATE ELSE END_DT END)) FROM #DATE_SPANS ) --SELECT @END_DT_1 ;with dates ([Date]) as ( Select convert(date, @BEGIN_DT_1) as [Date] -- Put the start date here union all Select dateadd(YEAR, 1, [Date]) from dates where [Date] <= @END_DT_1 -- Put the end date here ) select t.GROUP_ID , t.PLAN_ID , [Date] AS [DATE] INTO #TEMP_PLAN_YEARS from dates d join #DATE_SPANS t on d.Date >= t.BEG_DT and d.Date <= DATEADD(MM,0,t.END_DT) option (maxrecursion 32767) select distinct GROUP_ID, PLAN_ID, [DATE] into #TEMP_PLAN_YEARS_FINAL from #TEMP_PLAN_YEARS where [DATE] >= '2021-01-01' order by [DATE], PLAN_ID select * from #TEMP_PLAN_YEARS_FINAL where DATE <= DATEADD(YEAR, 1, GETDATE())
- 測試數據 - - - -
CREATE TABLE #DATE_SPANS ( GROUP_ID VARCHAR(8) , PLAN_ID VARCHAR(8) , BEG_DT DATE , END_DT DATE , PLAN_LENGTH INT ) INSERT INTO #DATE_SPANS (GROUP_ID, PLAN_ID, BEG_DT, END_DT, PLAN_LENGTH)組 82551399 有 3 個不同的計劃 ID，它們在不同的時間段內執行。每個計劃都基於 12 個月的計劃年度。AMT00001 顯示了它的一年。AMT00002 將分為 2020、2021 和 2022 三個計劃年。
注意：系統日期可以發佈到 2100 年及以後。它們通常顯示為 9999-12-31。AMT00003 將分為 2020 年和 2021 年兩年。在 12 個月的日曆年中。組 32198498 與組 82551399 相同，但從 10-01 開始生效。
以下結果的執行日期為 2021 年 6 月 7 日
VALUES ('82551399','AMT00001','2020-01-01','2020-12-31','12') , ('82551399','AMT00002','2020-01-01','2100-12-31','12') , ('82551399','AMT00003','2020-01-01','2021-12-31','12') , ('75491773','AMT00004','2020-01-01','2021-06-31','18') , ('32198498','AMD00001','2020-10-01','2021-09-30','12') , ('32198498','AMD00001','2021-10-01','2022-09-30','12') , ('32198498','AMD00002','2020-10-01','2022-09-30','12') , ('32198498','AMD00003','2020-10-01','2100-09-30','12') /* > EXPECTED RESULT > --Group ID 8251399 > 82551399, AMT00001, 2020-01-01 > > 82551399, AMT00002, 2020-01-01 > 82551399, AMT00002, 2021-01-01 > 82551399, AMT00002, 2022-01-01 > > 82551399, AMT00003, 2020-01-01 > 82551399, AMT00003, 2021-01-01 > > --Group ID 75491773 > > 75491773, AMT00004, 2020-01-01 > > --Group ID 32198498 > 32198498, AMD00001, 2020-10-01 > 32198498, AMD00001, 2021-10-01 > > 32198498, AMD00002, 2020-10-01 > 32198498, AMD00002, 2021-10-01 > 32198498, AMD00003, 2020-10-01 > 32198498, AMD00003, 2021-10-01 > */
我解決這個問題的方法可以總結如下：
- 對於每個日期範圍，獲取間隔
PLAN_LENGTH內相隔數月的所有日期的列表，從 開始BEG_DT。- 排除等於或晚於今天加上
PLAN_LENGTH月份的所有日期。如果這是 PostgreSQL，那麼邏輯可以相當簡潔地表達如下：
SELECT ds.* , x.Date FROM date_spans AS ds , generate_series(ds.BEG_DT, ds.END_DT, (ds.PLAN_LENGTH || ' months')::interval) AS x (Date) WHERE x.Date < CURRENT_TIMESTAMP::date + ds.PLAN_LENGTH * INTERVAL '1 month' ORDER BY ds.group_id ASC , ds.plan_id ASC , x.Date ASC ;唉，Transact-SQL 沒有
generate_series或沒有等價物，所以我只好沒有它。不過，我仍然使用上述查詢作為通用指針。因此，以下 Transact-SQL 解決方案包含使用其他方法進行模擬的嘗試generate_series，即數字表（或者更確切地說是其簡單的 CTE 實現）和一點日期算術：WITH Numbers (N) AS ( SELECT hundreds.N * 100 + tens.N * 10 + ones.N * 1 FROM (VALUES (0), (1), (2), (3), (4), (5), (6), (7), (8), (9)) AS ones (N) , (VALUES (0), (1), (2), (3), (4), (5), (6), (7), (8), (9)) AS tens (N) , (VALUES (0), (1), (2), (3), (4), (5), (6), (7), (8), (9)) AS hundreds (N) ) SELECT ds.* , x.Date FROM dbo.date_spans AS ds -- this join multiplies each range as many times as there are -- PLAN_LENGTH-month intervals within the range INNER JOIN Numbers AS n ON n.N BETWEEN 0 AND DATEDIFF(MONTH, ds.BEG_DT, ds.END_DT) / ds.PLAN_LENGTH -- this generates an actual date CROSS APPLY ( SELECT DATEADD(YEAR, n.N, ds.BEG_DT) ) AS x (Date) WHERE -- this additionally caps the date list at a point that is today + PLAN_LENGTH months x.Date < DATEADD(MONTH, ds.PLAN_LENGTH, CAST(CURRENT_TIMESTAMP AS date)) ORDER BY ds.group_id ASC , ds.plan_id ASC , n.N ASC ;數據集
Numbers本身可以被視為generate_series（非負）整數的替代品。與CROSS APPLY它一起構成了該函式的時間戳相關版本的替代品。對於問題中提供的測試設置，上述腳本返回此輸出：
該解決方案的實時版本可在 db<>fiddle獲得。
我的方法與@Andriy 的方法非常相似——怎麼可能呢？如果有人有明顯不同的東西，我會全神貫注！下面的所有程式碼都可以在這裡找到。
我根據問題創建了表格，但是，我添加了一些適當的（我認為）約束 - 我們告訴優化器的越多，它產生的計劃就越好！我意識到這是一個測試場景，所以也許這些不適用於現實世界！
CREATE TABLE date_span ( group_id VARCHAR(8) NOT NULL, plan_id VARCHAR(8) NOT NULL, beg_dt DATE NOT NULL, end_dt DATE NOT NULL, plan_length INTEGER NOT NULL, CONSTRAINT ds_pk PRIMARY KEY (group_id, plan_id, beg_dt), CONSTRAINT bd_lt_ed_ck CHECK (beg_dt < end_dt), );但是當涉及
INSERT到記錄時，我稍微改變了一些事情。最初，數據如下：VALUES ('82551399','AMT00001','2020-01-01','2020-12-31','12') , ('82551399','AMT00002','2020-01-01','2100-12-31','12') , ('82551399','AMT00003','2020-01-01','2021-12-31','12') , ('75491773','AMT00004','2020-01-01','2021-06-31','18') , ('32198498','AMD00001','2020-10-01','2021-09-30','12') , ('32198498','AMD00001','2021-10-01','2022-09-30','12') -- out of order , ('32198498','AMD00002','2020-10-01','2022-09-30','12') " , ('32198498','AMD00003','2020-10-01','2100-09-30','12') "現在，人類大腦在發現模式和發現這些模式之間的邏輯聯繫方面非常出色——它不太擅長的是從“混亂”的事實中提取資訊。
因此，我按如下方式載入了這些記錄：
('32198498','AMD00001','2020-10-01','2022-09-30', '12'), ('32198498','AMD00002','2020-10-01','2022-09-30', '12'), ('32198498','AMD00003','2020-10-01','2100-09-30', '12'), ('75491773','AMT00004','2020-01-01','2021-06-30', '18'), ('82551399','AMT00001','2020-01-01','2020-12-31', '12'), ('82551399','AMT00002','2020-01-01','2100-12-31', '12'), ('82551399','AMT00003','2020-01-01','2021-12-31', '12');我在記錄之間劃了一條線——讓這個人更容易看到發生了什麼。另請注意，在原始集合中，兩條記錄分別
group_id為 32198498 和- 我將它們合併為一條記錄plan_id。AMD00001你可以這樣做：SELECT group_id, plan_id, MIN(beg_dt), MAX(end_dt), plan_length FROM date_spans_x GROUP BY group_id, plan_id, plan_length;在這個測試案例中，我是手動完成的。該解決方案適用於兩組數據（未顯示）。
我已經完成了我用來找到解決方案的邏輯步驟 - 希望它會幫助你 - 我知道在我完成一個問題之後，我發現坐下來向自己解釋也對我有幫助。 .
這是一個需要
Common Table Expression（CTE），特別是RECURSIVECTE（RCTE）的問題。第一步 - 對問題開槍，希望有什麼能解決的！
-- -- non-discriminating RCTE - limited by n - arbitrary! -- WITH tab (n, gid, pid, st, t_p1_et, t_p1_st, et, pl) AS ( SELECT 1, ds.group_id, ds.plan_id, ds.beg_dt, -- beginning of period for plan 1 - for a given group_id & plan_id DATEADD(DAY, -1, DATEADD(MONTH, ds.plan_length, ds.beg_dt)), -- end of period for plan 1 DATEADD(MONTH, ds.plan_length, ds.beg_dt), -- beginning of period for plan 1 + 1 ds.end_dt, ds.plan_length FROM date_span ds UNION ALL SELECT n + 1, gid, pid, t_p1_st, -- start of next period, DATEADD(DAY, -1, DATEADD(MONTH, tab.pl, tab.t_p1_st)), -- end of next period DATEADD(MONTH, tab.pl, tab.t_p1_st), -- beginning of plan, period + 2 et, pl FROM tab WHERE n < 4 ) SELECT * FROM tab ORDER BY gid, pid, n, st, et OPTION (MAXRECURSION 150);結果：
n gid pid st t_p1_et t_p1_st et pl 1 32198498 AMD00001 2020-10-01 2021-09-30 2021-10-01 2022-09-30 12 2 32198498 AMD00001 2021-10-01 2022-09-30 2022-10-01 2022-09-30 12 3 32198498 AMD00001 2022-10-01 2023-09-30 2023-10-01 2022-09-30 12 4 32198498 AMD00001 2023-10-01 2024-09-30 2024-10-01 2022-09-30 12 1 32198498 AMD00002 2020-10-01 2021-09-30 2021-10-01 2022-09-30 12 2 32198498 AMD00002 2021-10-01 2022-09-30 2022-10-01 2022-09-30 12 3 32198498 AMD00002 2022-10-01 2023-09-30 2023-10-01 2022-09-30 12 4 32198498 AMD00002 2023-10-01 2024-09-30 2024-10-01 2022-09-30 12 1 32198498 AMD00003 2020-10-01 2021-09-30 2021-10-01 2100-09-30 12 ... ... Snipped for brevity ... 28 rows因此，CTE 沒有任何選擇性 - 只是
n< 4 - 所以，我們獲得 7 x 4 = 28 條記錄。我們將快速瀏覽第一組記錄（按 group_id/plan_id）：n gid pid st t_p1_et t_p1_st et pl 1 32198498 AMD00001 2020-10-01 2021-09-30 2021-10-01 2022-09-30 12 2 32198498 AMD00001 2021-10-01 2022-09-30 2022-10-01 2022-09-30 12 3 32198498 AMD00001 2022-10-01 2023-09-30 2023-10-01 2022-09-30 12 4 32198498 AMD00001 2023-10-01 2024-09-30 2024-10-01 2022-09-30 12第一條記錄
st是開始時間（beg_dt從表中）是2020-10-01。外部（第四個）日期欄位是et（表中的 end_dt）。後一個不會在組內更改 (group_id, plan_id)。第二個日期欄位（
t_p1_et-期間 1，結束時間）是2020-10-01+ 12 個月 =2021-09-30這是正確的，第三個日期欄位（t_np_st-下一個期間，開始時間）是2021-10-01。所以，現在我們繼續進行該組的第二條記錄。它
st現在是t_np_st上一個（第一個）記錄的 -2021-10-01然後它的結束時間是通過將plan_length月數添加到 來計算的st，並且下一個時期的開始也是通過在 中添加一年來得出的st。等等！RCTE 的驚人力量變得顯而易見。
關於這一點和一般的 CTE，有幾點需要注意。
n是用於停止 CTE 的“虛擬” - 如果沒有停止條件，CTE 將進入無限循環（while如果沒有終止條件，其他語言也會發生）。如果您使用 dbfiddle，您將（最終）收到一條Run failed錯誤消息。在評估不同的條件時，它
n也變得非常有用。WHERE
OPTION (MAXRECURSION 150);做與上面相同的事情n——它是特定於 SQL Server 的，以確保查詢不會執行異常——這裡不是絕對必要的——但是n在試驗時刪除查詢的一部分太容易了……YMMV。- 可以**
in theory**通過一個（可怕的）查詢將date_span查詢連接回自身 4 次並獲取 ds1.plan 欄位並UNION使用 ds2.plan_2 欄位執行 a 並UNION使用 ds3 執行 a 來做到這一點…日期計算也會變得非常複雜，加上嵌套 4 層的月份和年份……我把它留給讀者作為練習。如果你想要一個結果，比如說，10 年後會發生什麼？
經過大量的擺弄、試驗、哀號和
gnashing of teeth（向聖經的作者道歉……）以及來自@Andriy 的一些提示，我想出了這個 - 對於 RCTE 中的WHERE條款以及該 RCTE 中的後續條款SELECT：-- -- RCTE with meaningful WHERE clause -- ... ... Recursive CTE the same to here - snipped for brevity ... FROM tab WHERE tab.t_p1_et < tab.et AND tab.t_p1_et < DATEADD(MONTH, tab.pl, GETDATE()) ) SELECT n, t.gid, t.pid, t.st, t.t_p1_et, t.pl FROM tab t ORDER BY t.gid, t.pid, t.st -- OPTION (MAXRECURSION 150); -- can leave just in case of data error...結果：
n gid pid st t_p1_et pl 1 32198498 AMD00001 2020-10-01 2021-09-30 12 2 32198498 AMD00001 2021-10-01 2022-09-30 12 1 32198498 AMD00002 2020-10-01 2021-09-30 12 2 32198498 AMD00002 2021-10-01 2022-09-30 12 1 32198498 AMD00003 2020-10-01 2021-09-30 12 2 32198498 AMD00003 2021-10-01 2022-09-30 12 1 75491773 AMT00004 2020-01-01 2021-06-30 18 1 82551399 AMT00001 2020-01-01 2020-12-31 12 1 82551399 AMT00002 2020-01-01 2020-12-31 12 2 82551399 AMT00002 2021-01-01 2021-12-31 12 3 82551399 AMT00002 2022-01-01 2022-12-31 12 1 82551399 AMT00003 2020-01-01 2020-12-31 12 2 82551399 AMT00003 2021-01-01 2021-12-31 12 13 rows這裡的癥結是
WHERE子句：WHERE tab.t_p1_et < tab.et AND tab.t_p1_et < DATEADD(MONTH, tab.pl, GETDATE())因此，第一位
tab.t_p1_et < tab.et表示計劃期間的結束時間不能超過原始表中指定的結束時間。當 CTE 將被子句的第二部分終止時，這可以是2100-09-30（不會是終止條件）或更接近的東西。WHERE第二位是，如果週期的結束時間不超過查詢執行的日期 (
GETDATE())加上plan_length 中的月數。花了相當多的時間和討論來闡明這一點。如果您想永久保留這些記錄，您可能需要使用一個表（我稱之為它
tranche），如下所示：CREATE TABLE tranche ( t_id INTEGER IDENTITY (1, 1), t_gid VARCHAR(8) NOT NULL, t_pid VARCHAR(8) NOT NULL, t_sdt DATE NOT NULL, t_edt AS (DATEADD(MONTH, t_plen, t_sdt)), t_plen INTEGER NOT NULL, CONSTRAINT n_g_p_uq UNIQUE (t_gid, t_pid, t_sdt), -- you can put in various -- other constraints );請注意，該
t_edt欄位是根據月數計算t_sdt的t_plen- 您可能會也可能不會覺得這很有用。性能：
最後，我在小提琴中轉向
on statistics如下：SET STATISTICS PROFILE, TIME, IO ON;
- 您應該始終根據實際數據集對任何 SQL 進行基準測試。來自伺服器的任何結果 a）您不知道機器的其餘部分發生了什麼，並且 b）您只處理 7 條記錄，應該用一大撮鹽來處理！
我將我的解決方案與@Andriy 的解決方案進行了比較，並顯示了結果——我並不是真正的 SQL Server 人員，但我會看看是否可以探勘出與這兩次執行相關的任何內容。
最後，像您這樣的問題在這裡經常出現，並且由於 RCTE，現在可以合理地處理。他們中的許多人使用日曆表 - 正如@Andriy 指出的那樣，PostgreSQL 具有非常強大的
GENERATE_SERIES功能 -這是一個 SQL Server 答案，我使用各種方法來解決類似的問題。這裡有幾個（1和2）使用 PostgreSQL 的答案 - 但如果您有興趣了解更多資訊，您可以使用 SQL Server 遞歸 CTE 而不是 GENERATE_SERIES。其他海報的做法也值得一看！