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

select 
    city,
    driver_id,
    avg_grade,
    avg_order_num,
    avg_mileage
from 
(select 
    city,
    driver_id,
    round(avg(grade),1) as avg_grade,
    round(count(o.order_id)/count(distinct date(order_time)),1) as avg_order_num,
    round(sum(mileage)/count(distinct date(order_time)),3) as avg_mileage,
    rank()over(partition by city order by avg(grade) desc) as rk
from 
    tb_get_car_order o left join tb_get_car_record r using(order_id)
group by 
    city,
    driver_id
) t
where rk = 1
order by avg_order_num