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

写的比较艰难，也不知道是不是最好的方法。

1.取出需要的字段

select b.city,a.driver_id,a.grade,a.mileage
from tb_get_car_order a left join tb_get_car_record b on a.order_id=b.order_id
where a.grade is not null

2.在上一步的基础上，分组group by+avg ,求出城市+司机+平均评分

select city,driver_id,avg(grade) avg_grade
	from
	(
	select b.city,a.driver_id,a.grade,a.mileage
	from tb_get_car_order a left join tb_get_car_record b on a.order_id=b.order_id
		 where a.grade is not null)a
	group by city,driver_id
	order by city,driver_id

3.在上一步的基础上，然后dense_rank排序，筛选出每个城市排名第一的司机

select driver_id
from(select city,driver_id,avg_grade,row_number()over(partition by city order by avg_grade desc) rk
from(select city,driver_id,avg(grade) avg_grade
	from
	(
	select b.city,a.driver_id,a.grade,a.mileage
	from tb_get_car_order a left join tb_get_car_record b on a.order_id=b.order_id
		 where a.grade is not null)a
	group by city,driver_id
	order by city,driver_id)a)a
where rk=1

4.group by +order by，结合sum,count求出日均订单量、日均里程数等值 完整代码如下：

select b.city,a.driver_id,
round(avg(a.grade),1),
round(count(distinct a.order_id)/count(distinct date(a.order_time)),1) avg_order_num,
round(sum(a.mileage)/count(distinct date(a.order_time)),3)

from tb_get_car_order a left join tb_get_car_record b on a.order_id=b.order_id

where driver_id in(
select driver_id
from(select city,driver_id,avg_grade,dense_rank()over(partition by city order by avg_grade desc) rk
from(select city,driver_id,avg(grade) avg_grade
	from
	(
	select b.city,a.driver_id,a.grade,a.mileage
	from tb_get_car_order a left join tb_get_car_record b on a.order_id=b.order_id
		 where a.grade is not null)a
	group by city,driver_id
	order by city,driver_id)a)a
where rk=1
)
group by b.city,a.driver_id
order by avg_order_num