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

难点：每个城市中评分最高是平均评分最高，有多个司机评分并列最高时，都输出，使用窗口函数，rank（）平均评分相同时排名相同；

rank() over(partition by city order by avg(grade) desc) ranking

select city,driver_id,avg_grade,avg_order_num,avg_mileage
from 
(select city,driver_id,
 round(avg(grade),1) avg_grade,
round(count(order_id)/count(distinct date_format(order_time,'%Y-%m-%d')),1) avg_order_num,
round(sum(mileage)/count(distinct date_format(order_time,'%Y-%m-%d')),3) avg_mileage,
rank() over(partition by city order by avg(grade) desc) ranking
from tb_get_car_order tc
join tb_get_car_record tr 
using(order_id)
group by city,driver_id)t1
where ranking=1
order by avg_order_num asc
;