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

#求每个城市中平均评分最高的司机
with t1 as(
select 
city,
driver_id,
round(avg_grade,1)
from(
select *,dense_rank() over(partition by city order by avg_grade desc) as rk
from(
select city,driver_id,
avg(grade) as avg_grade
from tb_get_car_record tr
join tb_get_car_order too
on tr.order_id = too.order_id

group by  city,driver_id
)a
)b
where rk=1)

#主查询
select city,driver_id,
round(avg(grade),1) as avg_grade,
round(count(too.order_id)/count(distinct date(order_time)),1) as avg_order_num,
round(sum(mileage)/count(distinct date(order_time)),3) as avg_mileage

from tb_get_car_record tr
join tb_get_car_order too
on tr.order_id = too.order_id

where driver_id in (select driver_id from t1)  

group by city,driver_id
order by avg_order_num