力扣-Mysql-3308- 寻找表现最佳的司机（中等）

一、题目来源

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）。

三、需求

优步正在基于司机的行程分析他们的情况。编写一个解决方案，根据下列标准来找到 每种动力类型 中 表现最好的司机：

1. 一个司机的表现由他们行程的 平均评分 计算。平均评分应该舍入到 2 位小数。
2. 如果两个司机有相同的平均评分，里程数更多 的司机评分更高。
3. 如果 依旧持平，选择 事故数最少 的司机。

返回结果表以 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 (
    SELECT
        fuel_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 rk
    FROM
        drivers d
            JOIN vehicles v ON d.driver_id = v.driver_id
            JOIN trips t ON v.vehicle_id = t.vehicle_id
    group by fuel_type,v.driver_id
)
SELECT
    fuel_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 进行升序排序。