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

select 
f.city,
f.driver_id,
format(avg_socre,1) avg_grade,
format(count(e.order_id)/count(distinct date_format(e.order_time,'%Y%m%d')),1) avg_order_num,
format(sum(e.mileage)/count(distinct date_format(e.order_time,'%Y%m%d')),3) avg_mileage
from 
tb_get_car_order e,
(
	select 
	*
	from (
		select
		*,
		rank() over(partition by city order by avg_socre desc) ranking 
		from 
		(
		    select
		    a.city,b.driver_id,avg(grade) avg_socre
		    from 
		    tb_get_car_record a,tb_get_car_order b
		    where a.order_id=b.order_id
		    group by a.city,b.driver_id
		) c
	) d
	where ranking=1
) f
where e.driver_id=f.driver_id
group by f.city,f.driver_id
order by avg_order_num asc