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

select city,driver_id,avg_grade,num avg_order_num,avg_mileage from(
select city,driver_id,round(avg(grade),1) avg_grade,round(count(1)/count(distinct order_date),1) num,sum(mileage)/count(distinct order_date) avg_mileage,dense_rank()over(partition by city order by avg(grade) desc) rk
from tb_get_car_record tgcr
inner join 
(select *,date(order_time) order_date from tb_get_car_order tgco) as t1
on tgcr.uid = t1.uid and tgcr.order_id = t1.order_id
group by city,driver_id) as t1
where rk = 1
order by avg_order_num

窗口函数进行排名