SQL 酒店入住率：已住天数/可售天数（携程面试题）
一、题目
统计各酒店1月的入住率。入住率 = 实际入住房间·天数 / (总房间数 × 31天)，只统计 status='completed' 的订单，按入住率降序输出。
t1_hotel_info 酒店信息表：
+-----------+---------------+-------+--------------+
| hotel_id | hotel_name | city | total_rooms |
+-----------+---------------+-------+--------------+
| 1001 | 如家酒店(北京国贸店) | 北京 | 120 |
| 1002 | 汉庭酒店(上海南京路店) | 上海 | 80 |
| 1003 | 7天酒店(广州天河店) | 广州 | 100 |
+-----------+---------------+-------+--------------+
t1_order_info 订单信息表：
+--------------+-----------+---------------+----------------+-------------+------------+
| order_id | hotel_id | checkin_date | checkout_date | room_count | status |
+--------------+-----------+---------------+----------------+-------------+------------+
| 20250101001 | 1001 | 2025-01-01 | 2025-01-03 | 1 | completed |
| 20250102001 | 1001 | 2025-01-02 | 2025-01-04 | 2 | completed |
| 20250101002 | 1002 | 2025-01-01 | 2025-01-02 | 1 | cancelled |
| 20250103001 | 1002 | 2025-01-03 | 2025-01-05 | 1 | completed |
| 20250104001 | 1003 | 2025-01-04 | 2025-01-06 | 1 | completed |
| 20250105001 | 1003 | 2025-01-05 | 2025-01-07 | 2 | completed |
+--------------+-----------+---------------+----------------+-------------+------------+
二、思路分析
| 维度 | 评分 |
|---|---|
| 题目难度 | ⭐ |
| 题目清晰度 | ⭐⭐⭐⭐⭐ |
| 业务常见度 | ⭐⭐⭐⭐⭐ |
- 关联酒店与订单表，只保留 status='completed' 且入住日期与1月有交集的订单
- 使用 datediff + least/greatest 截取订单在1月内的实际入住天数
- 房间·天数 = 入住天数 x room_count
- 按 hotel_id 汇总已入住房间·天数
- 总可用房间·天数 = total_rooms x 31
- 入住率 = 已入住房间·天数 / 总可用房间·天数，round 保留 4 位小数
- 按入住率降序排列
三、逐步推导
1. 计算每个酒店1月已入住房间·天数
关联两张表，用 least/greatest 截取订单在1月内的入住天数，乘以房间数得到房间·天数，按酒店汇总。
执行SQL
select
h.hotel_id,
h.hotel_name,
h.city,
h.total_rooms,
sum(
datediff(
least(o.checkout_date, '2025-01-31'),
greatest(o.checkin_date, '2025-01-01')
) * o.room_count
) as occupied_room_days
from t1_hotel_info h
join t1_order_info o
on h.hotel_id = o.hotel_id
and o.status = 'completed'
and o.checkin_date < '2025-02-01'
and o.checkout_date > '2025-01-01'
group by h.hotel_id, h.hotel_name, h.city, h.total_rooms;
执行结果
+-----------+---------------+-------+--------------+---------------------+
| hotel_id | hotel_name | city | total_rooms | occupied_room_days |
+-----------+---------------+-------+--------------+---------------------+
| 1001 | 如家酒店(北京国贸店) | 北京 | 120 | 6 |
| 1002 | 汉庭酒店(上海南京路店) | 上海 | 80 | 2 |
| 1003 | 7天酒店(广州天河店) | 广州 | 100 | 6 |
+-----------+---------------+-------+--------------+---------------------+
3 rows selected (0.626 seconds)(https://www.dwsql.com)
2. 计算入住率并排序
基于上一步结果，计算总可用房间·天数（total_rooms x 31），入住率 = 已入住 / 总可用，round 保留 4 位小数，降序输出。
执行SQL
with occupancy as (
select
h.hotel_id,
h.hotel_name,
h.city,
h.total_rooms,
sum(
datediff(
least(o.checkout_date, '2025-01-31'),
greatest(o.checkin_date, '2025-01-01')
) * o.room_count
) as occupied_room_days
from t1_hotel_info h
join t1_order_info o
on h.hotel_id = o.hotel_id
and o.status = 'completed'
and o.checkin_date < '2025-02-01'
and o.checkout_date > '2025-01-01'
group by h.hotel_id, h.hotel_name, h.city, h.total_rooms
)
select
hotel_id,
hotel_name,
city,
occupied_room_days,
total_rooms * 31 as total_available_room_days,
round(occupied_room_days / (total_rooms * 31), 4) as occupancy_rate
from occupancy
order by occupancy_rate desc;
执行结果
+-----------+---------------+-------+---------------------+----------------------------+-----------------+
| hotel_id | hotel_name | city | occupied_room_days | total_available_room_days | occupancy_rate |
+-----------+---------------+-------+---------------------+----------------------------+-----------------+
| 1003 | 7天酒店(广州天河店) | 广州 | 6 | 3100 | 0.0019 |
| 1001 | 如家酒店(北京国贸店) | 北京 | 6 | 3720 | 0.0016 |
| 1002 | 汉庭酒店(上海南京路店) | 上海 | 2 | 2480 | 8.0E-4 |
+-----------+---------------+-------+---------------------+----------------------------+-----------------+
3 rows selected (0.73 seconds)(https://www.dwsql.com)
四、常见坑点
坑1：datediff 边界计算 — datediff(checkout, checkin) 返回的是入住天数（ Nights ），checkin 当天计入，checkout 当天不计。要统计1月范围内的数据，需要用 least/greatest 把日期边界截断到月内：least(checkout_date, '2025-01-31') 防止超出月末，greatest(checkin_date, '2025-01-01') 防止超出月初。
坑2：跨月订单处理 — 订单的 checkin_date 可能在 12 月、checkout_date 在 2 月，实际在 1 月的入住天数只是中间那一段。where 条件需要宽松匹配（checkin_date < '2025-02-01' and checkout_date > '2025-01-01'），然后在 select 中用 least/greatest 精确截取 1 月内的天数，避免遗漏或重复计算。
坑3：已取消订单不应计入 — status='cancelled' 的订单没有实际入住，必须过滤掉，只保留 status='completed' 的订单参与入住率计算。
五、知识点总结
| 考点 | 说明 |
|---|---|
| datediff + least/greatest | 通过日期函数截取订单在目标月份内的实际入住天数，处理跨月边界 |
| join + group by | 关联酒店与订单表，按酒店维度汇总已入住房间·天数 |
| round | 保留指定小数位数，入住率精确到 4 位小数 |
| sum 聚合 | 汇总每个酒店所有订单的房间·天数（入住天数 x 房间数） |
六、建表语句
点击展开 DDL & DML
-- 酒店信息表
create table t1_hotel_info (
hotel_id int comment '酒店id',
hotel_name string comment '酒店名称',
city string comment '所在城市',
total_rooms int comment '总房间数'
);
insert into t1_hotel_info values
(1001, '如家酒店(北京国贸店)', '北京', 120),
(1002, '汉庭酒店(上海南京路店)', '上海', 80),
(1003, '7天酒店(广州天河店)', '广州', 100);
-- 订单信息表
create table t1_order_info (
order_id string comment '订单id',
hotel_id int comment '酒店id',
checkin_date string comment '入住日期',
checkout_date string comment '离店日期',
room_count int comment '房间数量',
status string comment '订单状态'
);
insert into t1_order_info values
('20250101001', 1001, '2025-01-01', '2025-01-03', 1, 'completed'),
('20250102001', 1001, '2025-01-02', '2025-01-04', 2, 'completed'),
('20250101002', 1002, '2025-01-01', '2025-01-02', 1, 'cancelled'),
('20250103001', 1002, '2025-01-03', '2025-01-05', 1, 'completed'),
('20250104001', 1003, '2025-01-04', '2025-01-06', 1, 'completed'),
('20250105001', 1003, '2025-01-05', '2025-01-07', 2, 'completed');
📱关注公众号
「数据仓库技术」文章同步更新，不错过每一篇干货
💬加群交流
备注「数据仓库技术」加入社群，每日一道大厂SQL真题
