题解 | #每个城市中评分最高的司机信息#
每个城市中评分最高的司机信息
https://www.nowcoder.com/practice/dcc4adafd0fe41b5b2fc03ad6a4ac686
注意：1.要用order_time 而不是start_time,2.接单是否取消都不管的
with a as (select t1.driver_id,t1.order_id,t1.mileage,t1.grade,t1.start_time,t2.city
from(select driver_id,order_id,mileage,grade,substr(order_time,1,10) start_time
from tb_get_car_order
)t1
left join
(select city,order_id
from tb_get_car_record)t2
on t1.order_id=t2.order_id)
select city,t1.driver_id,round(avg_grade,1),round(avg_times,1) avg_times,round(avg_mil ,3)
from
(select city,driver_id,avg_grade
,rank() over (partition by city order by avg_grade desc) num
from
(select city,driver_id,avg(grade) avg_grade
from a
group by city,driver_id)aa)t1
left join
(select driver_id,sum(mileage)/count(distinct start_time) avg_mil
,count(order_id)/count(distinct start_time) avg_times
from a
group by driver_id)t2
on t1.driver_id=t2.driver_id
where num=1
order by avg_times
