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

 # 每个城市 -> partition by city 
# 评分最高的司机 -> 平均评分最高
# 输出：平均评分，'日均'接单量，'日均'行驶里程数
# 可维护、审计、扩展的写法 -> with

with
    base as (
        select
            r.city,
            o.driver_id,
            o.order_id,
            o.order_time,
            o.grade,
            o.mileage,
            date(o.order_time) as dt
        from
            tb_get_car_order o
            join tb_get_car_record r on o.order_id = r.order_id
    ),
    driver_city as (
        select
            city,
            driver_id,
            avg(grade) as avg_grade,
            sum(mileage) as total_mileage,
            count(*) as order_num,
            count(distinct dt) as work_days
        from
            base
        group by
            city,
            driver_id
    ),
    metric as (
        select
        city,
        driver_id,
        avg_grade,
        total_mileage / nullif(work_days, 0) as avg_daily_mileage,
        order_num / nullif(work_days, 0) as avg_daily_order_num
        from
            driver_city
    ),
    ranked as (
        select
        city,
        driver_id,
        round(avg_grade, 1) as avg_grade,
        round(avg_daily_order_num, 1) as avg_order_num,
        round(avg_daily_mileage, 3) as avg_mileage,
        rank() over (
            partition by
                city
            order by
                avg_grade desc
        ) as rk
        from
            metric
    )
select
    city,
    driver_id,
    avg_grade,
    avg_order_num,
    avg_mileage
from
    ranked
where
    rk = 1
order by
    avg_order_num;