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

with t1 as(
    select
    city, driver_id,
    round(avg(grade),1) as avg_grade,
    round(count(1)/count(distinct date(order_time)),1) as avg_order_num,
    round(sum(mileage)/count(distinct date(order_time)),3) as avg_mileage,
    dense_rank() over(partition by city order by avg(grade) desc) as tmpr
    from tb_get_car_order left join tb_get_car_record using(order_id)
    # where tmpr=1
    group by city, driver_id
    # having tmpr=1
    # order by avg_order_num
)
select 
city, driver_id, avg_grade, avg_order_num, avg_mileage
from t1
where tmpr=1
order by avg_order_num



# SELECT city, driver_id, avg_grade, avg_order_num, avg_mileage
# FROM (
#     SELECT city, driver_id, ROUND(avg_grade, 1) as avg_grade,
#         ROUND(order_num / work_days, 1) as avg_order_num,
#         ROUND(toal_mileage / work_days, 3) as avg_mileage,
#         RANK() over(PARTITION BY city ORDER BY avg_grade DESC) as rk
#     FROM (
#         SELECT driver_id, city, AVG(grade) as avg_grade,
#             COUNT(DISTINCT DATE(order_time)) as work_days,
#             COUNT(order_time) as order_num,
#             SUM(mileage) as toal_mileage
#         FROM tb_get_car_record
#         JOIN tb_get_car_order USING(order_id)
#         GROUP BY driver_id, city
#     ) as t_driver_info
# ) as t_driver_rk
# WHERE rk = 1
# ORDER BY avg_order_num;