Mysql 解法, 执行用时: 37ms, 内存消耗: 6376KB, 提交时间: 2022-01-25

# 问题：请统计每个城市中评分最高的司机平均评分、日均接单量和日均行驶里程数。

# 注：有多个司机评分并列最高时，都输出。
#        平均评分和日均接单量保留1位小数，
#        日均行驶里程数保留3位小数，按日均接单数升序排序。
       
       
with c as(
SELECT driver_id
FROM(
    SELECT *,rank() over(partition by city order by avg_grade desc) as r
    FROM(
        SELECT city,driver_id,avg(grade) as avg_grade
        FROM tb_get_car_order tbgco
        JOIN tb_get_car_record tbgcr USING(order_id)
        GROUP BY city,driver_id) as a) as b
WHERE r = 1)

SELECT city,tbgco.driver_id,
       round(avg(grade),1) as avg_grade,
       round(count(order_time) / count(distinct DATE(order_time)),1) as avg_order_num,
       sum(mileage) / count(distinct DATE(order_time)) as avg_mileage
FROM tb_get_car_order tbgco
JOIN tb_get_car_record tbgcr USING(order_id)
WHERE tbgco.driver_id in (select * from c)
GROUP BY city,tbgco.driver_id
order by avg_order_num

Mysql 解法, 执行用时: 37ms, 内存消耗: 6384KB, 提交时间: 2021-12-27

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 rank_grade
from
(select city,driver_id,round(avg(grade),1)avg_grade,
round(count(ord.order_id)/count(distinct date_format(order_time,"%Y-%m-%d")),1)avg_order_num,
round(sum(mileage)/count(distinct date_format(order_time,"%Y-%m-%d")),3)avg_mileage
from tb_get_car_order ord
left join tb_get_car_record re
on ord.order_id=re.order_id
group by city,driver_id)a )b
where rank_grade=1
order by avg_order_num;

Mysql 解法, 执行用时: 37ms, 内存消耗: 6384KB, 提交时间: 2021-12-06

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

Mysql 解法, 执行用时: 37ms, 内存消耗: 6408KB, 提交时间: 2022-01-22

SELECT city, driver_id, avg_grade, avg_order_num, avg_mileage
FROM (
    SELECT city, driver_id, ROUND(avg_grade, 1) as avg_grade,
        ROUND(order_num / work_days, 1) as avg_order_num,
        ROUND(toal_mileage / work_days, 3) as avg_mileage,
        RANK() over(PARTITION BY city ORDER BY avg_grade DESC) as rk
    FROM (
        SELECT driver_id, city, AVG(grade) as avg_grade,
            COUNT(DISTINCT DATE(order_time)) as work_days,
            COUNT(order_time) as order_num,
            SUM(mileage) as toal_mileage
        FROM tb_get_car_record
        JOIN tb_get_car_order USING(order_id)
        GROUP BY driver_id, city
    ) as t_driver_info
) as t_driver_rk
WHERE rk = 1
ORDER BY avg_order_num;

Mysql 解法, 执行用时: 37ms, 内存消耗: 6412KB, 提交时间: 2021-12-19

select 
t.city,
t.driver_id,
t.avg_grade,
t.avg_order_num,
t.avg_mileage
from
(select 
r.city as city,
o.driver_id as driver_id,
round(avg(o.grade) ,1) as avg_grade,
round(count(*)/count(distinct date(finish_time)),1)as avg_order_num,
round(sum(o.mileage)/count(distinct date(finish_time)),3)as avg_mileage,
rank() over (partition by r.city order by round(avg(o.grade) ,1) desc) as ranking
from tb_get_car_order o
join tb_get_car_record r using(order_id)
group by r.city, o.driver_id) as t
where t.ranking=1
order by t.avg_order_num