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

select city,driver_id,avg_grade,avg_order_num,avg_mileage
from (
    select *,
    dense_rank() over(partition by city order by avg_grade desc) as rk
from(
    select city,
        driver_id,
        round(avg(grade),1) as avg_grade,
        round(count(driver_id)/count(distinct date_format(order_time,'%m-%d')),1) as avg_order_num,
        round(sum(mileage)/count(distinct date_format(order_time,'%m-%d')),3) as avg_mileage
    from tb_get_car_order o
    left join tb_get_car_record r on o.order_id=r.order_id
    group by city,driver_id
    ) a
) b
where rk='1'
order by avg_order_num