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

/*
统计每个城市中评分最高的司机
平均评分  (总评分/总评分个数)
日均接单量（总单数/总天数）
日均行驶里程数（总里程数/总天数）
city	driver_id	avg_grade	avg_order_num	avg_mileage
注：有多个司机评分并列最高时，都输出。
平均评分和日均接单量保留1位小数，
日均行驶里程数保留3位小数，按日均接单数升序排序
*/
#1.先计算出每个城市所有司机的平均评分，日均接单量，日均行驶里程数，
#2.再筛选出平均评分最高的那一位司机的信息
select 
      city,
      driver_id,
      avg_grade,
      avg_order_num,
      avg_mileage
  from
      (select 
             city,
             driver_id,
             round(avg(grade),1) as avg_grade,
             round(count(order_id) / count(distinct date(order_time)),1) as avg_order_num,
             round(sum(mileage) / count(distinct date(order_time)),3) as avg_mileage,
             dense_rank() over(partition by city order by round(avg(grade),1) desc ) as dr
         from tb_get_car_order a join tb_get_car_record b using (order_id)
         group by city,driver_id
      )a
 where dr = 1
 order by avg_order_num