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

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
;

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 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

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

【分类】：嵌套子查询、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, 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 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 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 t1 as (select city, driver_id, round(avg(grade), 1) as avg_grade, round(count(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 as d inner join tb_get_car_record as u using(order_id)
group by city, driver_id)

select * 
from t1
group by city, driver_id
having avg_grade in (select max(avg_grade) from t1 group by city)
order by avg_order_num

with a as
(select  city,driver_id,avg_grade,count(*) as order_cnt,
count(DISTINCT DATE_FORMAT(order_time,'%Y%m%d')) as days,
sum(mileage) as total_mileage
from tb_get_car_order co
right join 

(
(select city,driver_id,avg_grade
from 
(select driver_id,city,avg(grade) as avg_grade,
dense_rank() over (partition by city order by avg(grade) asc) as ranking
from tb_get_car_order join tb_get_car_record using(order_id)
where fare is not null
group by driver_id,city)
as t1
where ranking = 1)
) 

as t2 using(driver_id)
group by city,driver_id)


select city,driver_id,round(avg_grade,1) as avg_grade,
round(order_cnt/days,1) as  avg_order_num,
round(total_mileage/days,3) as avg_mileage
from a
order by avg_order_num asc

with 
t as (  #总表
select city, order_time, driver_id as did, mileage, grade
from tb_get_car_record join tb_get_car_order using(order_id)
),
t1 as (  #司机平均分表
select city, did, avg(grade) as avg from t
group by city, did
), 
t2 as ( #由t1得到每个城市最高分
select city, max(avg) as max from t1
group by city
),
t3 as ( #由t1和t2得到最高评分司机
select did, avg from t1 inner join t2 using(city)
where avg=max 
),
t4 as ( #司机的日均接单和里程数
select did, 
    count(order_time)/count(distinct date(order_time)) as avg_n,
    sum(mileage)/count(distinct date(order_time)) as avg_m
from t
group by did
)

select city, did, round(avg(avg), 1), 
    round(avg(avg_n), 1), round(avg(avg_m), 3) 
from t3 
    inner join t using(did) 
    inner join t4 using(did)
group by city, did
order by round(avg(avg_n), 1);