美团大数据面试SQL-外卖配送延迟率统计
⚠️ 待修正
一、题目背景
这道题来自美团外卖配送部门的数据分析岗面试。配送延迟率是外卖业务最核心的服务质量指标——超时送达直接影响用户体验和复购率。美团每天处理数千万笔外卖订单，配送延迟率每波动0.1个百分点，都可能意味着上万用户的体验受损。
业务场景：配送运营团队每天盯延迟率看板，按月份、城市、商圈维度下钻分析。如果某区域延迟率持续走高，需要立即调整骑手调度策略。这道SQL就是延迟率报表的基础查询。
二、题目
现有一张外卖配送表 t2_delivery_order，记录了每笔外卖订单的预计送达时间和实际送达时间。请统计每月的配送延迟率。
延迟定义：实际送达时间 > 预计送达时间（以分钟计，超过0分钟即为延迟）
外卖配送表 t2_delivery_order：
+----------+----------+---------------------+---------------------+
| order_id | rider_id | estimated_arrive | actual_arrive |
+----------+----------+---------------------+---------------------+
| D001 | R01 | 2023-03-01 12:30:00 | 2023-03-01 12:35:00 |
| D002 | R02 | 2023-03-01 12:30:00 | 2023-03-01 12:28:00 |
| D003 | R01 | 2023-03-01 13:00:00 | 2023-03-01 13:10:00 |
| D004 | R03 | 2023-03-02 11:30:00 | 2023-03-02 11:25:00 |
| D005 | R02 | 2023-03-02 12:00:00 | 2023-03-02 12:15:00 |
| D006 | R01 | 2023-03-03 18:00:00 | 2023-03-03 18:02:00 |
| D007 | R03 | 2023-03-03 18:30:00 | 2023-03-03 18:45:00 |
| D008 | R02 | 2023-03-03 19:00:00 | 2023-03-03 18:58:00 |
+----------+----------+---------------------+---------------------+
三、思路分析
外卖配送延迟率是衡量配送服务质量的核心指标。
解题步骤：
- 提取月份
substr(actual_arrive, 1, 7)； - 使用
unix_timestamp计算送达时间差（秒→分）； - 判断是否延迟，按月统计延迟订单数和总订单数；
- 计算延迟率 = 延迟订单数 / 总订单数；
| 维度 | 评分 |
|---|---|
| 题目难度 | ⭐️⭐️ |
| 题目清晰度 | ⭐️⭐️⭐️⭐️⭐️ |
| 业务常见度 | ⭐️⭐️⭐️⭐️⭐️ |
四、逐步推导
1. 计算每单的送达延迟分钟数
执行SQL
select order_id,
rider_id,
estimated_arrive,
actual_arrive,
substr(actual_arrive, 1, 7) as delivery_month,
(unix_timestamp(actual_arrive) - unix_timestamp(estimated_arrive)) / 60 as delay_minutes
from t2_delivery_order
执行结果
+----------+----------+---------------------+---------------------+----------------+---------------+
| order_id | rider_id | estimated_arrive | actual_arrive | delivery_month | delay_minutes |
+----------+----------+---------------------+---------------------+----------------+---------------+
| D001 | R01 | 2023-03-01 12:30:00 | 2023-03-01 12:35:00 | 2023-03 | 5.0 |
| D002 | R02 | 2023-03-01 12:30:00 | 2023-03-01 12:28:00 | 2023-03 | -2.0 |
| D003 | R01 | 2023-03-01 13:00:00 | 2023-03-01 13:10:00 | 2023-03 | 10.0 |
| D004 | R03 | 2023-03-02 11:30:00 | 2023-03-02 11:25:00 | 2023-03 | -5.0 |
| D005 | R02 | 2023-03-02 12:00:00 | 2023-03-02 12:15:00 | 2023-03 | 15.0 |
| D006 | R01 | 2023-03-03 18:00:00 | 2023-03-03 18:02:00 | 2023-03 | 2.0 |
| D007 | R03 | 2023-03-03 18:30:00 | 2023-03-03 18:45:00 | 2023-03 | 15.0 |
| D008 | R02 | 2023-03-03 19:00:00 | 2023-03-03 18:58:00 | 2023-03 | -2.0 |
+----------+----------+---------------------+---------------------+----------------+---------------+
2. 按月统计延迟率
执行SQL
select delivery_month,
count(1) as total_orders,
sum(case when delay_minutes > 0 then 1 else 0 end) as delay_orders,
round(sum(case when delay_minutes > 0 then 1 else 0 end) / count(1), 4) as delay_rate
from (
select order_id,
(unix_timestamp(actual_arrive) - unix_timestamp(estimated_arrive)) / 60 as delay_minutes,
substr(actual_arrive, 1, 7) as delivery_month
from t2_delivery_order
) t
group by delivery_month
order by delivery_month
执行结果
+----------------+--------------+--------------+------------+
| delivery_month | total_orders | delay_orders | delay_rate |
+----------------+--------------+--------------+------------+
| 2023-03 | 8 | 5 | 0.6250 |
+----------------+--------------+--------------+------------+
3月配送延迟率为62.5%，超过半数订单延迟，需要重点关注配送效率。
五、常见坑点
坑1：时间差计算的精度
unix_timestamp() 返回秒级时间戳，两个时间相减得到的是秒数，除以60才是分钟。如果忘记除以60直接用秒数判断延迟，所有订单都会被误判为"延迟"。
坑2：预计和实际时间的顺序
如果数据采集中实际送达时间早于预计送达时间（提前送达），unix_timestamp(actual) - unix_timestamp(estimated) 为负数，CASE WHEN > 0 不会误判。但要注意负数不要影响平均值计算。
坑3：延迟率统计的时间范围
如果只统计有 actual_arrive 的订单（已完成配送），会忽略"骑手取餐后超时未送达被取消"的订单，导致延迟率偏乐观。应确认数据范围是否包含取消/异常订单。
六、举一反三
- 按骑手/站点维度：GROUP BY 加上 rider_id 或 station_id，定位延迟率最高的骑手或配送站点
- 按延迟程度分级：CASE WHEN 分为"准时(<=0)、轻微延迟(1-10分钟)、严重延迟(>10分钟)"三级统计
- 时段分析：按小时统计延迟率，识别高峰时段（午高峰11:00-13:00、晚高峰17:00-19:00）的配送压力
- 天气关联：关联天气数据，分析暴雨/高温等极端天气对延迟率的影响
七、知识点总结
| 考点 | 说明 |
|---|---|
| unix_timestamp 时间差 | 转为秒数后相减再除以60得分钟差 |
| CASE WHEN 延迟判断 | 实际时间 > 预计时间 → 延迟标志 |
| GROUP BY 月份聚合 | substr取年月前缀，按月汇总 |
| COUNT + CASE WHEN | 条件聚合同时统计总数和延迟数 |
八、建表语句和数据插入
点击展开 DDL & DML
CREATE TABLE t2_delivery_order (
order_id string COMMENT '订单ID',
rider_id string COMMENT '骑手ID',
estimated_arrive string COMMENT '预计送达时间',
actual_arrive string COMMENT '实际送达时间'
) COMMENT '外卖配送表';
-- 数据插入
INSERT INTO t2_delivery_order VALUES
('D001', 'R01', '2023-03-01 12:30:00', '2023-03-01 12:35:00'),
('D002', 'R02', '2023-03-01 12:30:00', '2023-03-01 12:28:00'),
('D003', 'R01', '2023-03-01 13:00:00', '2023-03-01 13:10:00'),
('D004', 'R03', '2023-03-02 11:30:00', '2023-03-02 11:25:00'),
('D005', 'R02', '2023-03-02 12:00:00', '2023-03-02 12:15:00'),
('D006', 'R01', '2023-03-03 18:00:00', '2023-03-03 18:02:00'),
('D007', 'R03', '2023-03-03 18:30:00', '2023-03-03 18:45:00'),
('D008', 'R02', '2023-03-03 19:00:00', '2023-03-03 18:58:00');
📱关注公众号
「数据仓库技术」文章同步更新，不错过每一篇干货
💬加群交流
备注「数据仓库技术」加入社群，每日一道大厂SQL真题
