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

说实话，题意描述确实是有误导性的，需要统计的是平均分评分最高的司机数据，而不是评分最高的司机。

注意点：日均接单数，日均行驶里程数。一日可能有多单，但是对该日做分母只能计为1

sum(co.mileage) / count(distinct date(co.order_time)) as avg_mileage

另一个技巧在于求平均评分最大的司机，可以可以用窗口函数rank（）对分组后的均值进行倒序排序，值为1则表示该司机平均分最高。

rank() over(partition by cr.city order by avg(co.grade) desc) rank1

注：窗口函数运行顺序在分组之后。对分组后聚合之后的数据进行处理。

select t1.city, t1.driver_id, t1.avg_grade, t1.avg_order_num, t1.avg_mileage
from (
    select cr.city,
        co.driver_id,
        round(avg(co.grade), 1) as avg_grade,
        rank() over(partition by cr.city order by avg(co.grade) desc) rank1,
        round(count(1) / count(distinct date(co.order_time)), 1) as avg_order_num,
        sum(co.mileage) / count(distinct date(co.order_time)) as avg_mileage
    from tb_get_car_order co
    inner join tb_get_car_record cr on cr.order_id=co.order_id
    group by cr.city, co.driver_id
) t1
where t1.rank1=1
order by avg_order_num asc