获得 1 小时房间可用性增量 MySQL

Question

我正在尝试以 1 小时为增量获得酒店房间的可用性。例如，如果房间是从上午 9 点到上午 10 点预订的，从上午 12 点到下午 3 点预订的，我正在尝试获取 available_from 到 available_to 之间所有其他时间的 1 小时增量

我可以在桌子上留下加入并获得房间可用性，但不是时间段。

这是我的相关架构：

旅馆：

Id | name

保留：

Id | hotel_id | room_id | start | end | status

房间：

Id | hotel_id | name | number | available_from | available_to

这是我到目前为止的查询：

SELECT r.id, r.name, r.number, r.type, r.rating
FROM rooms r
LEFT OUTER JOIN reservations res ON res.room_id = r.id 
AND CURRENT_TIMESTAMP BETWEEN r.available_from AND r.available_to
GROUP BY r.id, r.type

例：

(这是我试图从数据库中获取的数组。忽略属性名称)：

[{"房间号"：1，"可用时间"：["2019-01-01 00：00：00"，"2019-01-01 01：00：00"，"2019-01-01 02：00：00"，"2019-01-01 03：00：00"，"2019-01-01 04：00：00"，"2019-01-01

05：00：00"，"2019-01-01 06： 00：00"，"2019-01-01 07：00：00"，"2019-01-01 08：00：00"，"2019-01-01 09：00：00"，"2019-01-01 10：00：00"，"2019-01-01 11：00：00"，"2019-01-01 12：00：00"，"2019-01-01 13：00：00"，"2019-01-01 14：00：00"，"2019-01-01 15：00：00"，"2019-01-01 16：00：00"，"2019-01-01 17：00：00"，"2019-01-01 18：00：00"，">

2019-01-01 19：00：00"，"2019-01-01 20：00：00"，"2019-01-01 21：00：00"，"2019-01-01 22：00：00"，"2019-01-01 23：00：00"]}]

我尝试了以下方法：

SELECT free_from, free_until
FROM (
SELECT a.end AS free_from,
(SELECT MIN(c.start)
FROM reservations c
WHERE c.start > a.end) as free_until
FROM reservations a
WHERE NOT EXISTS (
SELECT 1
FROM reservations b
WHERE b.start BETWEEN a.end AND a.end + INTERVAL 1 HOUR
)
AND a.end BETWEEN '2019-01-03 09:00' AND '2019-01-03 21:00'
) as d
ORDER BY free_until-free_from
LIMIT 0,3;

但是我只返回一行，结果也是不正确的。如何解决这个问题？

示例数据：

旅馆：

1 | Marriott

保留：

1 | 1 | 1 | 2019-01-03 15:00:00 | 2019-01-03 17:00:00 | Confirmed
1 | 1 | 1 | 2019-01-03 18:00:00 | 2019-01-03 20:00:00 | Confirmed

房间：

1 | 1 | "Single" | 528 | 09:00:00 | 21:00:00

预期成果

房间编号 |房间名称 |可用时间

1 | "Single" | 2019-01-03 09:00:00, 2019-01-03 10:00:00, 2019-01-03 11:00:00, 2019-01-03 12:00:00, 2019-01-03 13:00:00, 2019-01-03 14:00:00, 2019-01-03 17:00:00, 2019-01-03 20:00:00, 2019-01-03 21:00:00, 2019-01-03 22:00:00, 2019-01-03 23:00:00, 2019-01-03 24:00:00

Answer 1

如果将Times_Slots表添加到数据库中，如以下 SQL Fiddle 所示：

CREATE TABLE Time_Slots
(`Slot` time);
INSERT INTO Time_Slots
(`Slot`)
VALUES
('00:00:00'),
('01:00:00'),
('02:00:00'),
('03:00:00'),
('04:00:00'),
('05:00:00'),
('06:00:00'),
('07:00:00'),
('08:00:00'),
('09:00:00'),
('10:00:00'),
('11:00:00'),
('12:00:00'),
('13:00:00'),
('14:00:00'),
('15:00:00'),
('16:00:00'),
('17:00:00'),
('18:00:00'),
('19:00:00'),
('20:00:00'),
('21:00:00'),
('22:00:00'),
('23:00:00');

然后，以下查询将提供所有预订客房的空房情况：

查询 1：

select r.id
, r.Name
, res_date + interval t.slot hour_second available
from Time_Slots t
join Rooms r
on t.Slot between r.available_from and r.available_to
join (select distinct room_id, date(start) res_date from Reservation) res
on res.room_id = r.id
where (r.id, res_date + interval t.slot hour_second) not in (
select r.room_id
, date(r.start) + interval t.slot hour_second Reserved
from Time_Slots t
join Reservation r
on r.start <= date(r.end) + interval t.slot hour_second
and date(r.start) + interval t.slot hour_second < r.end)

此查询的工作原理是，首先从Times_Slots中为该会议室至少有一个预留选择可用时段，然后筛选出预留的时隙。

结果：

| id |   Name |            available |
|----|--------|----------------------|
|  1 | Single | 2019-01-03T09:00:00Z |
|  1 | Single | 2019-01-03T10:00:00Z |
|  1 | Single | 2019-01-03T11:00:00Z |
|  1 | Single | 2019-01-03T12:00:00Z |
|  1 | Single | 2019-01-03T13:00:00Z |
|  1 | Single | 2019-01-03T14:00:00Z |
|  1 | Single | 2019-01-03T17:00:00Z |
|  1 | Single | 2019-01-03T20:00:00Z |
|  1 | Single | 2019-01-03T21:00:00Z |

在您的示例输出中，您指示房间在 2019-01-03 22：00：00、2019-01-03 23：00：00、2019-01-03 24：00：00 可用，但是这些时间是在房间表定义的可用性块之后，所以我的查询排除了这些时间。

Answer 2

您遇到的第一个问题是您的架构设置很差。您没有良好的数据规范化。1) 重命名字段以提高清晰度。2) 将这两个表更改为如下所示：

Reservation:
Res_ID | hotel_id | room_id | res_start | res_end | status
Rooms:
Room_ID | hotel_id | room_name | room_number | available_from | available_to

您将需要一个定义了时间段的表。您可以使用 CTE 进行，然后将其与您的房间CROSS JOIN。这是CROSS JOIN有用的少数情况之一。

现在像这样执行查询：

WITH timeslots AS (
SELECT CURRENT_DATE() AS time_slot UNION
SELECT CURRENT_DATE() + 1/24   UNION
SELECT CURRENT_DATE() + 2/24   UNION
SELECT CURRENT_DATE() + 3/24   UNION
SELECT CURRENT_DATE() + 4/24   UNION
SELECT CURRENT_DATE() + 5/24   UNION
SELECT CURRENT_DATE() + 6/24   UNION
SELECT CURRENT_DATE() + 7/24   UNION
SELECT CURRENT_DATE() + 8/24   UNION
SELECT CURRENT_DATE() + 9/24   UNION
SELECT CURRENT_DATE() + 10/24  UNION
SELECT CURRENT_DATE() + 11/24  UNION
SELECT CURRENT_DATE() + 12/24  UNION
SELECT CURRENT_DATE() + 13/24  UNION
SELECT CURRENT_DATE() + 14/24  UNION
SELECT CURRENT_DATE() + 15/24  UNION
SELECT CURRENT_DATE() + 16/24  UNION
SELECT CURRENT_DATE() + 17/24  UNION
SELECT CURRENT_DATE() + 18/24  UNION
SELECT CURRENT_DATE() + 19/24  UNION
SELECT CURRENT_DATE() + 20/24  UNION
SELECT CURRENT_DATE() + 21/24  UNION
SELECT CURRENT_DATE() + 22/24  UNION
SELECT CURRENT_DATE() + 23/24 )
SELECT r.id, r.name, r.number, r.type, r.rating, 
t.time_slot AS time_slot_open, 
t.time_slot + 1/24 AS time_slot_close,
res.Res_ID
FROM rooms r 
CROSS JOIN timeslots t
LEFT JOIN reservation res ON res.hotel_id = r.hotel_id AND res.room_id = r.room_id 
AND time_slot_open >= res.res_start AND time_slot_open < res.res_close

这将为您提供所有酒店房间的列表，每个房间有 24 条记录。如果该房间有预订，则它将显示该时段的预订ID。从这里，您可以按原样使用数据，也可以进一步将其放入其自己的 CTE 中，然后从中选择预留 ID 为空的所有内容。您还可以根据该 ID 加入或查找有关预留的其他数据。

更新

如果您运行 8.0 之前的 MySQL 版本，则不支持WITH子句(请参阅：如何在 MySQL 中使用"WITH"子句？您必须将其设置为如下所示的子查询：

SELECT r.id, r.name, r.number, r.type, r.rating, 
t.time_slot AS time_slot_open, 
t.time_slot + 1/24 AS time_slot_close,
res.Res_ID
FROM rooms r 
CROSS JOIN (SELECT CURRENT_DATE() AS time_slot UNION
SELECT CURRENT_DATE() + 1/24   UNION
SELECT CURRENT_DATE() + 2/24   UNION
SELECT CURRENT_DATE() + 3/24   UNION
SELECT CURRENT_DATE() + 4/24   UNION
SELECT CURRENT_DATE() + 5/24   UNION
SELECT CURRENT_DATE() + 6/24   UNION
SELECT CURRENT_DATE() + 7/24   UNION
SELECT CURRENT_DATE() + 8/24   UNION
SELECT CURRENT_DATE() + 9/24   UNION
SELECT CURRENT_DATE() + 10/24  UNION
SELECT CURRENT_DATE() + 11/24  UNION
SELECT CURRENT_DATE() + 12/24  UNION
SELECT CURRENT_DATE() + 13/24  UNION
SELECT CURRENT_DATE() + 14/24  UNION
SELECT CURRENT_DATE() + 15/24  UNION
SELECT CURRENT_DATE() + 16/24  UNION
SELECT CURRENT_DATE() + 17/24  UNION
SELECT CURRENT_DATE() + 18/24  UNION
SELECT CURRENT_DATE() + 19/24  UNION
SELECT CURRENT_DATE() + 20/24  UNION
SELECT CURRENT_DATE() + 21/24  UNION
SELECT CURRENT_DATE() + 22/24  UNION
SELECT CURRENT_DATE() + 23/24 ) t
LEFT JOIN reservation res ON res.hotel_id = r.hotel_id AND res.room_id = r.room_id 
AND time_slot_open >= res.res_start AND time_slot_open < res.res_close

Answer 3

计划 A(每小时一行)

• 摆脱 T 和 Z;MySQL不理解这种语法。
• 您的汽车旅馆位于一个时区，对吗？ 然后使用DATETIME或TIMESTAMP是等效的。
• 对于 3 小时的预订，请排 3 行。 (使用范围可能会更混乱。
• 唉，你使用的是MySQL，而不是MariaDB;后者有自动序列生成器。 示例：名为seq_0_to_23的伪表的作用类似于预填充了数字 0 到 23 的表。
• 查找可用时间需要有一个包含所有日期所有可能时间的表格，因此上面的注释。
要么
• 做算术，要么离开几个小时：
• 由于 LEFT 简单明了，

我将讨论它：

mysql> SELECT NOW(), LEFT(NOW(), 13);
+---------------------+-----------------+
| NOW()               | LEFT(NOW(), 13) |
+---------------------+-----------------+
| 2019-01-03 13:43:56 | 2019-01-03 13   |
+---------------------+-----------------+
1 row in set (0.00 sec)

第二列显示一个字符串，可用于指示当天的下午 1 点。

计划B(范围)

另一种方法是使用范围。 但是，处理过程很复杂，因为所有时间始终与预订或"可用"相关联。 代码变得复杂，但性能很好：http://mysql.rjweb.org/doc.php/ipranges

计划 C(位)

该表包含一个日期(无时间)，以及一个恰好是 24 位的MEDIUMINT UNSIGNED。 每个位代表一天中的一个小时。

使用各种布尔运算：

• |(OR) 位在一起以查看分配的小时数。
• 0xFFFFFF & ~hours看看有什么可用的。
• BIT_COUNT()计算位数(小时)。
• 虽然可以在 SQL 中确定房间的可用时间，但最好在客户端代码中执行此操作。 我假设你有一个PHP/Java/任何前端！
• 等。

更多？

您想更详细地讨论其中任何一个吗？

Answer 4

您需要将房间表与时隙表(24 行)连接起来。这将生成给定房间的所有可能时间段的列表。过滤掉不可用的时隙是微不足道的：

SELECT rooms.id, rooms.name, TIMESTAMP(checkdates.checkdate, timeslots.timeslot) AS datetimeslot
FROM rooms
INNER JOIN (
SELECT CAST('00:00' AS TIME) AS timeslot UNION
SELECT CAST('01:00' AS TIME) UNION
SELECT CAST('02:00' AS TIME) UNION
SELECT CAST('03:00' AS TIME) UNION
SELECT CAST('04:00' AS TIME) UNION
SELECT CAST('05:00' AS TIME) UNION
SELECT CAST('06:00' AS TIME) UNION
SELECT CAST('07:00' AS TIME) UNION
SELECT CAST('08:00' AS TIME) UNION
SELECT CAST('09:00' AS TIME) UNION
SELECT CAST('10:00' AS TIME) UNION
SELECT CAST('11:00' AS TIME) UNION
SELECT CAST('12:00' AS TIME) UNION
SELECT CAST('13:00' AS TIME) UNION
SELECT CAST('14:00' AS TIME) UNION
SELECT CAST('15:00' AS TIME) UNION
SELECT CAST('16:00' AS TIME) UNION
SELECT CAST('17:00' AS TIME) UNION
SELECT CAST('18:00' AS TIME) UNION
SELECT CAST('19:00' AS TIME) UNION
SELECT CAST('20:00' AS TIME) UNION
SELECT CAST('21:00' AS TIME) UNION
SELECT CAST('22:00' AS TIME) UNION
SELECT CAST('23:00' AS TIME)
) AS timeslots ON timeslots.timeslot >= rooms.available_from
AND timeslots.timeslot <  rooms.available_to
CROSS JOIN (
SELECT CAST('2019-01-03' AS DATE) AS checkdate
) AS checkdates
WHERE NOT EXISTS (
SELECT 1
FROM reservations
WHERE room_id = rooms.id
AND TIMESTAMP(checkdates.checkdate, timeslots.timeslot) >= `start`
AND TIMESTAMP(checkdates.checkdate, timeslots.timeslot) <  `end`
)

DB小提琴上的演示

上述查询检查一个日期 (2019-01-03) 的可用性。对于多个日期，只需将它们添加到checkdates.