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

# 步骤一
# select
# city,
# driver_id,
# round(avg(grade),1) as avg_grade,
# round(count(co.driver_id) / count(distinct DATE_FORMAT(order_time, '%Y-%m-%d')), 1) as avg_order_num,
# round(sum(mileage) / count(distinct DATE_FORMAT(order_time, '%Y-%m-%d')), 3) as avg_mileage
# from tb_get_car_order as co
# left join tb_get_car_record as cr using(order_id)
# group by city, driver_id

# 步骤二
# select city,
# driver_id,
# avg_grade,
# max(avg_grade) over(partition by city order by avg_grade desc) as max_grade,
# avg_order_num,
# avg_mileage
# from (select
# city,
# driver_id,
# round(avg(grade),1) as avg_grade,
# round(count(co.driver_id) / count(distinct DATE_FORMAT(order_time, '%Y-%m-%d')), 1) as avg_order_num,
# round(sum(mileage) / count(distinct DATE_FORMAT(order_time, '%Y-%m-%d')), 3) as avg_mileage
# from tb_get_car_order as co
# left join tb_get_car_record as cr using(order_id)
# group by city, driver_id
#      ) as t1

# 步骤三
select
city,
driver_id,
avg_grade,
avg_order_num,
avg_mileage
from (select city,
      driver_id,
      avg_grade,
      max(avg_grade) over(partition by city order by avg_grade desc) as max_grade,
      avg_order_num,
      avg_mileage
      from (select city, driver_id,
            round(avg(grade),1) as avg_grade,
            round(count(co.driver_id) / count(distinct DATE_FORMAT(order_time, '%Y-%m-%d')), 1) as avg_order_num,
            round(sum(mileage) / count(distinct DATE_FORMAT(order_time, '%Y-%m-%d')), 3) as avg_mileage
            from tb_get_car_order as co
            left join tb_get_car_record as cr using(order_id)
            group by city, driver_id
           ) as t1
     ) as t2
where t2.avg_grade = max_grade
order by avg_order_num;