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

with
t1 as(
    select
        r.city,
        o.driver_id,
        o.order_id,
        date_format(o.order_time,'%Y-%m-%d') as order_dt,
        o.mileage,
        o.grade
    from tb_get_car_order o 
    left join tb_get_car_record r using(order_id)),
t2 as(
    select
        city,driver_id,
        avg(grade) avg_grade,
        count(distinct order_id)/count(distinct order_dt) avg_order_num,
        sum(mileage)/count(distinct order_dt) avg_mileage
    from t1
    group by city,driver_id),
t3 as(
    select
        *,
        dense_rank() over(partition by city order by avg_grade desc) as avg_grade_rank
    from t2)

select
    city,
    driver_id,
    round(avg_grade,1),
    round(avg_order_num,1),
    round(avg_mileage,3)
from t3
where avg_grade_rank=1
order by avg_order_num