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

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