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

select city, driver_id,
       round(max(avggrade), 1)  
           as avg_grade,
       round(count(1) / count(distinct date(order_time)), 1) 
           as avg_order_num,
       round(sum(mileage) / count(distinct date(order_time)), 3) 
           as avg_mileage
from tb_get_car_order
    left join (
        
        -- 求得每个司机的平均分,并按照不同城市进行排名
        select city, driver_id,
               avg(grade)
                   as avggrade,
               -- 考虑到并列第一的情况,题目未阐述,故使用dense_rank
               dense_rank() over (partition by city order by avg(grade) desc )
                   as drk_grade 
        from tb_get_car_order
        left join tb_get_car_record using (order_id)
        group by city, driver_id
        
        # 生成表tb_driver_rk大致如下:        
        # | city | driver_id | avggrade | drk_grade |
        # | 北京 | 203 | 4.7500 | 1 |
        # | 北京 | 202 | 4.3333 | 2 |
        
        ) tb_driver_rk using (driver_id)

-- 筛选出各城市并列第一的司机
where drk_grade = 1
group by city, driver_id
-- 根据日均接单量排序
order by avg_order_num;