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

with 
    t1 as(
        select 
            tgcr.city,
            tgco.driver_id,
			#平均评分
            round(avg(tgco.grade),1)  `avg_grade`,
			#日均接单数（每个用户的总接单数/每个用户的接单天数）
            round(count(finish_time)/count(distinct date(finish_time)),1) `avg_order_num`,
			#日均行驶里程
            round(sum(mileage)/count(distinct date(finish_time)),3) `avg_mileage`,
			#关键点 --- 利用开窗将粒度粗的过滤条件加在粒度细的表中
            dense_rank() over(partition by city order by round(avg(grade),1) desc) `rk`
        from tb_get_car_record tgcr
        join tb_get_car_order tgco
        on tgcr.order_id=tgco.order_id
        group by tgcr.city,tgco.driver_id
    )
select 
    city,
    driver_id,
    avg_grade,
    avg_order_num,
    avg_mileage
from t1
where rk=1
order by avg_order_num