携程大数据面试SQL-酒店入住率统计
⚠️ 待修正
一、题目背景
这道题来自携程的数据分析岗面试。携程是在线旅游平台，数据分析师需要从海量业务数据中挖掘洞见。
业务场景：旅游出行相关需求在日常工作中频繁出现，这类型的SQL题是面试高频考点。
一、题目
在携程平台上，我们需要统计各个酒店在2025年1月份的入住率。给定两张表：hotel_info（酒店信息表）和 order_info（订单信息表）。
hotel_info 酒店信息表：
| hotel_id | hotel_name | city | total_rooms |
|---|---|---|---|
| 1001 | 如家酒店(北京国贸店) | 北京 | 120 |
| 1002 | 汉庭酒店(上海南京路店) | 上海 | 80 |
| 1003 | 7天酒店(广州天河店) | 广州 | 100 |
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 |
要求：统计每个酒店在2025年1月份的入住率。入住率 = 实际入住的房间数 /（总房间数 × 当月天数），结果保留两位小数。只统计 status = 'completed' 的订单。按入住率降序排列。
三、思路分析
本题是典型的聚合统计问题，考察 COUNT、GROUP BY、SUM 以及多表 JOIN 的能力。核心难点在于理解"入住率"的计算逻辑——需要按照每个订单实际覆盖的日期范围来计算入住的"房间·天"数。
| 维度 | 评分 |
|---|---|
| 题目难度 | ⭐ |
| 题目清晰度 | ⭐⭐⭐⭐⭐ |
| 业务常见度 | ⭐⭐⭐⭐⭐ |
解题思路：
- 通过
JOIN将订单表和酒店信息表关联 - 只保留
status = 'completed'且在2025年1月有入住的订单（checkin_date 和 checkout_date 与1月有交集） - 计算每个订单在1月份实际占用的"房间·天"数：
DATEDIFF(LEAST(checkout_date, '2025-01-31'), GREATEST(checkin_date, '2025-01-01'))× room_count - 按 hotel_id 汇总所有已入住订单的房间·天总和
- 酒店总可用房间·天 = total_rooms × 31（1月共31天）
- 入住率 = 已入住房间·天 / 总可用房间·天
四、逐步推导
步骤1：计算每个酒店在1月份的已入住房间·天数
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 hotel_info h
JOIN 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 | 5 |
| 1002 | 汉庭酒店(上海南京路店) | 上海 | 80 | 2 |
| 1003 | 7天酒店(广州天河店) | 广州 | 100 | 4 |
步骤2：计算入住率并排序
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 hotel_info h
JOIN 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 * 100.0 / (total_rooms * 31), 2) AS occupancy_rate_pct
FROM occupancy
ORDER BY occupancy_rate_pct DESC;
执行结果：
| hotel_id | hotel_name | city | occupied_room_days | total_available_room_days | occupancy_rate_pct |
|---|---|---|---|---|---|
| 1003 | 7天酒店(广州天河店) | 广州 | 4 | 3100 | 0.13 |
| 1001 | 如家酒店(北京国贸店) | 北京 | 5 | 3720 | 0.13 |
| 1002 | 汉庭酒店(上海南京路店) | 上海 | 2 | 2480 | 0.08 |
五、常见坑点
坑1：CASE WHEN条件的顺序 — 从上到下匹配，先命中先生效。写区间时从低到高或从高到低，避免条件重叠。
坑2：占比计算的分母 — SUM() OVER() 不带 PARTITION BY 才是全局总量，加了PARTITION BY分母变成组内总量。
六、举一反三
-
按骑手/站点/城市维度下钻：定位问题来源，是某个骑手还是某个区域的延迟率偏高
-
按延迟程度分级：CASE WHEN 分为轻微/中等/严重三级，关注严重延迟的占比
-
时段分析：按小时统计，识别午晚高峰时段的配送压力变化
七、知识点总结
| 考点 | 说明 |
|---|---|
| 多表JOIN | LEFT JOIN保留主表所有数据，COALESCE处理NULL |
| GROUP BY + 聚合函数 | 分组聚合是数据分析的基础，配合HAVING筛选分组结果 |
| 日期函数 | DATEDIFF / DATE_ADD / unix_timestamp处理日期运算 |
八、建表语句和数据插入
点击展开 DDL & DML
-- 酒店信息表
CREATE TABLE hotel_info (
hotel_id INT PRIMARY KEY,
hotel_name VARCHAR(100),
city VARCHAR(50),
total_rooms INT
);
INSERT INTO hotel_info VALUES
(1001, '如家酒店(北京国贸店)', '北京', 120),
(1002, '汉庭酒店(上海南京路店)', '上海', 80),
(1003, '7天酒店(广州天河店)', '广州', 100);
-- 订单信息表
CREATE TABLE order_info (
order_id VARCHAR(20) PRIMARY KEY,
hotel_id INT,
checkin_date DATE,
checkout_date DATE,
room_count INT,
status VARCHAR(20)
);
INSERT INTO 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真题
