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

with
    t1 as (
        select
            tb_get_car_record.city,
            tb_get_car_order.driver_id,
            round(avg(grade), 1) as avg_grade,
            round(count(order_time), 1) as order_num,
            round(sum(mileage), 1) as mileage,
            rank() over (
                partition by
                    tb_get_car_record.city
                order by
                    avg(grade) desc
            ) as ranking
        from
            tb_get_car_order
            left join tb_get_car_record using (order_id)
        group by
            tb_get_car_record.city,
            tb_get_car_order.driver_id
    )
select
    t1.city,
    t1.driver_id,
    t1.avg_grade,
    round(t1.order_num / count(distinct date (tb_get_car_order.order_time)),1) as avg_order_num,
    t1.mileage / count(distinct date (tb_get_car_order.order_time))
from
    t1
    left join tb_get_car_order using (driver_id)
where
    ranking = 1
group by
    t1.city,
    t1.driver_id,
    t1.avg_grade
order by  t1.avg_grade asc;