滴滴大数据面试SQL-司机完单率统计
一、题目
已知有表t1_order_detail记录了滴滴平台司机的订单明细，包含订单ID、司机ID、订单状态（completed/cancelled）和订单时间。请统计每位司机的完单率，完单率 = 完成订单数 / 总接单数，并找出完单率低于80%的司机。
样例数据
+-----------+-----------+-----------+----------------------+
| order_id | driver_id | status | order_time |
+-----------+-----------+-----------+----------------------+
| 1001 | d001 | completed | 2024-06-01 08:00:00 |
| 1002 | d001 | completed | 2024-06-01 09:00:00 |
| 1003 | d001 | cancelled | 2024-06-01 10:00:00 |
| 1004 | d001 | completed | 2024-06-01 11:00:00 |
| 1005 | d002 | completed | 2024-06-01 08:30:00 |
| 1006 | d002 | cancelled | 2024-06-01 09:30:00 |
| 1007 | d002 | cancelled | 2024-06-01 10:30:00 |
| 1008 | d003 | completed | 2024-06-01 08:00:00 |
| 1009 | d003 | completed | 2024-06-01 09:00:00 |
| 1010 | d003 | completed | 2024-06-01 10:00:00 |
+-----------+-----------+-----------+----------------------+
三、思路分析
本题考察条件聚合能力，核心是将"完成订单数"和"总订单数"分别统计，然后做除法。关键在于理解两种写法：
- CASE WHEN + SUM：利用SUM(CASE WHEN status='completed' THEN 1 ELSE 0 END)对每个司机分别计算完成单数与总单数。这种写法直观、好理解。
- COUNT DISTINCT 问题：注意这里用COUNT而不是COUNT DISTINCT，因为每个订单ID本身就是唯一的，计数即可。如果订单表存在重复数据，需先对order_id去重再统计。
在滴滴实际业务中，完单率是衡量司机接单质量的核心指标，通常需要按城市、时段等多维度下钻。
| 维度 | 评分 |
|---|---|
| 题目难度 | ⭐️⭐️ |
| 题目清晰度 | ⭐️⭐️⭐️⭐️ |
| 业务常见度 | ⭐️⭐️⭐️⭐️⭐️ |
四、逐步推导
1.计算每位司机的完单率
首先分别统计每位司机的总订单数和完成订单数，然后计算完单率。这里使用CASE WHEN进行条件计数，用ROUND保留两位小数方便查看。
执行SQL
select
driver_id,
count(*) as total_orders,
sum(case when status = 'completed' then 1 else 0 end) as completed_orders,
round(
sum(case when status = 'completed' then 1 else 0 end) * 100.0 / count(*),
2
) as completion_rate
from t1_order_detail
group by driver_id
执行结果
+-----------+---------------+-------------------+-----------------+
| driver_id | total_orders | completed_orders | completion_rate |
+-----------+---------------+-------------------+-----------------+
| d001 | 4 | 3 | 75.00 |
| d002 | 3 | 1 | 33.33 |
| d003 | 3 | 3 | 100.00 |
+-----------+---------------+-------------------+-----------------+
2.筛选完单率低于80%的司机
在上一步基础上，使用HAVING子句筛选完单率低于80%的司机。也可以在子查询或CTE中用WHERE过滤，但直接用HAVING更简洁。
执行SQL
select
driver_id,
count(*) as total_orders,
sum(case when status = 'completed' then 1 else 0 end) as completed_orders,
round(
sum(case when status = 'completed' then 1 else 0 end) * 100.0 / count(*),
2
) as completion_rate
from t1_order_detail
group by driver_id
having round(
sum(case when status = 'completed' then 1 else 0 end) * 100.0 / count(*),
2
) < 80
执行结果
+-----------+---------------+-------------------+-----------------+
| driver_id | total_orders | completed_orders | completion_rate |
+-----------+---------------+-------------------+-----------------+
| d001 | 4 | 3 | 75.00 |
| d002 | 3 | 1 | 33.33 |
+-----------+---------------+-------------------+-----------------+
五、常见坑点
坑1：CASE WHEN条件的顺序 — 从上到下匹配，先命中先生效。写区间时从低到高或从高到低，避免条件重叠。
坑2：占比计算的分母 — SUM() OVER() 不带 PARTITION BY 才是全局总量，加了PARTITION BY分母变成组内总量。
六、举一反三
-
按骑手/站点/城市维度下钻：定位问题来源，是某个骑手还是某个区域的延迟率偏高
-
按延迟程度分级：CASE WHEN 分为轻微/中等/严重三级，关注严重延迟的占比
-
时段分析：按小时统计，识别午晚高峰时段的配送压力变化
七、知识点总结
| 考点 | 说明 |
|---|---|
| CASE WHEN 条件聚合 | 灵活实现分段统计、条件计数、标签化处理 |
| COUNT DISTINCT 去重 | 统计唯一用户/事件数，避免重复记录干扰聚合结果 |
| GROUP BY + 聚合函数 | 分组聚合是数据分析的基础，配合HAVING筛选分组结果 |
八、建表语句和数据插入
点击展开 DDL & DML
--建表语句
CREATE TABLE IF NOT EXISTS t1_order_detail (
order_id BIGINT COMMENT '订单ID',
driver_id STRING COMMENT '司机ID',
status STRING COMMENT '订单状态: completed-完成, cancelled-取消',
order_time STRING COMMENT '订单时间'
);
--数据插入
INSERT INTO t1_order_detail(order_id, driver_id, status, order_time) VALUES
(1001, 'd001', 'completed', '2024-06-01 08:00:00'),
(1002, 'd001', 'completed', '2024-06-01 09:00:00'),
(1003, 'd001', 'cancelled', '2024-06-01 10:00:00'),
(1004, 'd001', 'completed', '2024-06-01 11:00:00'),
(1005, 'd002', 'completed', '2024-06-01 08:30:00'),
(1006, 'd002', 'cancelled', '2024-06-01 09:30:00'),
(1007, 'd002', 'cancelled', '2024-06-01 10:30:00'),
(1008, 'd003', 'completed', '2024-06-01 08:00:00'),
(1009, 'd003', 'completed', '2024-06-01 09:00:00'),
(1010, 'd003', 'completed', '2024-06-01 10:00:00');
📱关注公众号
「数据仓库技术」文章同步更新，不错过每一篇干货
💬加群交流
备注「数据仓库技术」加入社群，每日一道大厂SQL真题
