Совокупные непрерывные диапазоны дат

Допустим, у вас есть следующая разреженная таблица PostgreSQL со списком дат резервирования:

CREATE TABLE reserved_dates (
    reserved_days_id    SERIAL  NOT NULL,
    reserved_date       DATE    NOT NULL
);

INSERT INTO reserved_dates (reserved_date) VALUES
    ('2014-10-11'),
    ('2014-10-12'),
    ('2014-10-13'),
    -- gap
    ('2014-10-15'),
    ('2014-10-16'),
    -- gap
    ('2014-10-18'),
    -- gap
    ('2014-10-20'),
    ('2014-10-21');

Как объединить эти даты в непрерывные диапазоны дат (диапазоны без пробелов)? Такие как:

 start_date | end_date
------------+------------
 2014-10-11 | 2014-10-13
 2014-10-15 | 2014-10-16
 2014-10-18 | 2014-10-18
 2014-10-20 | 2014-10-21

Это то, что я придумал до сих пор, но я могу получить только start_date таким образом:

WITH reserved_date_ranges AS (
    SELECT reserved_date,
           reserved_date
           - LAG(reserved_date) OVER (ORDER BY reserved_date) AS difference
    FROM reserved_dates
)
SELECT *
FROM reserved_date_ranges
WHERE difference > 1 OR difference IS NULL;

postgresql sql window-functions aggregate-functions time-series
person Linas    schedule 20.10.2014    source источник
comment
Ключевое слово calendar table , в вашем случае:: generate_series() оставило соединение с YourTable, в основном.   -  person wildplasser    schedule 21.10.2014
comment
Хорошо, я бы восполнил пробелы промежуточными датами таким образом. Как мне получить диапазоны дат из этого?   -  person Linas    schedule 21.10.2014

Ответы (1)


arrow_upward
8
arrow_downward 

SELECT min(reserved_date) AS start_date
     , max(reserved_date) AS end_date
FROM  (
   SELECT reserved_date
        , reserved_date - row_number() OVER (ORDER BY reserved_date)::int AS grp
   FROM   reserved_dates
   ) sub 
GROUP  BY grp
ORDER  BY grp;

  1. Вычислите непрерывное число без пробелов в хронологическом порядке с помощью оконной функции row_number(), если только ваше reserved_days_id не окажется без пробелов и в хронологическом порядке, что обычно не имеет место.

  2. Вычтите это из reserved_date в каждой строке (после преобразования в integer). Последовательные дни заканчиваются одним и тем же значением даты grp, которое не имеет другой цели или значения, кроме формирования групп.

  3. Агрегировать во внешнем запросе. Вуаля.

SQL Fiddle.

Похожие случаи:

person Erwin Brandstetter    schedule 21.10.2014
comment
Этот А кажется трудно применимым в общем случае для временных рядов с заданным разрешением, например. 2 дня или каждые 10 минут. Временные метки должны быть приведены к реальным значениям, а операции с плавающей запятой неточны. Есть ли альтернативы? - person mlt; 09.03.2018
comment
Беру свои слова обратно. Это решение работает со столбцами меток времени, подобными extract(epoch from timestamp) - extract(epoch from interval '1day')*row_number() over (order by timestamp) - person mlt; 10.03.2018
comment
После всех этих лет я понял, что он не защищен от дубликатов db-fiddle.com/f /31UdD6udUZqwQk43pRbGKZ/1 - person mlt; 20.06.2020
comment
Используйте dense_rank вместо row_number, если у вас есть дубликаты. - person mlt; 21.06.2020