sql - 如何在已使用sql预订的房间列表中获得空闲时间

Question

我在使用数据库，但在计算酒店预订房间的可用时间时遇到问题

我的 table 看起来像

房间:

 _________________________________________________________________
| roomid  | roomname  | type      | time_start  | time_end        |
|_________|___________|___________|_____________|_________________|
|  R01    |  Room1    |     1     |  07:00      |    07:30        |
|_________|___________|___________|_____________|_________________|
|  R01    |  Room1    |     2     |  08:30      |    9:30         |
|_________|___________|___________|_____________|_________________|
|  R02    |  Room2     |   3      |  7:30       |   8:15          |    
|_________|___________|___________|_____________|_________________|
|  R02    |  Room2    |  4        |  10:15      |   11:20         |
|_________|___________|___________|_____________|_________________|

我有一个开始时间和持续时间的预订房间。我想要返回 time_start 和 time_end 可用用户可以预订房间。示例:我想在数据库中查询和选择时间几乎是用户输入的时间和持续时间空闲时间必须 >=30。用户希望预订从 8:00 开始，预订持续时间为 30 分钟。在我的数据库中，您可以看到我有两个预订房间。房间 01 预订 7:00 -> 7h:30 和 8:30 ->9:30 这意味着房间 1 可用 7h31 -> 8:29。房间 02 预订 7:30 -> 8:15 和 10:15 ->11:20 表示房间 1 可用时间为 8:16 -> 10:14。

我有两个房间用户可以预订:

1 . Room01:   7h31 -> 8:29

2. Room02: 8:16 -> 10:14

因为条件是预订时间最接近用户输入的时间并且预订时间必须大于等于30。这意味着:我选择 room1 用户可以预订，我希望返回时间可用用户可以预订(7h31 -> 8:29)。如果用户在 5:00:00 预订房间但在我的预订表中时间从 7:00:00 开始到 11:00:00 结束，我想把用户的预订时间和结束时间设为5:00:00。我希望返回的查询是开始时间:5:00:00 和结束时间:5:30:00。我该怎么做？

我试试写sql代码的样子

SELECT r.start_time, r.end_time
FROM rooms r
  INNER JOIN
  (
    SELECT room_id, r.time_start - r.end_time > :input_time)
    FROM r
    GROUP BY room_id
  ) r1
  ON b1.room_id = b.room_id

但我认为这行不通。

我有一个问题。如何返回 time_start 和 time_end 可用用户可以预订房间条件预订时间最接近用户输入时间，如果我有 start_time 和列表预订房间，预订时间必须大于或等于 30。如果用户在 5:00:00 预订房间但在我的预订表中时间从 7:00:00 开始到 11:00:00 结束，我想把用户的预订时间和结束时间设为5:00:00。我希望返回的查询是 time_start: 5:00:00 和 time_end: 5:30:00。我该怎么做？

Answer 1

demo: db<>fiddle

使用滞后() window function给你预期的结果:

SELECT
    room_id,
    last_time + interval '1 minute' AS free_time_start,
    time_start - interval '1 minute' AS free_time_end
FROM (
    SELECT
        lag(time_end) OVER (PARTITION BY room_id ORDER BY time_start) AS last_time,
        *
    FROM
        rooms
) s
WHERE last_time < time_start

窗口函数允许您从之前的记录中获取一个值(您可以使用 PARTITION BY 子句将其分组在 room_id 上，并且必须添加特定的顺序)。

如果前一个结束时间小于当前开始时间，则存在空闲时间。

---

添加预约时间:

您可以抽取预约时间，查看是否符合计算的空闲时间。之后，您可以按合适的插槽排序，并使用 LIMIT

仅给出第一个结果

SELECT
    free_time_start,
    free_time_end,
    booking_start BETWEEN free_time_start AND free_time_end
        AND 
    booking_end BETWEEN free_time_start AND free_time_end
FROM (
    SELECT
        room_id,
        last_time + interval '1 minute' AS free_time_start,
        time_start - interval '1 minute' AS free_time_end,
        '08:00'::time AS booking_start,
        '08:00'::time + interval '29 minutes' AS booking_end
    FROM (
        SELECT
            lag(time_end) OVER (PARTITION BY room_id ORDER BY time_start) AS last_time,
            *
        FROM
            rooms
    ) s
    WHERE last_time < time_start
) s
ORDER BY 3 DESC, 1
LIMIT 1

demo: db<>fiddle