在SQL(Postgresql)中检查预订是否可行

Question

我有这样一张预订表：

其中包含以下记录：

  id  |        from         |         to
------+---------------------+---------------------
  101 | 2015-09-24 08:00:00 | 2015-09-24 09:30:00
 2261 | 2015-09-24 09:00:00 | 2015-09-24 10:00:00
 4061 | 2015-09-24 10:00:00 | 2015-09-24 10:30:00
  204 | 2015-09-24 12:00:00 | 2015-09-24 13:30:00
 2400 | 2015-09-24 13:30:00 | 2015-09-24 14:00:00
 4224 | 2015-09-24 14:00:00 | 2015-09-24 14:30:00
  309 | 2015-09-24 16:00:00 | 2015-09-24 17:30:00
 2541 | 2015-09-24 17:00:00 | 2015-09-24 18:00:00

我正在寻找最佳查询来回答以下问题：

在上面的记录中，是否有可能找到持续时间为x（例如30分钟）的时间段？

我考虑使用Postgres数组或时间范围，但仍在寻找更好的想法....

编辑：我将提供“虚假”预订作为边界，但如果您有更好的想法，请写下来 :)

- Sławosz

---

问题似乎不完整。如果您没有预订，那么您无法找到时间段。换句话说，您需要提供开始和结束时间信息。 - Gordon Linoff

@GordonLinoff：你说得对。我目前假设 [负无穷大，正无穷大]。 - wildplasser

我编辑了问题，感谢您的提醒！ - Sławosz

Answer 1

类似这样的内容：

select t1.*
from tablename t1
where (select min("from") from tablename t2
       where t2."from" > t1."from") >= t1."to" + interval '30' minute

如果与下一行之间的间隔大于或等于30分钟，则返回一行。

注意：在ANSI SQL中，“from”和“to”是保留字，因此它们被限定为“from”和“to”。

- jarlh

Answer 2

这里提供一种使用分析函数的解决方案，它可以提供所有没有预订的窗口：

SELECT null as ts_from, min(ts_from) as ts_to
  FROM bookings
 UNION ALL
SELECT ts as ts_from, next_ts as ts_to
  FROM (SELECT ts, lead(ts, 1) over (order by ts) as next_ts, sum(bk) over (order by ts) as bksum
          FROM (SELECT ts_from as ts, 1 as bk
                  FROM bookings
                 UNION ALL
                SELECT ts_to as ts, -1 as bk
                  FROM bookings) as t) as tt
 WHERE bksum = 0
 ORDER BY 1 NULLS FIRST;

SQL Fiddle 这里。

（保留HTML）

- Kombajn zbożowy

Answer 3

您可以使用lag()函数：

select *, book_start- previous_book_end timeslot
from (
    select id, "from" book_start, "to" book_end, 
    lag("to") over (order by "to") previous_book_end
    from test
    ) sub
order by book_end

  id  |     book_start      |      book_end       |  previous_book_end  | timeslot
------+---------------------+---------------------+---------------------+-----------
  101 | 2015-09-24 08:00:00 | 2015-09-24 09:30:00 |                     |
 2261 | 2015-09-24 09:00:00 | 2015-09-24 10:00:00 | 2015-09-24 09:30:00 | -00:30:00
 4061 | 2015-09-24 10:00:00 | 2015-09-24 10:30:00 | 2015-09-24 10:00:00 | 00:00:00
  204 | 2015-09-24 12:00:00 | 2015-09-24 13:30:00 | 2015-09-24 10:30:00 | 01:30:00
 2400 | 2015-09-24 13:30:00 | 2015-09-24 14:00:00 | 2015-09-24 13:30:00 | 00:00:00
 4224 | 2015-09-24 14:00:00 | 2015-09-24 14:30:00 | 2015-09-24 14:00:00 | 00:00:00
  309 | 2015-09-24 16:00:00 | 2015-09-24 17:30:00 | 2015-09-24 14:30:00 | 01:30:00
 2541 | 2015-09-24 17:00:00 | 2015-09-24 18:00:00 | 2015-09-24 17:30:00 | -00:30:00
(8 rows)    

选取 timeslots >= '30m'::interval的行：

select *, book_start- previous_book_end timeslot
from (
    select id, "from" book_start, "to" book_end, 
    lag("to") over (order by "to") previous_book_end
    from test
    ) sub
where book_start- previous_book_end >= '30m'::interval
order by book_end

 id  |     book_start      |      book_end       |  previous_book_end  | timeslot
-----+---------------------+---------------------+---------------------+----------
 204 | 2015-09-24 12:00:00 | 2015-09-24 13:30:00 | 2015-09-24 10:30:00 | 01:30:00
 309 | 2015-09-24 16:00:00 | 2015-09-24 17:30:00 | 2015-09-24 14:30:00 | 01:30:00
(2 rows)

- klin

---

你不能只看之前的预订。想象一下第一个预订覆盖了整个时间段。否则你会报告错误的插槽（示例）。 - Kombajn zbożowy

当然，我可以想象一切。也许更好的想法是让原帖作者评估这样的假设情况是否可能发生？ - klin

好的，我犯了错误，我们可以假设预订不会重叠（例如在示例中确实会发生，但表中有一些外键）。 - Sławosz

这符合我理解预订本质的方式。我回答中的查询可以帮助避免这种情况。好的，@Kombajnzbożowy，打负分有点过早了，不是吗？ - klin

是的，考虑到不重叠预订的限制，您的解决方案很好。我一开始只是假设最坏情况。 - Kombajn zbożowy

Answer 4

非标准自连接：

SELECT
    ll.ts_to AS ts_from
    , hh.ts_from AS ts_to
FROM bookings ll
JOIN bookings hh
    -- enough space 
    ON hh.ts_from >= ll.ts_to + '30 min'::interval
    -- and nothing in between
    AND NOT EXISTS (
        SELECT * FROM bookings nx
        WHERE nx.ts_from >= ll.ts_to
        AND nx.ts_to <= hh.ts_from
        )
UNION ALL   -- before the first
SELECT '-infinity'::timestamp AS ts_from
       , MIN(ts_from) AS ts_to
    FROM bookings
UNION ALL   -- after the last
SELECT MAX(ts_to) AS ts_from
       , 'infinity'::timestamp AS ts_to
    FROM bookings
ORDER BY 1,2
    ;

- wildplasser

Answer 5

生成您的插槽，然后将它们左连接。 http://sqlfiddle.com/#!15/12bfa

create table t (id integer, "from" timestamp, "to" timestamp);

insert into t values 
(101 , '2015-09-24 08:00:00' , '2015-09-24 09:30:00' ),
(2261 , '2015-09-24 09:00:00' , '2015-09-24 10:00:00' ),
(4061 , '2015-09-24 10:00:00' , '2015-09-24 10:30:00' ),
( 204 , '2015-09-24 12:00:00' , '2015-09-24 13:30:00' ),
(2400 , '2015-09-24 13:30:00' , '2015-09-24 14:00:00' ),
(4224 , '2015-09-24 14:00:00' , '2015-09-24 14:30:00' ),
( 309 , '2015-09-24 16:00:00' , '2015-09-24 17:30:00' ),
(2541 , '2015-09-24 17:00:00' , '2015-09-24 18:00:00' );

SELECT time_slots.t,
       time_slots.t + interval '30 minutes'
FROM generate_series(date'2015-09-24',date'2015-09-25' - interval '30 minutes' ,interval '30 minutes') AS time_slots(t)
LEFT JOIN t ON (time_slots.t BETWEEN t."from" AND t."to")
WHERE t.id IS NULL;


SELECT time_slots.t,
       time_slots.t + interval '30 minutes'
FROM generate_series(date'2015-09-24',date'2015-09-25' - interval '30 minutes',interval '30 minutes') AS time_slots(t)
LEFT JOIN t ON ((time_slots.t,
                 time_slots.t + interval '30 minutes') OVERLAPS (t."from",
                                                                 t."to"))
WHERE t.id IS NULL;

- Slava Lenskyy