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         |
+---------+-------------+----------------------+----------------------+--------------+

二、思路分析

本题考察聚合计算，需要对每辆车分别统计总里程和行驶天数（去重日期），然后求日均里程。

解题步骤：

1. 按 car_id 分组，使用 SUM(distance_km) 统计总里程；
2. 使用 COUNT(DISTINCT trip_date) 统计行驶天数；
3. 计算日均里程 = 总里程 / 行驶天数；
4. 用 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  |
+---------+-----------+-------------+
| C003    | 37.5      | 2           |
| C001    | 199.5     | 3           |
| C002    | 285.4     | 3           |
+---------+-----------+-------------+
3 rows selected (1.297 seconds)(https://www.dwsql.com)

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 row selected (0.662 seconds)(https://www.dwsql.com)

四、常见坑点

坑1：COUNT(DISTINCT trip_date) vs COUNT(*) — 同一辆车在同一天可能有多次行程记录。计算日均里程时，天数必须使用 COUNT(DISTINCT trip_date) 去重，而不能用 COUNT(*)，否则分母偏大会导致日均里程偏低。

坑2：HAVING vs WHERE — 聚合后的筛选条件需使用 HAVING（如 HAVING total_km / drive_days > 80）。但此处使用子查询 + WHERE 更为清晰，可读性更好，也是推荐写法。

坑3：整数除法的精度问题 — total_km / drive_days 若两个字段均为整数类型，Spark SQL 会执行整数除法导致小数被截断。建议使用 ROUND(total_km * 1.0 / drive_days, 2) 或保证分子分母至少有一个为浮点类型。

五、知识点总结

考点	说明
SUM + GROUP BY	按 car_id 分组，SUM(distance_km) 统计每辆车的总行驶里程
COUNT(DISTINCT)	去重统计行驶天数，避免同一天多次行程导致分母偏大
ROUND 函数	ROUND(total_km / drive_days, 2) 保留两位小数，提升结果可读性
HAVING vs WHERE	聚合后筛选用 HAVING；子查询中用 WHERE 更清晰直观

六、建表语句和数据插入

点击展开 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真题