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

# 思路：
# 1、将每个城市中平均得分第一名的司机信息提取出来（dense_rank）
# 2、计算平均评分、日均接单量和日均行驶里程数
select t5.city,t5.driver_id,round(avg(t5.grade),1) as avg_grade
,round(count(t5.order_time)/count(distinct day(t5.order_time)),1) as avg_order_num
,sum(t5.mileage)/count(distinct day(t5.order_time)) as avg_mileage 
from(
# 将计算需要用的额信息提取出来
select c4.city,c3.driver_id,c3.grade,c3.order_time,c3.mileage from 
tb_get_car_order as c3 left join tb_get_car_record as c4 
on c3.order_id = c4.order_id 
where (c3.driver_id,c4.city) in (
# 将每个城市中平均得分第一名的城市、司机信息查询出来
select t2.driver_id,t2.city from(
select t1.driver_id,t1.city
# 由于同分都要提取，因此用dense_rank
,dense_rank() over(partition by t1.city order by t1.avg_grade desc) as rank_grade 
from(
select distinct c1.driver_id,c2.city
,avg(c1.grade) over(partition by c1.driver_id) as avg_grade from 
tb_get_car_order as c1 left join tb_get_car_record as c2 
on c1.order_id = c2.order_id ) t1 ) t2 where t2.rank_grade = 1)) t5 
group by t5.city,t5.driver_id 
order by count(t5.order_time)/count(distinct day(t5.order_time))