Postgresql - минимальный/максимальный диапазон дат в группе

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

Пример:

  Product | Status   | Start Date | End Date
----------+------- --+------------+-----------
widget a  | active   | 02/01/2020 | 02/30/2020
widget a  | active   | 03/01/2020 | 03/19/2020
widget a  | inactive | 03/20/2020 | 05/01/2020
widget a  | active   | 05/02/2020 | 08/31/2020
widget b  | active   | 02/01/2020 | 05/31/2020
widget b  | inactive | 06/01/2020 | 06/31/2020

Я пытаюсь свернуть эти данные на основе минимальной и максимальной дат по мере изменения статуса (как я уже сказал, другие атрибуты вносят свой вклад в изменение записи, но меня интересуют только изменения статуса). Таким образом, в приведенном выше примере «виджет a» будет иметь три записи: активен с 01.02.2020 по 19.03.2020, неактивен с 20.03.2020 по 01.05.2020 и активен с 02.05.2020 — 31.08.2020. Это можно легко сделать с помощью инструмента ETL, но я хотел бы представить это в виде.

Каков наилучший способ сделать это, помня о производительности

Это постгрескл 10


postgresql sql datetime window-functions gaps-and-islands
person TXAggie00    schedule 14.10.2020    source источник

Ответы (1)


arrow_upward
1
arrow_downward

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

Вот подход, который использует разницу между номерами строк для построения групп:

select product, status, min(start_date) start_date, max(end_date) end_date
from (
    select t.*, 
        row_number() over(partition by product order by start_date) rn1,
        row_number() over(partition by product, status order by start_date) rn2
    from mytable t
) t
group by product, rn1 - rn2
person GMB    schedule 14.10.2020
comment
Потрясающе, спасибо! Я раньше не слышал термина «пробелы и острова», и хотя ваш пример не обязательно сработал для меня, он предоставил то, что мне нужно для решения проблемы. - person TXAggie00; 16.10.2020