美团大数据面试SQL-配送准时率按月趋势
⚠️ 待修正
一、题目背景
这道题来自美团的数据分析岗面试。美团是本地生活服务（外卖、到店、酒旅），数据分析师需要从海量业务数据中挖掘洞见。
业务场景：配送效率相关需求在日常工作中频繁出现，这类型的SQL题是面试高频考点。
一、题目
现有一张配送记录表 t9_delivery_record，记录了每次配送的预计到达时间和实际到达时间。请按月份统计配送准时率的变化趋势。
准时定义：实际到达时间 ≤ 预计到达时间（即
actual_time <= estimated_time）
配送记录表 t9_delivery_record：
+----------+----------+---------------------+---------------------+
| order_id | rider_id | estimated_time | actual_time |
+----------+----------+---------------------+---------------------+
| D001 | R01 | 2023-01-15 12:30:00 | 2023-01-15 12:28:00 |
| D002 | R02 | 2023-01-15 12:30:00 | 2023-01-15 12:35:00 |
| D003 | R01 | 2023-01-16 13:00:00 | 2023-01-16 13:05:00 |
| D004 | R03 | 2023-02-10 18:00:00 | 2023-02-10 17:58:00 |
| D005 | R02 | 2023-02-11 12:00:00 | 2023-02-11 12:10:00 |
| D006 | R01 | 2023-02-12 19:00:00 | 2023-02-12 19:01:00 |
| D007 | R03 | 2023-03-05 11:30:00 | 2023-03-05 11:25:00 |
| D008 | R02 | 2023-03-06 12:00:00 | 2023-03-06 12:02:00 |
| D009 | R01 | 2023-03-07 18:00:00 | 2023-03-07 17:55:00 |
+----------+----------+---------------------+---------------------+
三、思路分析
本题考察按月聚合统计和时间比较，是配送质量监控的基础分析。
解题步骤：
- 提取月份
substr(actual_time, 1, 7)； - 判断是否准时：实际时间 ≤ 预计时间；
- 按月统计总订单数和准时订单数，计算准时率；
| 维度 | 评分 |
|---|---|
| 题目难度 | ⭐️ |
| 题目清晰度 | ⭐️⭐️⭐️⭐️⭐️ |
| 业务常见度 | ⭐️⭐️⭐️⭐️⭐️ |
四、逐步推导
按月统计准时率
执行SQL
select delivery_month,
count(1) as total_orders,
sum(case when actual_time <= estimated_time then 1 else 0 end) as ontime_orders,
round(sum(case when actual_time <= estimated_time then 1 else 0 end) / count(1), 4) as ontime_rate
from (
select order_id,
estimated_time,
actual_time,
substr(actual_time, 1, 7) as delivery_month
from t9_delivery_record
) t
group by delivery_month
order by delivery_month
执行结果
+----------------+--------------+---------------+---------------+-------------+
| delivery_month | total_orders | ontime_orders | ontime_rate |
+----------------+--------------+---------------+---------------+-------------+
| 2023-01 | 3 | 1 | 0.3333 |
| 2023-02 | 3 | 1 | 0.3333 |
| 2023-03 | 3 | 2 | 0.6667 |
+----------------+--------------+---------------+---------------+-------------+
配送准时率从1月的33%提升至3月的67%，服务质量有明显改善。
五、常见坑点
坑1：分子分母的业务定义 — 明确分子是"延迟订单数"还是"延迟用户数"，分母对应"总订单数"还是"总用户数"，口径不同结果差异大。
坑2：时间差计算的单位 — unix_timestamp 返回秒，除以60才是分钟。忘记单位转换会导致判断完全错误。
六、举一反三
-
按骑手/站点/城市维度下钻：定位问题来源，是某个骑手还是某个区域的延迟率偏高
-
按延迟程度分级：CASE WHEN 分为轻微/中等/严重三级，关注严重延迟的占比
-
时段分析：按小时统计，识别午晚高峰时段的配送压力变化
七、知识点总结
| 考点 | 说明 |
|---|---|
| CASE WHEN 条件聚合 | 灵活实现分段统计、条件计数、标签化处理 |
| GROUP BY + 聚合函数 | 分组聚合是数据分析的基础，配合HAVING筛选分组结果 |
| 日期函数 | DATEDIFF / DATE_ADD / unix_timestamp处理日期运算 |
八、建表语句和数据插入
点击展开 DDL & DML
-- 建表语句
CREATE TABLE t9_delivery_record (
order_id string COMMENT '订单ID',
rider_id string COMMENT '骑手ID',
estimated_time string COMMENT '预计到达时间',
actual_time string COMMENT '实际到达时间'
) COMMENT '配送记录表';
-- 数据插入
INSERT INTO t9_delivery_record VALUES
('D001', 'R01', '2023-01-15 12:30:00', '2023-01-15 12:28:00'),
('D002', 'R02', '2023-01-15 12:30:00', '2023-01-15 12:35:00'),
('D003', 'R01', '2023-01-16 13:00:00', '2023-01-16 13:05:00'),
('D004', 'R03', '2023-02-10 18:00:00', '2023-02-10 17:58:00'),
('D005', 'R02', '2023-02-11 12:00:00', '2023-02-11 12:10:00'),
('D006', 'R01', '2023-02-12 19:00:00', '2023-02-12 19:01:00'),
('D007', 'R03', '2023-03-05 11:30:00', '2023-03-05 11:25:00'),
('D008', 'R02', '2023-03-06 12:00:00', '2023-03-06 12:02:00'),
('D009', 'R01', '2023-03-07 18:00:00', '2023-03-07 17:55:00');
📱关注公众号
「数据仓库技术」文章同步更新，不错过每一篇干货
💬加群交流
备注「数据仓库技术」加入社群，每日一道大厂SQL真题
