主页 / dba / 问题 / 308101
Accepted
RBS
Asked: 2022-03-02 00:20:40 +0800 CST

将时间段分成多个长度不规则的较小时间段

  • 772

我们有一组数据，其中包含事件日期、其他事件数据以及事件发生的“事件季节”的开始和结束日期。

问题如下：在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
postgresql date

1 个回答

  1. Best Answer
    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

    • 2

相关问题