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

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

注意：rank()over(partition by city order by avg(grade) desc) as rk，需要avg(grade) ，并不能直接写‘avg_grade'