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

with tb1 as(
    select city, driver_id, date(order_time) dt, mileage, grade
    from tb_get_car_order left join tb_get_car_record
    using(order_id))

select city, driver_id, a, b, c
from(
    select *,
    rank()over(partition by city order by a desc) r
    from (
        select city, driver_id, 
        round(avg(grade),1) a, 
        round(count(dt)/count(distinct dt),1) b,
        round(sum(mileage)/count(distinct dt),3) c
        from tb1
        group by city, driver_id
		) as tb2
    ) as tb3
where r=1
order by b