Início / dba / Perguntas / 308101
Accepted
RBS
Asked: 2022-03-02 00:20:40 +0800 CST

Dividindo o período de tempo em vários menores com duração irregular

  • 772

Temos um conjunto de dados contendo datas de eventos, outros dados de eventos e a data de início e término da 'temporada do evento' em que o evento ocorreu.

A questão é a seguinte: no postgresql, desejo dividir as temporadas de eventos em várias temporadas menores, encerrando uma nova temporada uma semana após a ocorrência de um evento. Uma nova temporada começará no dia seguinte, terminando novamente 7 dias após o primeiro evento desta nova temporada. Isso é muito fácil de fazer se houvesse um evento todos os dias, eu poderia simplesmente usar generate_series() e dividir cada temporada em pedaços de 7 dias.

No entanto, o problema é que não há um evento todos os dias, e desejo que uma nova temporada termine apenas 6 dias após o evento ter ocorrido.

Por exemplo, se houver um evento nos dias 1,2,16,18 e 20 da temporada antiga (dia 1-31), as novas temporadas terão as seguintes datas de início e término:

  • Parte 1: Dia 1 - Dia 7 (6 dias após o primeiro evento nesta nova temporada)
  • Parte 2: Dia 8 - Dia 16 (6 dias após o dia 10)
  • Parte 3: Dia 17 - Dia 24 (6 dias após o dia 18)
  • Parte 4: Dia 25 - Dia 31 (que não conteria eventos)

Eu tentei criar isso usando uma combinação de LAG, menor e maior, mas não consigo encontrar uma maneira que não resulte em pelo menos parte do script sendo iterativo.

Alguém pode, talvez, dar algumas dicas sobre a melhor forma de resolver esse problema?

Exemplo: Tabela de entrada:

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')
;

A saída esperada seria:

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

Se o evento do dia 10 não existisse, a saída esperada seria:

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 respostas

  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

relate perguntas