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

select city,driver_id,avg_grade,avg_order_num,avg_mileage from
(select r.city,o.driver_id,
round(avg(o.grade),1) avg_grade,
round(count(o.order_id)/(count(distinct date(order_time))),1) avg_order_num,
round(sum(o.mileage)/(count(distinct date(order_time))),3) avg_mileage,
dense_rank() over (partition by r.city order by round(avg(o.grade),1) desc) t_rank
from tb_get_car_order o 
inner join tb_get_car_record r on r.order_id=o.order_id
group by r.city,o.driver_id) as a
where t_rank = 1
order by avg_order_num

with t as(
    SELECT
        city,
        driver_id,
        round(avg(grade),1) avg_grade,
        round(count(finish_time)/count(distinct date_format(finish_time,'%Y%m%d')),1) avg_order_num,
        sum(mileage)/count(distinct date_format(finish_time,'%Y%m%d')) avg_mileage,
        rank() over(partition by city order by round(avg(grade),1) desc) city_rank
    FROM tb_get_car_order co
    LEFT JOIN tb_get_car_record cr
    ON co.order_id=cr.order_id
    GROUP BY city,driver_id
)
select city,driver_id,avg_grade,avg_order_num,avg_mileage
from t
where city_rank=1
order by city desc,avg_mileage asc
;

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

【分类】：嵌套子查询、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

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

with driver_info as(
    select tgcr.city,tgco.*
    from tb_get_car_order tgco
    join tb_get_car_record tgcr
    on  tgco.order_id=tgcr.order_id
)
,
ans_to_be_refined as(
select city,driver_id,
ROUND(avg(grade),1) as avg_grade,
ROUND(count(*)/count(distinct date_format(order_time,"%Y-%m-%d")),1) as avg_order_num,
ROUND(sum(mileage)/count(distinct date_format(order_time,"%Y-%m-%d")),3) as avg_mileage
from driver_info
group by city,driver_id
order by avg_order_num asc
)

select a.* from ans_to_be_refined  a
join (select city,max(avg_grade) as max_avg_grade from ans_to_be_refined group by city) t1
on t1.city=a.city  and t1.max_avg_grade=a.avg_grade

select city, driver_id, avg_grade, avg_order_num, avg_mileage
from (
select city, driver_id, avg_grade, avg_order_num, avg_mileage, 
    dense_rank() over(partition by city order by avg_grade desc) as rnk
from (
select city, driver_id, 
    round(avg(grade), 1) as avg_grade, 
    round(count(a.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_order a left join tb_get_car_record b 
on a.order_id = b.order_id
group by city, driver_id
    ) t1
        ) t2
where rnk = 1
order by avg_order_num

select city,driver_id,avg_grade,cnt_order,cnt_mile
from 
    (select 
        b.city,a.driver_id
        ,round(avg(grade),1) as avg_grade
        ,round(count(a.order_id)/count(distinct substring(order_time,1,10)),1) as cnt_order
        ,round(sum(mileage)/count(distinct substring(order_time,1,10)),3) as cnt_mile
        ,dense_rank()over(partition by b.city order by avg(grade) desc) as rank2
    from tb_get_car_order a 
    left join tb_get_car_record b on a.order_id=b.order_id
    group by b.city,a.driver_id) t 
where rank2=1
order by cnt_order

select c,d,g,o,m
from(select city c,driver_id d
    ,round(avg(grade),1) g
    ,round(count(order_time)/count(distinct date(order_time)),1) o
    ,round(sum(mileage)/count(distinct date(order_time)),3) m
    ,rank()over(partition by city order by avg(grade) desc) p
    from tb_get_car_order join tb_get_car_record using(order_id)
    group by 1,2) a
where p=1
order by 4;

with t as (
    select
        city,
        driver_id,
        round(avg(grade),1) avg_grade,
        round(count(gco.order_id) / count(distinct date(order_time)),1) avg_order_num,
        round(sum(mileage) / count(distinct date(order_time)),3) avg_mileage,
        rank() over(partition by city order by avg(grade) desc) ranking
    from tb_get_car_record gcr
    join tb_get_car_order gco 
    on gcr.order_id = gco.order_id
    group by city,driver_id
)
select
    city,
    driver_id,
    avg_grade,
    avg_order_num,
    avg_mileage
from t 
where ranking = 1
order by avg_order_num;

select
city,
driver_id,
round(avg_grade,1),
round(avg_order_num,1),
round(avg_mile_num,3)

from

(select r.city, o.driver_id, avg(grade) as avg_grade, 
count(o.order_id)/count(distinct date_format(order_time,"%Y%-%m-%d")) as avg_order_num,
sum(o.mileage)/count(distinct date_format(order_time,"%Y%-%m-%d")) as avg_mile_num,
rank()over(partition by city order by avg(grade) desc) as grade_rank

from tb_get_car_record as r join tb_get_car_order as o
on r.order_id = o.order_id


group by city, driver_id) as t 

where grade_rank = 1 #这个graderank因为是窗口函数搞出来的，不能直接套在里面那层用，必须放在外层
order by avg_order_num asc

select city,driver_id,avg_grade,
       round(order_cnt/date_cnt,1) avg_order_num,
       round(sum_mileage/date_cnt,3) 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) avg_grade,
       count(distinct date_format(order_time,'%Y-%m-%d')) date_cnt,
       count(*) order_cnt,
       sum(mileage) sum_mileage
from tb_get_car_record t_rec,
     tb_get_car_order t_ord 
where t_rec.order_id=t_ord.order_id 
group by city,driver_id
)a
)b
where rk=1
order by avg_order_num

select tb1.city,tb1.driver_id,round(avg_grade,1) as avg_grade,
round(count(tb2.order_time)/count(distinct date(tb2.order_time)),1) as avg_order_num,
round(sum(ifnull(tb2.mileage,0))/count(distinct date(tb2.order_time)),3) as avg_mileage
from
(
select city,driver_id,avg(grade) as avg_grade,
rank() over(partition by city order by avg(grade) desc) as ranking
from
(select city,order_id from tb_get_car_record) as cr
inner join tb_get_car_order as co 
on cr.order_id=co.order_id
where grade is not null
group by city,driver_id
) as tb1 #每个城市司机的平均分，并进行排序
inner join tb_get_car_order as tb2
on tb1.driver_id=tb2.driver_id
where ranking=1 #多个司机评分并列最高时，都输出
group by city,driver_id
order by avg_order_num;

select city ,driver_id,a,b,c
from (select city,t2.driver_id,round(avg(grade),1) A,
round(count(order_time)/count(distinct date(order_time)),1) b,
round(sum(mileage)/count(distinct date(order_time)),3) c,
rank() over(partition by city order by 
                  round(count(order_time)/count(distinct date(order_time)),1) desc
                 ) rn
from tb_get_car_record t1
inner join tb_get_car_order t2
on t1.uid=t2.uid
and t1.order_id=t2.order_id
group by city,t2.driver_id)t
where rn=1

order by b

with t as (select 
       a.city,
       b.driver_id,
       round(avg(b.grade),1) as avg_grade,
      round(count(b.order_id)/count(distinct date(b.order_time)),1) as avg_order_num,
       round(sum(b.mileage)/count(distinct date(b.order_time)),3) as avg_mileage
from tb_get_car_order as b
left join tb_get_car_record as a on a.order_id = b.order_id 
group by 1,2)


select city,driver_id,avg_grade ,avg_order_num,avg_mileage 
from (
select *,
      dense_rank() over (partition by city order by avg_grade desc ) as rn
from t) as c
where rn = 1
order by avg_order_num

with a as
(
    
        select 
        city ,
        t2.driver_id driver_id,
        round(avg(t2.grade),1) avg_grade,
        round(count( t2.order_id)/count(distinct date(order_time)),1) avg_order_num,
        round(sum(mileage)/count(distinct date(order_time)),3) avg_mileage

        from tb_get_car_record t1
        join tb_get_car_order  t2
        on t1.order_id=t2.order_id
        group by 1,2
    
)
select 
    city,
    driver_id,
    avg_grade,
    avg_order_num,
    avg_mileage
from
(
    select
    city,
    driver_id,
    avg_grade,
    avg_order_num,
    avg_mileage,
    rank() over(partition by city order by avg_grade desc) rn
    from a
)b
where rn=1
order by avg_order_num

with driver_info
as
    (select 
    city,
    driver_id,
    round(avg(grade),1) avg_grade,
    round(count(tb1.order_id)/count(distinct left(order_time,10)),1) avg_order_num,
    round(sum(mileage)/count(distinct left(order_time,10)),3) avg_mileage
    from tb_get_car_order tb1 
    left join tb_get_car_record tb2 
    on tb1.order_id = tb2.order_id
    group by city,driver_id )

select
city,
driver_id,
avg_grade,
avg_order_num,
avg_mileage
from
    (select 
    city,
    driver_id,
    avg_grade,
    avg_order_num,
    avg_mileage,
    rank()over(partition by city order by avg_grade desc) rk
    from driver_info) a
where rk = 1
order by avg_order_num