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

# 9：50 - 
# 字段：city，driver_id，avg_grade，avg_order_num，avg_mileage
# 最高评分并列输出  dense_rank?

# tb1：链接
with tb1 as(
    select city,driver_id,
    # 平均评分 ? 直接用avg能否筛选掉null？  不行的话得用sum/count(前者可行)
    round(avg(grade),1) as avg_grade,
    # 注意这里求的是日均数据
    round(count(date(order_time))/count(distinct date(order_time)),1) as avg_order_num,
    round(sum(mileage)/count(distinct date(order_time)),3) as avg_mileage
    from tb_get_car_order left join tb_get_car_record using(order_id)
    group by city,driver_id
),

# tb2：输出每个城市排序第一(可以并列)
tb2 as(
    select *,dense_rank()over(partition by city order by avg_grade desc) as rk
    from tb1
)

select city,driver_id,avg_grade,avg_order_num,avg_mileage
from tb2
where rk = 1
order by avg_order_num asc

# where dense_rank()over(partition by city order by avg_grade desc)=1
# 注意点：
# 排序窗口函数不可以直接在where中使用 ； 正确解法：在一个新的子查询中别名rk，在where中用rk筛选~