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

-- 思路：

• -- 1.先按城市，司机分组统计每个司机的avg_grade,avg_order_num,avg_mileage
• -- 2.然后使用dense_rank函数按照城市分组，按平均评分降序排序
• -- 3.取排序第一的记录，按照日均接单数升序

-- 思路：
--      1.先按城市，司机分组统计每个司机的avg_grade,avg_order_num,avg_mileage
--      2.然后使用dense_rank函数按照城市分组，按平均评分降序排序
--      3.取排序第一的记录，按照日均接单数升序
with
    t1 as (
        select
            r.city,
            o.driver_id,
            round(avg(grade), 1) as avg_grade,
            round(
                count(o.order_id) / count(distinct date_format (o.order_time, '%Y-%m-%d')),
                1
            ) as avg_order_num,
            round(
                sum(mileage) / count(distinct date_format (o.order_time, '%Y-%m-%d')),
                3
            ) as avg_mileage
        from
            tb_get_car_order o
            join tb_get_car_record r on o.order_id = r.order_id
        group by
            r.city,
            o.driver_id
    ),
    t2 as (
        select
            city,
            driver_id,
            avg_grade,
            avg_order_num,
            avg_mileage,
            dense_rank() over (
                partition by
                    city
                order by
                    avg_grade desc
            ) as rk
        from
            t1
    )
select
    city,
    driver_id,
    avg_grade,
    avg_order_num,
    avg_mileage
from
    t2
where
    rk = 1
order by
    avg_order_num