將時間段分成多個長度不規則的較小時間段

我們有一組數據，其中包含事件日期、其他事件數據以及事件發生的“事件季節”的開始和結束日期。

問題如下：在postgresql中，我希望將活動季節分成多個較小的季節，在事件發生一周後結束新的季節。新賽季將在第二天開始，在這個新賽季的第一場比賽后 7 天再次結束。如果每天都有一個事件，這很容易做到，我可以使用 generate_series() 並將每個季節分成 7 天的塊。

但是，問題在於不是每天都有活動，我希望新賽季在活動發生後僅 6 天結束。

例如，如果在舊賽季（第 1-31 天）的第 1、2、16、18 和 20 天發生事件，則新賽季將具有以下開始和結束日期：

• 第 1 部分：第 1 天 - 第 7 天（新賽季第一次活動後 6 天）
• 第 2 部分：第 8 天 - 第 16 天（10 日後 6 天）
• 第 3 部分：第 17 天 - 第 24 天（18 日後 6 天）
• 第 4 部分：第 25 天 - 第 31 天（不包含任何事件）

我嘗試使用 LAG、最小和最大的組合來創建它，但似乎找不到一種不會導致至少部分腳本被迭代的方法。

有人可以就如何最好地解決這個問題的方向給出一些指示嗎？

範例：輸入表：

CREATE TABLE events (
season_start  date
,season_end    date
,event_date    date
)

INSERT INTO events (season_start, season_end, event_date) VALUES 
('2022-01-01', '2022-01-31', '2022-01-01')
,('2022-01-01', '2022-01-31', '2022-01-02')
,('2022-01-01', '2022-01-31', '2022-01-10')
,('2022-01-01', '2022-01-31', '2022-01-16')
,('2022-01-01', '2022-01-31', '2022-01-18')
,('2022-01-01', '2022-01-31', '2022-01-20')
;

預期的輸出將是：

season_start | season_end | event_date
2022-01-01   | 2022-01-07 | 2022-01-01
2022-01-01   | 2022-01-07 | 2022-01-02
2022-01-08   | 2022-01-16 | 2022-01-10
2022-01-08   | 2022-01-16 | 2022-01-16
2022-01-17   | 2022-01-24 | 2022-01-18
2022-01-17   | 2022-01-24 | 2022-01-20

如果 10 日的事件不存在，那麼預期的輸出將是：

season_start | season_end | event_date
2022-01-01   | 2022-01-07 | 2022-01-01
2022-01-01   | 2022-01-07 | 2022-01-02
2022-01-08   | 2022-01-22 | 2022-01-16
2022-01-08   | 2022-01-22 | 2022-01-18
2022-01-08   | 2022-01-22 | 2022-01-20

WITH RECURSIVE
ranges AS (
   (
   SELECT id, 
          season_start, 
          season_end, 
          event_date,
          season_start range_start,
          (event_date + INTERVAL '6 day') :: DATE range_end
   FROM events
   ORDER BY event_date LIMIT 1
   )
   UNION ALL
   (
   SELECT events.id, 
          events.season_start, 
          events.season_end, 
          events.event_date,
          (ranges.range_end + INTERVAL '1 day') :: DATE,
          (events.event_date + INTERVAL '6 day') :: DATE
   FROM events
   JOIN ranges ON ranges.range_end < events.event_date
   ORDER BY event_date LIMIT 1
   )
)
SELECT events.id,
      events.season_start, 
      events.season_end,
      events.event_date,
      ranges.range_start,
      ranges.range_end
FROM events
JOIN ranges ON events.event_date BETWEEN range_start AND range_end;

https://dbfiddle.uk/?rdbms=postgres_12&fiddle=8108f98dc0370d28dba04a7df4dae1ba

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