题解 | #每个城市中评分最高的司机信息#
每个城市中评分最高的司机信息
https://www.nowcoder.com/practice/dcc4adafd0fe41b5b2fc03ad6a4ac686
select city, driver_id,
round(max(avggrade), 1)
as avg_grade,
round(count(1) / 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_order
left join (
-- 求得每个司机的平均分,并按照不同城市进行排名
select city, driver_id,
avg(grade)
as avggrade,
-- 考虑到并列第一的情况,题目未阐述,故使用dense_rank
dense_rank() over (partition by city order by avg(grade) desc )
as drk_grade
from tb_get_car_order
left join tb_get_car_record using (order_id)
group by city, driver_id
# 生成表tb_driver_rk大致如下:
# | city | driver_id | avggrade | drk_grade |
# | 北京 | 203 | 4.7500 | 1 |
# | 北京 | 202 | 4.3333 | 2 |
) tb_driver_rk using (driver_id)
-- 筛选出各城市并列第一的司机
where drk_grade = 1
group by city, driver_id
-- 根据日均接单量排序
order by avg_order_num;
查看6道真题和解析
