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

select city,driver_id,avg_grade,avg_order_num,avg_mileage from (

    select

    df2.city,

    df1.driver_id,

    round(avg(df1.grade),1) as "avg_grade",

    round(count(distinct df1.order_id)/count(distinct substr(df1.order_time,1,10)),1) as "avg_order_num",

    round(sum(mileage)/count(distinct substr(df1.order_time,1,10)),3) as "avg_mileage",

    dense_rank() over(partition by df2.city order by avg(df1.grade) desc) as "rank"

    from tb_get_car_order as df1

    left join tb_get_car_record as df2

    on df1.order_id = df2.order_id

    group by df2.city,df1.driver_id

) as df1

where df1.rank = 1

order by avg_order_num asc