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

题目

请统计每个城市中评分最高的司机平均评分、日均接单量和日均行驶里程数。

注：有多个司机评分并列最高时，都输出。 平均评分和日均接单量保留1位小数， 日均行驶里程数保留3位小数，按日均接单数升序排序。

select city,driver_id,avgscore,round(cnt/day,1),mile/day avg_mileage
from(select city,driver_id,
             round(avg(grade),1) as avgscore,
            dense_rank() over(partition by city order by avg(grade) desc) t_rank,
             count(finish_time) cnt,
             count(distinct date(finish_time)) day, 
             sum(mileage) mile
        from tb_get_car_record join tb_get_car_order using(order_id)
        group by city,driver_id) a
where t_rank=1
order by city desc,avg_mileage asc