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

select 
    city,
    driver_id,
    round(avg_grade,1),
    round(avg_order_num,1),
    round(avg_mileage,3)
from(
    select 
        *,
        rank() over (partition by city order by avg_grade desc) as rk
    from(
        select
            city,
            driver_id,
            avg(grade) as avg_grade,
            max(cnt)/count(distinct dt) as avg_order_num,
            sum(mileage)/count(distinct dt) as avg_mileage
        from(
            select 
                city,
                driver_id,
                grade,
                mileage,
                date(order_time) as dt,
                count(*) over(partition by driver_id) as cnt
            from 
                tb_get_car_order as a
            left join tb_get_car_record
            using(order_id)
        ) as t
        group by 1,2) as t2) as t3
where rk=1
order by avg_order_num asc