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

#不需要什么思路，应该都懂，纯纯为了打个卡。只需要注意平均计算评分的平均值的时候：评分值不为空的记录 才能计算进去，和上题保持一致

select
    d.city,d.driver_id,d.avg_grade,d.avg_order_num,d.avg_mileage
from 
(
    select

        c.city,
        c.driver_id,
        format(c.allGrades/c.gradeNums,1) as avg_grade,
        format(c.orderNum/c.days,1) as avg_order_num,
        format(c.miles/c.days,3) as avg_mileage,
        dense_rank() over(partition by c.city order by c.allGrades/c.gradeNums desc ) as rk
    from 
    (

        select

            a.driver_id,
            max(b.city) as city,
            count(1) as orderNum,
            count(distinct date_format(a.order_time,'%Y-%m-%d')) as days,
            sum(case when a.start_time is not null and a.finish_time is not null and a.grade is not null then 1 else 0 end) as gradeNums,
            sum(a.grade) as allGrades,
            sum(a.mileage) as miles
        from tb_get_car_order a join tb_get_car_record b on a.order_id = b.order_id
        group by a.driver_id
    ) c
)d where d.rk = 1
order by d.avg_order_num