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