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

# 思路：先分组聚合求出所有指标，再通过窗口函数rank（） 筛选评分最高的记录
SELECT city,driver_id,avg_grade,avg_order_num,avg_mileage
from (
  select *
  ,rank()over(partition by city order by avg_grade desc) posn
  from (
    select city,driver_id
    ,round(avg(grade),1) avg_grade
    ,round(count(order_time)/count(distinct date(order_time)),1) avg_order_num
    ,sum(mileage)/count(distinct date(order_time)) avg_mileage
    from tb_get_car_record tgcr
    inner join tb_get_car_order tgco
    on tgcr.order_id=tgco.order_id
    group by city,driver_id
  ) a
) b
where posn=1
order by avg_order_num