# 思路:先分组聚合求出所有指标,再通过窗口函数rank() 筛选评分最高的记录
SELECT city,driver_id,avg_grade,avg_order_num,avg_mileage
from (
select *
,rank()over(partition by city order by avg_grade desc) posn
from (
select city,driver_id
,round(avg(grade),1) avg_grade
,round(count(order_time)/count(distinct date(order_time)),1) avg_order_num
,sum(mileage)/count(distinct date(order_time)) avg_mileage
from tb_get_car_record tgcr
inner join tb_get_car_order tgco
on tgcr.order_id=tgco.order_id
group by city,driver_id
) a
) b
where posn=1
order by avg_order_num