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

# 先分组聚合求出所有指标，再通过窗口函数rank（） 筛选评分最高的记录
select
    t1.city,
    t1.driver_id,
    t1.avg_grade,
    t1.avg_order_num,
    t1.avg_mileage
from 
       ( select
            *,  # 2.取出每个城市最高的司机平均评分、日均接单量和日均行驶里程数。
            rank() over(partition by t.city order by t.avg_grade desc) as num
        from
            (   # 1.先算出每个城市每个司机的平均评分、日均接单量和日均行驶里程数。
                select
                    r.city,
                    o.driver_id,
                    round(avg(o.grade), 1) avg_grade,
                    round(count(o.order_time) / count(distinct date(order_time)) , 1) avg_order_num,
                    round(sum(o.mileage) / count(distinct date(order_time)), 3) avg_mileage
                from
                    tb_get_car_record r
                join
                    tb_get_car_order o
                on    
                  r.order_id = o.order_id  
                group by
                    r.city, o.driver_id
            )t
        )t1
where
    t1.num = 1
order by
    t1.avg_order_num asc