【场景】：输出最大值并列

【分类】：嵌套子查询、rank() ... ranking = 1

分析思路

难点：

1.最大值并列时如何输出并列的司机信息：使用窗口函数，并列排序

（1）统计每个城市中司机的平均评分、日均接单量和日均行驶里程数

  • [使用]：分组查询

（2）对每个城市中司机按照平均评分降序排序

  • [使用]：窗口函数 rank() 和 dense_rank()都可以并列排序

（3）统计每个城市中评分最高的司机平均评分、日均接单量和日均行驶里程数。按日均接单数升序排序

并列排序之后取 rank = 1的数据，最后按照日均接单数升序排序

  • [条件]：where ranking = 1

  • [使用]：order by avg_order_num

求解代码

方法一

with子句

with
    main as(
        #统计每个城市中司机的平均评分、日均接单量和日均行驶里程数
        select
            city,
            driver_id,
            round(avg(grade),1) as grade_avg,
            round(count(distinct 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
        join tb_get_car_order using(order_id)
        group by city,driver_id
    )
    ,main1 as(
        #对每个城市中司机按照平均评分降序排序
        select
            city,
            driver_id,
            grade_avg,
            avg_order_num,
            avg_mileage,
            rank() over (partition by city order by grade_avg desc) as ranking
        from main
    )

#统计每个城市中评分最高的司机平均评分、日均接单量和日均行驶里程数。按日均接单数升序排序
select
    city,
    driver_id,
    grade_avg,
    avg_order_num,
    avg_mileage
from main1
where ranking = 1
order by avg_order_num

方法二

from嵌套子查询

#统计每个城市中评分最高的司机平均评分、日均接单量和日均行驶里程数。按日均接单数升序排序
select
    city,
    driver_id,
    grade_avg,
    avg_order_num,
    avg_mileage
from(
    #对每个城市中司机按照平均评分降序排序
    select
        city,
        driver_id,
        grade_avg,
        avg_order_num,
        avg_mileage,
        rank() over (partition by city order by grade_avg desc) as ranking
    from(
        #统计每个城市中司机的平均评分、日均接单量和日均行驶里程数
        select
            city,
            driver_id,
            round(avg(grade),1) as grade_avg,
            round(count(distinct 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
        join tb_get_car_order using(order_id)
        group by city,driver_id
    ) main
) main1
where ranking = 1
order by avg_order_num