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

select city,driver_id,avg_grade,avg_order_num,avg_mileage
from
    (
    select city,
    driver_id,
    round(avg(grade),1) avg_grade,
    max(round(avg(grade),1)) over(partition by city) max_grade,
    round(count(*)/count(distinct date(order_time)),1) avg_order_num,
    sum(mileage)/count(distinct date(order_time)) avg_mileage
    from
        tb_get_car_record join tb_get_car_order using(order_id)
    group by city,driver_id
)t
where avg_grade = max_grade
order by avg_order_num

内层先聚合查询出每个司机的聚合数据，并添加一个城市最大平均评分数据max(round(avg(grade),1)) over(partition by city) max_grade

外层根据司机的平均评分和最大评分，判断是否输出