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

1.先计算所有司机的平均评分，平均订单数，平均里程

select city, driver_id,avg(grade) avg_grade,
count(order_id)/count(distinct date(order_time)) avg_order_num,
sum(mileage)/count(distinct date(order_time)) avg_mileage
from tb_get_car_order
join 
tb_get_car_record
using(order_id)
group by city,driver_id

得到下表

2.用rank() over() 窗口函数得到排名(按城市分区按平均分数降序）

select *, rank() over(partition by city order by avg_grade desc) rk from (
select city, driver_id,avg(grade) avg_grade,
count(order_id)/count(distinct date(order_time)) avg_order_num,
sum(mileage)/count(distinct date(order_time)) avg_mileage
from tb_get_car_order
join 
tb_get_car_record
using(order_id)
group by city,driver_id) t1;

得到下表

3.最后，取整，并筛选排名为1的数据，结果按照avg_order_num升序,最终的sql为

select city,driver_id,round(avg_grade,1) avg_grade ,round(avg_order_num,1) avg_order_num,
round(avg_mileage,3) avg_mileage
from
(select *, rank() over(partition by city order by avg_grade desc) rk from 
(select city, driver_id,avg(grade) avg_grade,
count(order_id)/count(distinct date(order_time)) avg_order_num,
sum(mileage)/count(distinct date(order_time)) avg_mileage
from tb_get_car_order
join 
tb_get_car_record
using(order_id)
group by city,driver_id) t1) t2
where rk =1
order by avg_order_num

得到正确结果