连续预留计数SQL查询

Question

到目前为止，我已经进行了以下查询:

SELECT
SUM(CASE WHEN CAST(check_in AS date) = CAST('$date' as date) THEN 1 ELSE 0 END) AS total_arrivals,
SUM(CASE WHEN CAST(check_out AS date) = CAST('$date' as date) THEN 1 ELSE 0 END) AS total_departures,
SUM(CASE WHEN (CAST(check_in AS date) <= CAST('$date' as date) AND CAST(check_out AS date) > CAST('$date' as date)) THEN 1 ELSE 0 END) AS total_stayovers
FROM bookings_beds24

上面这个非常简单的查询取一个$date，并计算该日期的check_in,check_out和停留时间。

我希望创建一个查询，这将允许我计数/计算切换。切换是两个不同的预订，其中一个退房，另一个在同一天入住。

你认为这样的事情是可能的只用一个sql查询吗?我目前正在迁移上述三个查询，同时我希望能够计算连续的预订。

请记住，bookings_beds24表保存check_in,check_out日期和room_id，因为它存储多个房间的数据。

编辑:目前正在做类似的事情，但显然不起作用:

SELECT * 
FROM 
(SELECT check_in FROM bookings_beds24 WHERE room_id='208979') AS a, 
(SELECT COUNT(book_id) AS total FROM bookings_beds24 WHERE check_out=a.check_in AND room_id='208979') AS b;

Answer 1

我将从部分改写您当前的查询开始。

假设:

• check_in和check_out是datetime或timestamp数据类型(意味着它们存储日期和时间)
• $date为查询参数(?)，给出date(不含时间部分)

我们可以:

• 使用子查询，所以我们不需要多次重复参数
• 将部分日期过滤逻辑移动到join
的on子句中
• 避免使用半打开间隔来铸造时间戳和过滤器(这更有效)
• 利用MySQL在数字上下文中计算谓词为0(false)和1(true)的事实来缩短条件sums。

:

select 
sum(b.check_in  >= r.refdate)                  as total_arrivals,
sum(b.check_out <  r.refdate + interval 1 day) as total_departures,
sum(b.check_in < r.refdate and b.check_out >= r.refdate + interval 1 day) as total_stayovers
from (select ? refdate) r
inner join bookings_beds24 b 
on b.check_in < r.refdate + interval 1 day and b.check_out >= r.refdate

我怀疑你对留宿的定义;您的查询将当天所有到达和离开的旅客都算作停留旅客，这似乎是不对的;我的帐户只接受在参考日期之前开始并在晚一天结束的帐户预订。

---

当涉及到计算切换时:一种选择是按房间预聚合，这样我们就可以确定在同一房间发生的到达/离开:

select 
sum(arrivals)                        as total_arrivals, 
sum(departures)                      as total_departures, 
sum(stayovers)                       as total_stayovers,
sum(arrivals = 1 and departures = 1) as total_switchovers
from (
select b.room_id,
sum(b.check_in  >= r.refdate)                  as arrivals,
sum(b.check_out <  r.refdate + interval 1 day) as departures,
sum(b.check_in < r.refdate and b.check_out >= r.refdate + interval 1 day) as stayovers
from (select ? refdate) r
inner join bookings_beds24 b 
on b.check_in < r.refdate + interval 1 day and b.check_out >= r.refdate
group by b.room_id
) b

请注意，在这种逻辑下，转换总是算作出发和到达(另一方面，当然不是所有的出发或到达都是转换)。

---

这里是一些示例数据，我们将使用它们作为参考日期'2023-04-07'。

create table bookings_beds24 (
room_id int,
check_in datetime,
check_out datetime
);
insert into bookings_beds24 values 
(1, '2023-04-05 12:00:00', '2023-04-06 12:00:00'),  -- irrelevant
(1, '2023-04-07 12:00:00', '2023-04-08 12:00:00'),  -- arrival
(2, '2023-04-05 12:00:00', '2023-04-07 12:00:00'),  -- departure
(3, '2023-04-05 12:00:00', '2023-04-08 12:00:00'),  -- stayover
(4, '2023-04-06 12:00:00', '2023-04-07 12:00:00'),  -- switchover (departure)
(4, '2023-04-07 12:00:00', '2023-04-10 12:00:00')   -- switchover (arrival)
;

运行日期'2023-04-07'的查询产生:

total_switchovers