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

with t as 
(	-- 2、将各城市司机根据当前城市排名dense_rank
    select city,driver_id,dense_rank()over(partition by city order by avg_grade desc) dr from 
    (
	  	-- 1、求出各城市司机的平均评分
        select city,driver_id,avg(grade) avg_grade
        from tb_get_car_record r join tb_get_car_order o on r.order_id=o.order_id
        group by city,driver_id
    ) t1
)
select city,driver_id,round(avg(grade),1) avg_grade,				-- 6、计算相关指标
round(count(1)/count(distinct date(order_time)),1) avg_order_num,
round(sum(mileage)/count(distinct date(order_time)),3) avg_mileage
from tb_get_car_record r join tb_get_car_order o on r.order_id=o.order_id -- 3、所有订单记录
-- 4、所有订单记录中满足不同城市中司机平均分排名第一的（city,driver_id）
where (city,driver_id) in (select city,driver_id from t where dr=1) 
group by city,driver_id -- 5、根据（city,driver_id）分组
order by avg_order_num		-- 7、排序

步骤结果：

1、求出各城市司机的平均评分

2、将各城市司机根据当前城市排名dense_rank

3-7