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

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

SELECT 
    city,
    o.driver_id,
    ROUND(AVG(grade),1) AS avg_grade,
    ROUND(COUNT(order_time) / COUNT(DISTINCT IF(start_time IS NULL, NULL, DATE(start_time))),1) AS avg_order_num,
    ROUND(SUM(IFNULL(mileage, 0)) / COUNT(DISTINCT IF(start_time IS NULL, NULL, DATE(start_time))),3) AS avg_mileage
FROM tb_get_car_order o 
JOIN tb_get_car_record r ON o.order_id = r.order_id
WHERE o.driver_id IN 
    (
    -- 评分排名第一的司机
    SELECT 
    driver_id
    FROM (
        SELECT 
            o.driver_id,
            r.city,
            AVG(grade) AS avg_grade,
            DENSE_RANK() OVER (PARTITION BY r.city ORDER BY AVG(grade) DESC) AS rk
        FROM tb_get_car_order o 
        JOIN tb_get_car_record r ON o.order_id = r.order_id
        WHERE grade IS NOT NULL
        GROUP BY r.city, o.driver_id
        ) a 
    WHERE rk = 1
    )
GROUP BY city, o.driver_id
ORDER BY avg_order_num ASC

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

为什么这段代码提交会报错
select t2.city,t2.driver_id
,round(sum(grade)/count(grade),1)avg_grade
,round(count(*)/d_cnt,1) avg_order_num
,round(sum(mileage)/d_cnt,3) avg_mileage
from(
select *,
dense_rank() over(partition by city order by avg_gd desc) rk
from(
select city,driver_id,grade,mileage
,round(sum(grade) over(partition by driver_id)/count(grade) over(partition by driver_id),1) avg_gd
from tb_get_car_order
join tb_get_car_record using(order_id)
) t1 )t2
join (select driver_id,city,count(distinct date_format(start_time,'%Y-%m-%d')) d_cnt from tb_get_car_order
join tb_get_car_record using(order_id) 
group by city,driver_id) t3 on t3.driver_id=t2.driver_id and t3.city=t2.city
where rk=1
group by 1,2
order by 3

select t1.city, t1.driver_id, t1.avg_grade,
round(t1.order_num/count(distinct day(t2.order_time)),1) as avg_order_num,
round(t1.sum_mileage/count(distinct day(t2.order_time)),3) as avg_mileage
from
(select city, driver_id, r1.avg_grade, r1.order_num, 
r1.sum_mileage 
from
(select city, driver_id, round(avg(grade),1) as avg_grade, 
count(*) as order_num, sum(mileage) as sum_mileage, rank() over(partition by city order by avg(grade) desc) as rk
from tb_get_car_order, tb_get_car_record
where tb_get_car_order.order_id=tb_get_car_record.order_id
group by driver_id, city)r1
where rk=1
)t1 join tb_get_car_order t2
on t1.driver_id=t2.driver_id
group by t1.city, t1.driver_id, t1.avg_grade
order by avg_order_num

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

WITH t1 AS (
SELECT driver_id,ROUND(AVG(grade),1) avg_grade,city FROM  tb_get_car_order tbo JOIN tb_get_car_record tbr USING(order_id) GROUP BY driver_id,city
)
#再根据每个城市的最高评分定位到司机id，然后根据司机id计算其他值
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
FROM tb_get_car_order tbo JOIN tb_get_car_record tbr USING(order_id) GROUP BY city,driver_id HAVING (city,avg_grade) IN (
SELECT city,MAX(avg_grade) avg_grade FROM t1 group by city
) ORDER BY avg_order_num;

with 
    tmp as (
        select city
            ,driver_id
            ,round(avg(grade),1) avg_grade
            ,round(count(order_time)/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 
        join tb_get_car_order
        using(order_id)
        group by city
            ,driver_id
    )
select *
from tmp
where avg_grade in (
    select max(avg_grade)
    from tmp
    group by city
)
order by avg_order_num

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