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

with t1 as (select city,driver_id,round(avg(grade),1) as avg_grade,count(distinct date(order_time)) as work_days,count(tgco.order_id) as order_num,sum(mileage) as total_mile from tb_get_car_record as tgcr inner join tb_get_car_order as tgco on tgco.order_id=tgcr.order_id group by driver_id,city)
select city,driver_id,avg_grade,avg_order_num,avg_mileage from (select city,driver_id,avg_grade,round(order_num/work_days,1) as avg_order_num,round(total_mile/work_days,3) as avg_mileage,rank() over (partition by city order by avg_grade desc) as r1 from t1) as t2 where r1=1 order by avg_order_num

rank：为查询结果集的每一行添加排名属性值，([OVER PARTITION BY partition_expression] ORDER BY sort_expression [ASC | DESC], [sort_expression2 [ASC | DESC], …])

根据city作为分组条件，并将avg_grade进行排序