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

select city,driver_id,avg_grade,avg_order_num,avg_mileage
from (select *,max(avg_grade) over (partition by city) max_grade
from (select city,driver_id,round(avg(grade),1) avg_grade,
round(count(1)/count(distinct date(order_time)),1) avg_order_num,
round(sum(mileage)/count(distinct date(order_time)),3) avg_mileage
from tb_get_car_record a 
left join tb_get_car_order b 
on a.uid = b.uid and a.order_id = b.order_id
group by city,driver_id) u ) v 
where avg_grade = max_grade
order by avg_order_num ASC