Postgresql根據日期將單行拆分為多行

在我的例子中，我們有一個表 ABC，其中應該有 1 行。

但是在檢索時，我想獲得多行，其中開始日期遞增 1，直到它小於或等於結束日期。

請讓我知道我們該怎麼做。

你可以通過幾種方式來做到這一點（下面的所有程式碼都可以在 fiddle here上找到）。

使用 GENERATE_SERIES 的解決方案：

對於初學者，我插入了更多記錄以使案例更加真實，我還在表中添加了一些合理的約束。

CREATE TABLE test
(
 id SMALLINT NOT NULL PRIMARY KEY,  -- PK added
 name TEXT NOT NULL,
 start_date DATE NOT NULL,
 end_date DATE NOT NULL,
 
 CONSTRAINT test_name_uq UNIQUE (name),   -- maybe, maybe not?
 CONSTRAINT sd_lt_ed_ck CHECK (start_date < end_date) -- important!
);

填充它：

INSERT INTO test
VALUES
(1, 'Rahul', '2021-06-01', '2021-06-06'),
(2, 'Bill' , '2021-06-02', '2021-06-10'),
(3, 'Mary' , '2021-07-15', '2021-07-22'),
(4, 'Fred' , '2021-07-20', '2021-07-27'),
(5, 'Joe'  , '2021-08-01', '2021-08-04'),
(6, 'Jim ' , '2021-09-04', '2021-09-05'),
(7, 'John' , '2021-09-17', '2021-09-21');

然後我執行了以下查詢：

SELECT 
 id, 
 GENERATE_SERIES 
   (t.start_date, t.end_date, '1 DAY')::DATE AS sd, 
 t.end_date
FROM test t
ORDER BY t.id, t.start_date;

結果（為簡潔起見 - 見小提琴）：

id  sd  end_date
1   2021-06-01  2021-06-06
1   2021-06-02  2021-06-06
1   2021-06-03  2021-06-06
1   2021-06-04  2021-06-06
1   2021-06-05  2021-06-06
1   2021-06-06  2021-06-06
2   2021-06-02  2021-06-10
2   2021-06-03  2021-06-10
...
...  more records
...

僅連續兩天的“邊緣案例”也很有效：

6   2021-09-04  2021-09-05
6   2021-09-05  2021-09-05

我對我的解決方案與@Akina 提供的另一個解決方案進行了性能分析，雖然我的解決方案似乎有更多的操作，但它始終比他的更快（通常約 2/3 的時間 - 雖然偶爾，我的速度較慢） . 看這裡的小提琴。

現在，當我們只查看我們無法控制的伺服器上的 7 條記錄時，不可能正確地對解決方案進行基準測試 - 機器上的其他地方發生了什麼？我會敦促您使用您自己的（測試）系統測試任何選擇的解決方案，以澄清這一點，讓您自己滿意。

使用遞歸 CTE (RCTE) 的解決方案：

另一個有趣的解決方案也存在使用RCTE如下：

WITH RECURSIVE cte (n, id, sd, ed) AS
(
 SELECT 1, t.id, t.start_date, t.end_date
 FROM test t
 UNION ALL
 SELECT n+1, c.id, (c.sd + INTERVAL '1 DAY')::DATE, c.ed
 FROM cte c
 WHERE c.sd < (SELECT z.end_date FROM test z WHERE z.id = c.id)
)
SELECT * FROM cte c2
ORDER BY c2.id, c2.sd, c2.ed;

結果：

Same as for the query above - including the two consecutive days

性能分析：

我在fiddleEXPLAIN (ANALYZE, BUFFERS)中包含了兩個查詢的輸出。如您所見，GENERATE_SERIES 查詢佔用了 RCTE 大約 50% 的時間。那麼，您可能會問，為什麼有人會為 RCTE 煩惱呢？

好吧，它們非常強大，它們允許程序員向他們的查詢添加複雜的邏輯——我敦促你去探索它們——也許不適合這種情況，但對於未來的場景來說，它們值得牢記。

ps 歡迎來到 dba.se。為了將來參考，請不要在此處和StackOverflow上發布相同的問題。

數據庫問題在這裡非常重要。如果您提出了問題並且在合理的時間內沒有收到回复，請隨時在其他地方提問，但是，在這種情況下，請將舊問題的連結添加到新問題以避免重複工作！

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