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

select city, driver_id, avg_grade, avg_order_num, avg_mileage
from (  # 司机平均评分、日均接单量和日均行驶里程数
    select 
        city, driver_id, round(avg(grade),1) avg_grade, 
        round(count(order_id)/count(distinct date(order_time)),1) avg_order_num,
        round(sum(mileage)/count(distinct date(order_time)),3) avg_mileage,
        rank() over(partition by city order by round(avg(grade),1) DESC) ranking
    from tb_get_car_order left join tb_get_car_record using(order_id)
    group by city, driver_id
) a
where ranking=1
order by avg_order_num