一、题目来源
3308. 寻找表现最佳的司机 - 力扣（LeetCode）
二、数据表结构
表：
Drivers+--------------+---------+ | Column Name | Type | +--------------+---------+ | driver_id | int | | name | varchar | | age | int | | experience | int | | accidents | int | +--------------+---------+ (driver_id) 是这张表的唯一主键。 每一行包含一个司机 ID，他们的名字，年龄，驾龄年数，以及事故数。表：
Vehicles+--------------+---------+ | vehicle_id | int | | driver_id | int | | model | varchar | | fuel_type | varchar | | mileage | int | +--------------+---------+ (vehicle_id, driver_id, fuel_type) 是这张表的唯一主键。 每一行包含机动车 ID，驾驶员，车型，动力类型和里程数。表：
Trips+--------------+---------+ | trip_id | int | | vehicle_id | int | | distance | int | | duration | int | | rating | int | +--------------+---------+ (trip_id) 是这张表的唯一主键。 每一行包含行程 ID，使用的机动车，覆盖的距离（以米计），行程市场（以分钟计），以及乘客评分（1-5）。
三、需求
优步正在基于司机的行程分析他们的情况。编写一个解决方案，根据下列标准来找到 每种动力类型 中 表现最好的司机：
- 一个司机的表现由他们行程的 平均评分 计算。平均评分应该舍入到
2位小数。 - 如果两个司机有相同的平均评分，里程数更多 的司机评分更高。
- 如果 依旧持平，选择 事故数最少 的司机。
返回结果表以 fuel_type 升序 排序。
四、示例数据
输入：
Drivers表：+-----------+----------+-----+------------+-----------+ | driver_id | name | age | experience | accidents | +-----------+----------+-----+------------+-----------+ | 1 | Alice | 34 | 10 | 1 | | 2 | Bob | 45 | 20 | 3 | | 3 | Charlie | 28 | 5 | 0 | +-----------+----------+-----+------------+-----------+
Vehicles表：+------------+-----------+---------+-----------+---------+ | vehicle_id | driver_id | model | fuel_type | mileage | +------------+-----------+---------+-----------+---------+ | 100 | 1 | Sedan | Gasoline | 20000 | | 101 | 2 | SUV | Electric | 30000 | | 102 | 3 | Coupe | Gasoline | 15000 | +------------+-----------+---------+-----------+---------+
Trips表：+---------+------------+----------+----------+--------+ | trip_id | vehicle_id | distance | duration | rating | +---------+------------+----------+----------+--------+ | 201 | 100 | 50 | 30 | 5 | | 202 | 100 | 30 | 20 | 4 | | 203 | 101 | 100 | 60 | 4 | | 204 | 101 | 80 | 50 | 5 | | 205 | 102 | 40 | 30 | 5 | | 206 | 102 | 60 | 40 | 5 | +---------+------------+----------+----------+--------+输出：
+-----------+-----------+--------+----------+ | fuel_type | driver_id | rating | distance | +-----------+-----------+--------+----------+ | Electric | 2 | 4.50 | 180 | | Gasoline | 3 | 5.00 | 100 | +-----------+-----------+--------+----------+
解释：
- 对于动力类型
Gasoline，Alice（司机 1）和 Charlie（司机 3）有行程。Charlie 平均评分为 5.0，而 Alice 为 4.5。因此，选择 Charlie。 - 对于动力类型
Electric，Bob（司机 2）是唯一的司机，评分为 4.5，因此选择他。
输出表以 fuel_type 升序排序。
五、分析
1.文字分析
第一步：首先进行三表关联查询，获取动力类型fuel_type 、driver_id、平均评分avg(rating)以及总距离sum(distance)；
第二步：根据需求，按照fuel_type进行分组，按照平均评分avg_rating的降序，总里程数的降序以及事故数的升序进行排名 ；
第三步：根据需求选择排名为1的数据，并按照动力类型fuel_type进行升序排序。
2.图解
六、代码实现
with t1 AS (SELECTfuel_type,v.driver_id,ROUND(AVG(rating), 2) AS rating,SUM(distance) AS distance,rank() over(partition by fuel_type order by AVG(rating) desc,SUM(mileage) desc,sum(accidents)) as rkFROMdrivers dJOIN vehicles v ON d.driver_id = v.driver_idJOIN trips t ON v.vehicle_id = t.vehicle_idgroup by fuel_type,v.driver_id
)
SELECTfuel_type,driver_id,rating,distance
from t1
where rk = 1
ORDER BY fuel_type;
七、总结
本题需求为寻找表现最佳的司机：
对三个表进行三表联查，然后根据需求求出平均评分 avg_rating、总里程 sum_meliage 和事故数 sum_accidents，然后根据动力类型 fuel_type 进行分组，对平均评分的降序(avg_rating desc)、总里程的降序(sum_meliage desc )以及事故数的升序(sum_accidents asc)对数据进行排名，使用 rank() 函数即可，然后获取排名为1的数据，即为所求，最终对数据按照 动力类型 fuel_type 进行升序排序。
