获得1小时客房可用性增量

k3bvogb1 于 2021-06-17 发布在 Mysql

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

我正设法增加一个小时的客房供应量。例如，如果房间是从上午9点到10点，从上午12点到下午3点，我试图得到从可用时间到可用时间的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

例子：
（这是我试图从数据库中获取的数组。忽略属性名称）：
[{“roomnumber”：1，“可用时间”：[“2019-01-01 00:00:00”，“2019-01-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”，“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

预期结果
房间id |房间名称|可用时间

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

sql mysql Join

来源：https://stackoverflow.com/questions/53989112/get-1-hour-incrementation-for-room-availability-mysql

4条答案

按热度按时间

mmvthczy1#

如果将times\u slots表添加到数据库中，如以下sql文件所示：

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，但是这些时间在房间表定义的可用性块之后，因此我的查询排除了这些时间。

赞(0）回复(0）举报 2021-06-17

ejk8hzay2#

方案a（每小时一排）
去掉t和z；mysql不理解这种语法。
你的汽车旅馆在同一个时区，对吗？然后使用 DATETIME 或者 TIMESTAMP 是等价的。
预订3小时，请排3排(使用靶场可能会更麻烦。）
唉，您使用的是mysql，而不是mariadb；后者有自动序列发生器。示例：名为 seq_0_to_23 就像一个预先填充了数字0到23的表。
查找可用时间需要有一个表，其中包含所有天中所有可能的小时数，因此上面有注解。
要么做算术，要么留几个小时：
因为左撇子简单明了，
我来讨论一下：

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（bits）
这个表包含一个日期（没有时间），加上一个 MEDIUMINT UNSIGNED 正好是24位。每一位代表一天中的一小时。
使用各种布尔运算： | （或）将这些位放在一起以查看分配的小时数。 0xFFFFFF & ~hours 查看可用的内容。 BIT_COUNT() 数位（小时）。
虽然在sql中可以确定房间的可用时间，但在客户机代码中这样做可能更好。我假设你有一个php/java/任何前端！
等。
更多？
你想更详细地讨论一下吗？

赞(0）回复(0）举报 2021-06-17

xmq68pz93#

您需要将rooms表与时间段表（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 .

赞(0）回复(0）举报 2021-06-17

0pizxfdo4#

您遇到的第一个问题是模式设置很差。你没有很好的数据规范化。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为null的所有内容。您还可以根据该id加入或查找有关预订的其他数据。
更新
如果运行mysql 8.0之前的版本，则 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

赞(0）回复(0）举报 2021-06-17

我来回答

获得1小时客房可用性增量

4条答案

相关问题

热门标签

最新问答