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

1. 先对每个城市的司机获取统计信息

select city,
        driver_id, 
        round(avg(grade),1) as avg_grade,
        round(count(order_id)/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_record
        inner join tb_get_car_order
        using(order_id)
        GROUP BY city, driver_id

2. 利用窗口函数对每个城市的司机统计信息按照评价分排名 (注：这里使用dense_rank及rank都可)

     select *,
        rank() over(partition by city ORDER by avg_grade DESC) as rk
        from (
        select city,
        driver_id, 
        round(avg(grade),1) as avg_grade,
        round(count(order_id)/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_record
        inner join tb_get_car_order
        using(order_id)
        GROUP BY city, driver_id
    	) temp 

3. 找到每个城市评分最高司机的信息并按照日均接单量升序

select city,
    driver_id,
    avg_grade,
    avg_order_num,
    avg_mileage 
    from (
        select *,
        rank() over(partition by city ORDER by avg_grade DESC) as rk
        from (
        select city,
        driver_id, 
        round(avg(grade),1) as avg_grade,
        round(count(order_id)/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_record
        inner join tb_get_car_order
        using(order_id)
        GROUP BY city, driver_id
    ) temp 
) temp2 
    where rk = 1 
    order by avg_order_num