我也找不到纯粹基于窗口函数的解决方案。
但是在PostgreSql中，递归CTE可以做到这一点。
临时表用于具有可用于连接到下一条记录的ID。
CREATE TEMPORARY TABLE tempEventDates ( id SERIAL primary key, account_id int not null, day date not null, min_day date not null, event_id int not null);INSERT INTO tempEventDates (account_id, day, min_day, event_id)SELECt account_id, day,MIN(day) OVER (PARTITION BY account_id) as min_day, event_idFROM yourtableGROUP BY account_id, day, event_idORDER BY account_id, day, event_id;WITH RECURSIVE RCTE AS( SELECt id, account_id, event_id, day, min_day FROM tempEventDates WHERe day = min_day UNIOn ALL SELECt t.id, t.account_id, t.event_id, t.day, CASE WHEN t.day > c.min_day + interval '30 days' THEN t.day ELSE c.min_day END FROM RCTE c JOIN tempEventDates t ON t.account_id = c.account_id AND t.id = c.id + 1)SELECt account_id, day, event_idFROM RCTEWHERe day = min_dayORDER BY account_id, day;
