Рекурсивный SQL-запрос с диапазонами Postgres для определения доступности

Я следил за этим сообщением в блоге: https://info.crunchydata.com/blog/range-types-recursion-how-to-search-availability-with-postgresql

CREATE TABLE travels (
    id serial PRIMARY KEY,
    travel_dates daterange NOT NULL,
    EXCLUDE USING spgist (travel_dates WITH &&)
);

и обнаружил, что эта функция не работает, когда я вставляю строки с длительностью подряд

CREATE OR REPLACE FUNCTION travels_get_available_dates(daterange)
RETURNS TABLE(available_dates daterange)
AS $$
    WITH RECURSIVE calendar AS (
        SELECT
            $1 AS left,
             $1 AS center,
             $1 AS right
        UNION
        SELECT
            CASE travels.travel_dates && calendar.left
                WHEN TRUE THEN daterange(lower(calendar.left), lower(travels.travel_dates * calendar.left))
                ELSE daterange(lower(calendar.right), lower(travels.travel_dates * calendar.right))
            END AS left,
            CASE travels.travel_dates && calendar.left
                WHEN TRUE THEN travels.travel_dates * calendar.left
                ELSE travels.travel_dates * calendar.right
            END AS center,
            CASE travels.travel_dates && calendar.right
                WHEN TRUE THEN daterange(upper(travels.travel_dates * calendar.right), upper(calendar.right))
                ELSE daterange(upper(travels.travel_dates * calendar.left), upper(calendar.left))
            END AS right
        FROM calendar
        JOIN travels ON
            travels.travel_dates && $1 AND
            travels.travel_dates <> calendar.center AND (
                travels.travel_dates && calendar.left OR
                travels.travel_dates && calendar.right
            )
)
SELECT *
FROM (
    SELECT
        a.left AS available_dates
    FROM calendar a
    LEFT OUTER JOIN calendar b ON
        a.left <> b.left AND
        a.left @> b.left
    GROUP BY a.left
    HAVING NOT bool_or(COALESCE(a.left @> b.left, FALSE))
    UNION
    SELECT
        a.right AS available_dates
    FROM calendar a
    LEFT OUTER JOIN calendar b ON
        a.right <> b.right AND
        a.right @> b.right
    GROUP BY a.right
    HAVING NOT bool_or(COALESCE(a.right @> b.right, FALSE))
) a
$$ LANGUAGE SQL STABLE;


INSERT INTO travels (travel_dates)
VALUES
    (daterange('2018-03-02', '2018-03-02', '[]')),
    (daterange('2018-03-06', '2018-03-09', '[]')),
    (daterange('2018-03-11', '2018-03-12', '[]')),
    (daterange('2018-03-16', '2018-03-17', '[]')),
    (daterange('2018-03-25', '2018-03-27', '[]'));

На данный момент это работает, как ожидалось.

SELECT *
FROM travels_get_available_dates(daterange('2018-03-01', '2018-04-01'))
ORDER BY available_dates;

available_dates
-------------------------
[2018-03-01,2018-03-02)
[2018-03-03,2018-03-06)
[2018-03-10,2018-03-11)
[2018-03-13,2018-03-16)
[2018-03-18,2018-03-25)
[2018-03-28,2018-04-01)

Но когда эта строка добавляется:

INSERT INTO travels (travel_dates)
VALUES
(daterange('2018-03-03', '2018-03-05', '[]'));

И повторно запустить

SELECT *
FROM travels_get_available_dates(daterange('2018-03-01', '2018-04-01'))
ORDER BY available_dates;

я получил

available_dates
-------------------------
empty
Источник

4 ответа

Я добавил комментарий к исходному сообщению в блоге о том, где, по моему мнению, возникает ошибка, то есть в способе обработки пустых диапазонов.

Когда диапазоны дат идут подряд или, скорее, смежны, это приводит к "пустым" диапазонам в любом или даже в обоих столбцах "левый" и "правый". Теперь, после завершения рекурсивного CTE (и предположим, что пустые диапазоны находятся в "левом" столбце), в предложении "LEFT OUTER JOIN ... ON ..." свободная и действительная travel_date будет соединена с ' empty'диапазон от B.left range, поскольку A.left <> 'empty' && A.left @> 'empty', поскольку все диапазоны тривиально содержат пустой диапазон. В идеале он должен быть в паре с NULL, поскольку это левое внешнее соединение, чтобы его можно было включить в окончательный набор результатов, но "пустой" вроде как мешал. Затем "empty" снова появляется в предложении "GROUP BY ... HAVING ...", где a.left @> 'empty' оценивается как истинное и отменяется, поэтому все действительные даты путешествия отбрасываются, что приводит к пустой таблице. Мое решение заключается в следующем: пустые поля должны быть равны NULL и отбрасываться от любого диапазона дат, который находится в центре:

CREATE OR REPLACE FUNCTION travels_get_available_dates(daterange)
RETURNS TABLE(available_dates daterange)
AS $$
    WITH RECURSIVE calendar AS (
        SELECT
            $1 AS left,
             $1 AS center,
             $1 AS right
        UNION
        SELECT
            CASE travels.travel_dates && calendar.left
                WHEN TRUE THEN daterange(lower(calendar.left), lower(travels.travel_dates * calendar.left))
                ELSE daterange(lower(calendar.right), lower(travels.travel_dates * calendar.right))
            END AS left,
            CASE travels.travel_dates && calendar.left
                WHEN TRUE THEN travels.travel_dates * calendar.left
                ELSE travels.travel_dates * calendar.right
            END AS center,
            CASE travels.travel_dates && calendar.right
                WHEN TRUE THEN daterange(upper(travels.travel_dates * calendar.right), upper(calendar.right))
                ELSE daterange(upper(travels.travel_dates * calendar.left), upper(calendar.left))
            END AS right
        FROM calendar
        JOIN travels ON
            travels.travel_dates && $1 AND
            travels.travel_dates <> calendar.center AND (
                travels.travel_dates && calendar.left OR
                travels.travel_dates && calendar.right
            )
)
SELECT *
FROM (
    SELECT
        a.left AS available_dates
    FROM calendar a
    LEFT OUTER JOIN calendar b ON
        a.left <> b.left AND
        a.left @> b.left
    GROUP BY a.left
    HAVING NOT bool_or(coalesce(a.left @> case when isempty(b.left) then null else b.left end, FALSE))

    UNION

    SELECT
        a.right AS available_dates
    FROM calendar a
    LEFT OUTER JOIN calendar b ON
        a.right <> b.right AND
        a.right @> b.right
    GROUP BY a.right
    HAVING NOT bool_or(coalesce(a.right @> case when isempty(b.right) then null else b.right end, false))

    EXCEPT

    SELECT a.center AS available_dates
    FROM calendar a
    LEFT OUTER JOIN calendar b ON
        a.center <> b.center AND
        a.center @> b.center
    GROUP BY a.center
    HAVING NOT bool_or(COALESCE(a.center @> b.center, FALSE))
) a
WHERE NOT isempty(a.available_dates)
$$ LANGUAGE SQL STABLE;
Источник

Изначально я забыл пункт о "центральной" области. Вот оно ниже:

CREATE OR REPLACE FUNCTION travels_get_available_dates(daterange)
RETURNS TABLE(available_dates daterange)
AS $$
    WITH RECURSIVE calendar AS (
        SELECT
            $1 AS left,
             $1 AS center,
             $1 AS right
        UNION
        SELECT
            CASE travels.travel_dates && calendar.left
                WHEN TRUE THEN daterange(lower(calendar.left), lower(travels.travel_dates * calendar.left))
                ELSE daterange(lower(calendar.right), lower(travels.travel_dates * calendar.right))
            END AS left,
            CASE travels.travel_dates && calendar.left
                WHEN TRUE THEN travels.travel_dates * calendar.left
                ELSE travels.travel_dates * calendar.right
            END AS center,
            CASE travels.travel_dates && calendar.right
                WHEN TRUE THEN daterange(upper(travels.travel_dates * calendar.right), upper(calendar.right))
                ELSE daterange(upper(travels.travel_dates * calendar.left), upper(calendar.left))
            END AS right
        FROM calendar
        JOIN travels ON
            travels.travel_dates && $1 AND
            travels.travel_dates <> calendar.center AND (
                travels.travel_dates && calendar.left OR
                travels.travel_dates && calendar.right
            )
)
SELECT *
FROM (
    SELECT
        a.left AS available_dates
    FROM calendar a
    LEFT OUTER JOIN calendar b ON
        a.left <> b.left AND
        a.left @> b.left
    GROUP BY a.left
    HAVING NOT bool_or(COALESCE(a.left @> b.left, FALSE))
    UNION
    SELECT a.center AS available_dates
    FROM calendar a
    LEFT OUTER JOIN calendar b ON
        a.center <> b.center AND
        a.center @> b.center
    GROUP BY a.center
    HAVING NOT bool_or(COALESCE(a.center @> b.center, FALSE))
    UNION
    SELECT
        a.right AS available_dates
    FROM calendar a
    LEFT OUTER JOIN calendar b ON
        a.right <> b.right AND
        a.right @> b.right
    GROUP BY a.right
    HAVING NOT bool_or(COALESCE(a.right @> b.right, FALSE))
) a
WHERE NOT isempty(a.available_dates)
$$ LANGUAGE SQL STABLE;
Источник

Я думаю, что вам следует выбрать другой подход:

CREATE OR REPLACE FUNCTION travels_get_available_dates(daterange)
RETURNS TABLE(
  available_dates daterange
)
AS $$
  WITH RECURSIVE calendar(available_dates) AS
  (
    SELECT 
      CASE 
        WHEN $1 @> travel_dates THEN unnest(array[
          daterange(lower($1),lower(travel_dates)),
          daterange(upper(travel_dates),upper($1)) 
        ])
        WHEN lower($1) < lower(travel_dates) THEN daterange(lower($1),lower(travel_dates)) 
        WHEN upper($1) > upper(travel_dates) THEN daterange(upper(travel_dates),upper($1)) 
      END
    FROM travels 
      WHERE $1 && travel_dates AND NOT travel_dates @> $1
    UNION
    SELECT 
      CASE 
        WHEN available_dates @> travel_dates THEN unnest(array[
          daterange(lower(available_dates),lower(travel_dates)), 
          daterange(upper(travel_dates),upper(available_dates)) 
        ])
        WHEN lower(available_dates) < lower(travel_dates) THEN daterange(lower(available_dates),lower(travel_dates)) 
        WHEN upper(available_dates) > upper(travel_dates) THEN daterange(upper(travel_dates),upper(available_dates)) 
      END
    FROM travels 
      JOIN calendar ON available_dates && travel_dates AND NOT travel_dates @> available_dates
  )

  SELECT $1 AS available_dates 
    WHERE NOT EXISTS(SELECT 1 FROM travels WHERE travel_dates <@ $1)    
  UNION
  SELECT * FROM calendar
    WHERE $1 <> available_dates AND 'empty' <> available_dates
      AND NOT EXISTS(SELECT 1 FROM travels WHERE available_dates && travel_dates)
$$ LANGUAGE SQL STABLE;

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

Источник

Я не мог заставить работать рекурсивные функции — получался просто бесконечный цикл. Однако для решения этой проблемы не требуется повторной редукции! Вместо этого вы можете использовать PostgreSQL WINDOW.

https://www.postgresql.org/docs/current/tutorial-window.html

Учитывая оригинал:

      CREATE TABLE travels (
    id serial PRIMARY KEY,
    travel_dates daterange NOT NULL,
    EXCLUDE USING spgist (travel_dates WITH &&)
);

и вставив следующие значения:

      INSERT INTO travels (travel_dates)
VALUES
    (daterange('2018-03-02', '2018-03-02', '[]')),
    (daterange('2018-03-06', '2018-03-09', '[]')),
    (daterange('2018-03-11', '2018-03-12', '[]')),
    (daterange('2018-03-16', '2018-03-17', '[]')),
    (daterange('2018-03-25', '2018-03-27', '[]'));

Следующий SQL найдет ВСЕ доступные даты (я указал количество последовательных доступных дат, потому что это мне было нужно):

      SELECT LOWER(lead(travel_dates) OVER w) - UPPER(travel_dates) as available_count,
       UPPER(travel_dates) AS available_start
  FROM travels
WINDOW w AS (ORDER BY travel_dates ASC);

Обратите внимание, что доступный_счет имеет значение null, что указывает на бесконечную доступность в этот момент (последняя дата в таблице поездок). Если это необходимо для вашего приложения, вы можете обрабатывать значение null по-другому; Кроме того, если вы хотите ограничить это для проверки доступности между двумя заданными датами, вы можете добавить предложение WHERE таким образом (например, ограничение между 01.03.2018 и 15.03.2018):

      SELECT LOWER(lead(travel_dates) OVER w) - UPPER(travel_dates) as available_count,
       UPPER(travel_dates) AS available_start
  FROM travels
 WHERE '[2018-03-01,2018-03-15]'::daterange @> travel_dates
WINDOW w AS (ORDER BY travel_dates ASC);

В этом случае вы захотите игнорировать нулевое значение; Я не нашел самого чистого способа сделать это, но вы можете сделать это подзапросом...

      
SELECT sq.available_count, sq.available_start
  FROM (
    SELECT LOWER(lead(travel_dates) OVER w) - UPPER(travel_dates) as available_count,
           UPPER(travel_dates) AS available_start
      FROM travels
     WHERE '[2018-03-01,2018-03-15]'::daterange @> travel_dates
    WINDOW w AS (ORDER BY travel_dates ASC)
  ) AS sq
 WHERE sq.available_count is not null;

Я считаю, что есть более удобный способ сделать это... но я не знаю, что это такое :) Обратите внимание, что вы можете использовать «сегодня» в предложении WHERE, если хотите включить текущий день в свой диапазон и получить доступность между сейчас и первый день в вашей таблице «путешествия».

Я надеюсь, что это поможет кому-то еще; Вероятно, на то, чтобы разобраться в этом, ушло около двух дней работы!

Источник
Другие вопросы по тегам