postgresql 递归SQL查询与Postgres范围查找可用性

lfapxunr 于 2023-04-20 发布在 PostgreSQL

关注(0)|答案(4)|浏览(139)

我跟踪了这篇博客：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

postgresql

来源：https://stackoverflow.com/questions/60274585/recursive-sql-query-with-postgres-ranges-to-find-availability

4条答案

按热度按时间

roqulrg31#

我在原博客文章中添加了一条评论，说明我认为错误是从哪里产生的，也就是说，在处理空范围的方式中。
当日期范围是连续的，或者更确切地说是相邻的时，它在“左”列和“右”列中的任一列或甚至两者中导致“空”范围。（并且假设空范围在‘left’列中），在‘LEFT OUTER JOIN…ON…’子句中，自由且有效的travel_date与从B.left range since A.left〈〉‘empty’&& A.left @〉‘empty’的‘empty’范围配对，因为所有范围都平凡地包含空范围。理想地，它应该与NULL配对，因为这是一个左外连接，它将被包含在最终结果集中，但'empty'有点 * 碍事 *。'empty'然后再次弹出'GROUP BY... HAVING...'子句，其中.left @〉'empty'计算为true，并且它's被否定，因此所有有效的旅行日期都被丢弃，导致一个空表。我的解决方案如下，将'emptys'设置为NULL，并丢弃'center'中的任何日期范围：

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;

赞(0）回复(0）举报 2023-04-20

yyyllmsg2#

我认为你应该采取另一种方法：

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;

我们必须递归地将给定的范围拆分为左段和右段，然后只得到那些未被占用的部分。

赞(0）回复(0）举报 2023-04-20

lskq00tm3#

我原来忘记了“中心”区域的条款。下面是：

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;

赞(0）回复(0）举报 2023-04-20

sr4lhrrt4#

我不能让递归函数工作--我只会得到一个无限循环。然而，你不需要递归来解决这个问题！你可以使用PostgreSQL WINDOWS来代替。
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);

注意，available_count为null表示在该点（旅行表中的最后日期）无限可用。此外，如果你想限制这两个给定日期之间的可用性，你可以添加一个WHERE子句（例如限制在2018-03-01和2018-03-15之间）：

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子句中使用'today'，并在你的'travels'表中获得从现在到第一天的可用性。
我希望这对其他人有帮助;这大概是两天的工作来解决这个问题！

赞(0）回复(0）举报 2023-04-20

我来回答

postgresql 递归SQL查询与Postgres范围查找可用性

4条答案

相关问题

热门标签

最新问答