题解 | #每个城市中评分最高的司机信息#

问题：请统计每个城市中评分最高的司机平均评分、日均接单量和日均行驶里程数。

• 注：有多个司机评分并列最高时，都输出。
• 平均评分和日均接单量保留1位小数，
• 日均行驶里程数保留3位小数，按日均接单数升序排序。

解题思路：

1. 先求出每个城市的司机的平均评分、日均接单量和日均行驶里程数；
2. • <有多个司机评分并列最高时，都输出> ，所以根据平均评分利用窗口函数排序，如果分数相同那么他们的排名相同
3. 如果排名都为1的话输出该条信息

with city_driver as (
    SELECT 
        city,
        driver_id,
        round(avg(grade),1) as avg_grade,
        round(count(order_time)/count(DISTINCT DATE_FORMAT(order_time,'%Y%m%d')),1) as avg_order_num,
        round(sum(mileage)/count(DISTINCT DATE_FORMAT(order_time,'%Y%m%d')),3) as avg_mileage,
        rank() over(partition by city order by round(avg(grade),1) desc) as rk 
    from tb_get_car_order join tb_get_car_record using (order_id)
    group by driver_id ,city 
)
select 
    city,
    driver_id,
    avg_grade,
    avg_order_num,
    avg_mileage
from city_driver
where rk=1
order by avg_order_num