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

1.求每个城市每个司机的三个平均数

select
city,
driver_id,
round(avg(grade), 1) as avg_grade,
round(count(a.order_time) / count(distinct date(order_time)), 1 ) as avg_order_num,
round(sum(mileage) / count(distinct date(order_time)), 3) as avg_mileage
from
tb_get_car_order a
left join tb_get_car_record b 
on a.order_id = b.order_id  and a.uid = b.uid
group by city, driver_id；

2.根据平均得分按城市分组进行排序

rank() over (partition by city order by avg_grade desc) as rn

3.筛选平均得分最高的，rn=1,按照日均订单数排序

select
  city,
  driver_id,
  avg_grade,
  avg_order_num,
  avg_mileage
from
  (
    select
      *,
      rank() over (partition by city order by avg_grade desc) as rn
    from
      (
        select
          city,
          driver_id,
          round(avg(grade), 1) as avg_grade,
          round(count(a.order_time) / count(distinct date(order_time)),1) as avg_order_num,
          round(sum(mileage) / count(distinct date(order_time)), 3) as avg_mileage
        from
          tb_get_car_order a
          left join tb_get_car_record b on a.order_id = b.order_id
          and a.uid = b.uid
        group by
          city,
          driver_id
      ) as t1
  ) as t2
where rn = 1
order by avg_order_num；