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

思路:先算出每个城市各个司机的参数，最后选出排名第1的即可。
①两表连接，计算各个指标；
②用窗口函数rank排序；
③用子查询选出排名第一的数据。

with tb1 as(
    select city, driver_id, order_time, mileage, grade
    from tb_get_car_order tb2 left join tb_get_car_record tb1 using(order_id)
)

select city, driver_id, avg_grade, avg_order_num, avg_mileage
from(
select *, rank()over(partition by city order by avg_grade desc) as r
from(
select city, driver_id, 
round(avg(grade),1) avg_grade,
round(count(order_time)/count(distinct date(order_time)),1) avg_order_num,
round(sum(mileage)/count(distinct date(order_time)),3) avg_mileage
from tb1
group by city, driver_id) as tb2
) as tb3
where r=1
order by avg_order_num