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

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