蔚来大数据面试SQL-车辆日均行驶里程统计
⚠️ 待修正
一、题目背景
这道题来自蔚来的数据分析岗面试。蔚来是高端新能源车企，数据分析师需要从海量业务数据中挖掘洞见。
业务场景：车联网相关需求在日常工作中频繁出现，这类型的SQL题是面试高频考点。
一、题目
现有一张车辆行驶记录表 t1_drive_record，记录了每辆车每天的单次行驶信息。请统计每辆车的日均行驶里程（总里程/行驶天数），并找出日均行驶里程超过 80 公里的"高活跃度车辆"。
行驶记录表 t1_drive_record：
+----------+------------+---------------------+---------------------+-------------+
| car_id | trip_date | start_time | end_time | distance_km |
+----------+------------+---------------------+---------------------+-------------+
| C001 | 2024-01-01 | 2024-01-01 08:00:00 | 2024-01-01 09:30:00 | 45.2 |
| C001 | 2024-01-01 | 2024-01-01 14:00:00 | 2024-01-01 15:00:00 | 38.5 |
| C001 | 2024-01-02 | 2024-01-02 07:30:00 | 2024-01-02 09:00:00 | 60.0 |
| C001 | 2024-01-03 | 2024-01-03 10:00:00 | 2024-01-03 11:30:00 | 55.8 |
| C002 | 2024-01-01 | 2024-01-01 09:00:00 | 2024-01-01 11:00:00 | 90.1 |
| C002 | 2024-01-02 | 2024-01-02 08:00:00 | 2024-01-02 10:00:00 | 85.3 |
| C002 | 2024-01-03 | 2024-01-03 13:00:00 | 2024-01-03 15:30:00 | 110.0 |
| C003 | 2024-01-01 | 2024-01-01 07:00:00 | 2024-01-01 07:30:00 | 15.0 |
| C003 | 2024-01-02 | 2024-01-02 18:00:00 | 2024-01-02 18:45:00 | 22.5 |
+----------+------------+---------------------+---------------------+-------------+
三、思路分析
本题考察聚合计算，需要对每辆车分别统计总里程和行驶天数（去重日期），然后求日均里程。
解题步骤：
- 按
car_id分组，使用SUM(distance_km)统计总里程； - 使用
COUNT(DISTINCT trip_date)统计行驶天数； - 计算日均里程 = 总里程 / 行驶天数；
- 用
HAVING筛选日均里程 > 80 的高活跃度车辆。
| 维度 | 评分 |
|---|---|
| 题目难度 | ⭐️ |
| 题目清晰度 | ⭐️⭐️⭐️⭐️⭐️ |
| 业务常见度 | ⭐️⭐️⭐️⭐️⭐️ |
四、逐步推导
1. 统计每辆车的总里程和行驶天数
执行SQL
select car_id,
sum(distance_km) as total_km,
count(distinct trip_date) as drive_days
from t1_drive_record
group by car_id
执行结果
+----------+----------+------------+
| car_id | total_km | drive_days |
+----------+----------+------------+
| C001 | 199.5 | 3 |
| C002 | 285.4 | 3 |
| C003 | 37.5 | 2 |
+----------+----------+------------+
2. 计算日均里程并筛选高活跃度车辆
执行SQL
select car_id,
total_km,
drive_days,
round(total_km / drive_days, 2) as avg_daily_km
from (
select car_id,
sum(distance_km) as total_km,
count(distinct trip_date) as drive_days
from t1_drive_record
group by car_id
) t
where total_km / drive_days > 80
order by avg_daily_km desc
执行结果
+----------+----------+------------+---------------+
| car_id | total_km | drive_days | avg_daily_km |
+----------+----------+------------+---------------+
| C002 | 285.4 | 3 | 95.13 |
+----------+----------+------------+---------------+
五、常见坑点
坑1：CASE WHEN条件的顺序 — 从上到下匹配，先命中先生效。写区间时从低到高或从高到低，避免条件重叠。
坑2：占比计算的分母 — SUM() OVER() 不带 PARTITION BY 才是全局总量，加了PARTITION BY分母变成组内总量。
六、举一反三
-
动态分段：阈值存在配置表中用JOIN取值，灵活调整分段策略
-
累计分布CDF：
SUM(cnt) OVER (ORDER BY bucket)算累计占比，画帕累托图 -
按时间维度对比：按天/周/月分区，观察分布的时序变化
七、知识点总结
| 考点 | 说明 |
|---|---|
| GROUP BY + 聚合函数 | 分组聚合是数据分析的基础，配合HAVING筛选分组结果 |
| 日期函数 | DATEDIFF / DATE_ADD / unix_timestamp处理日期运算 |
八、建表语句和数据插入
点击展开 DDL & DML
-- 建表语句
CREATE TABLE t1_drive_record (
car_id string COMMENT '车辆ID',
trip_date string COMMENT '行程日期',
start_time string COMMENT '开始时间',
end_time string COMMENT '结束时间',
distance_km double COMMENT '行驶里程(km)'
) COMMENT '车辆行驶记录表';
-- 数据插入
INSERT INTO t1_drive_record VALUES
('C001', '2024-01-01', '2024-01-01 08:00:00', '2024-01-01 09:30:00', 45.2),
('C001', '2024-01-01', '2024-01-01 14:00:00', '2024-01-01 15:00:00', 38.5),
('C001', '2024-01-02', '2024-01-02 07:30:00', '2024-01-02 09:00:00', 60.0),
('C001', '2024-01-03', '2024-01-03 10:00:00', '2024-01-03 11:30:00', 55.8),
('C002', '2024-01-01', '2024-01-01 09:00:00', '2024-01-01 11:00:00', 90.1),
('C002', '2024-01-02', '2024-01-02 08:00:00', '2024-01-02 10:00:00', 85.3),
('C002', '2024-01-03', '2024-01-03 13:00:00', '2024-01-03 15:30:00', 110.0),
('C003', '2024-01-01', '2024-01-01 07:00:00', '2024-01-01 07:30:00', 15.0),
('C003', '2024-01-02', '2024-01-02 18:00:00', '2024-01-02 18:45:00', 22.5);
📱关注公众号
「数据仓库技术」文章同步更新，不错过每一篇干货
💬加群交流
备注「数据仓库技术」加入社群，每日一道大厂SQL真题
