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

# 目标输出city,driver_id,avg_grade,avg_order_num,avg_mileage
with t as(
select  
    r.city,o.driver_id,
    round(avg(o.grade),1) as avg_grade,
    round(count(o.order_id)/count(distinct date(o.order_time)),1) as avg_order_num,
    round(sum(o.mileage)/count(distinct date(o.order_time)),3) as avg_mileage,
    rank()over(partition by r.city order by round(avg(o.grade),1) desc) as rk
from 
    tb_get_car_order as o left join tb_get_car_record as r on o.order_id=r.order_id
group by r.city,o.driver_id)
select t.city,t.driver_id,t.avg_grade,t.avg_order_num,t.avg_mileage from t where rk=1
order by t.avg_order_num

开始把时间理解成了timestampdiff(day,min(o.order_time),max(o.ordr_time))。。。