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

with tmp1 as(
select
    city,
    driver_id,
    rank() over(partition by city order by avg_grade desc) rk,
    avg_grade
from(
select distinct
    city,
    driver_id,
    round(avg(grade) over(partition by driver_id,city),1) avg_grade
from tb_get_car_order t1 left join tb_get_car_record t2 on t1.order_id=t2.order_id
) a
),
tmp2 as(
select 
    driver_id,
    round(count(t1.order_id)/count(distinct date(order_time)),1) avg_order_num,
    round(sum(if(mileage is null,0,mileage))/count(distinct date(order_time)),3) avg_mileage
from tb_get_car_order t1 left join tb_get_car_record t2 on t1.order_id=t2.order_id
group by driver_id

)

select
    city,
    driver_id,
    avg_grade,
    avg_order_num,
    avg_mileage
from tmp1 left join tmp2 using(driver_id)
where rk=1
order by avg_order_num