Блог

Я использую Postgres и пытаюсь разобраться, как именно я мог бы получить первую начальную дату в непрерывных интервалах дат. Например :-

 ID | Start Date | End Date
==========================
1|2020-01-01|2020-01-31
1|2020-02-01|2020-03-31
1|2020-05-01|2020-06-30
1|2020-07-01|2020-07-31
1|2020-08-01|2020-08-31
  

Результат, который я ожидаю, это

 ID | Start Date | End Date | Continous Date
===========================================
1|2020-01-01|2020-01-31|2020-01-01
1|2020-02-01|2020-03-31|2020-01-01
1|2020-05-01|2020-06-30|2020-05-01
1|2020-07-01|2020-07-31|2020-05-01
1|2020-08-01|2020-08-31|2020-05-01
  

По сути, это должно дать мне самую первую начальную дату непрерывного диапазона дат.

Ценю ваши входные данные или указания о том, как я мог бы это сделать. К сожалению, CTE — это то, с чем я, возможно, не смогу согласиться.

Это проблема пробелов и островов, когда острова определяются как группа последовательных записей со смежными датами. Вам нужно начало каждого острова.

Вот подход, который используется lag() для извлечения «предыдущей» конечной даты, а затем создает группу с кумулятивным sum() значением, которое увеличивается при каждом разрыве.

 select t.*, 
    min(start_date) over(partition by id, grp order by start_date) continous_date
from (
    select t.*, 
        count(*) filter(where start_date is distinct from lag_end_date   interval '1 day') over(partiton by id order by start_date) grp
    from (
        select t.*, 
            lag(end_date) over(partition by id order by start_date) lag_end_date
        from mytable t
    ) t
) t
  

Это проблема пробелов и островов. В принципе, определите, где есть перекрытие, используя lag() или какую-либо другую функцию. Затем «острова» идентифицируются совокупной суммой неперекрывающихся. Я бы подошел к этому как:

 select t.*,
       min(start_date) over (partition by id, grp) as continuous_start_date
from (select t.*,
             count(*) filter (where prev_end_date is null or prev_end_date < start_date - interval '1 day') as grp
      from (select t.*,
                   max(end_date) over (partition by id order by start_date rows between unbounded preceding and 1 preceding) as prev_end_date
            from t
           ) t
     ) t;
  

Обратите внимание, что это обрабатывает перекрытия между соседними записями, а также начинается накануне.