题解 | #每个城市中评分最高的司机信息#

WITH t1 AS ( SELECT a.uid, driver_id, date( order_time ) AS order_time, mileage, grade, city FROM tb_get_car_order a LEFT JOIN tb_get_car_record b USING ( order_id ) ), t2 AS ( SELECT RANK() over ( PARTITION BY city ORDER BY round( sum( grade )/ count( grade ), 1 ) DESC ) AS rank_num, city, driver_id, round( sum( grade )/ count( grade ), 1 ) AS avg_grade, ROUND( count( order_time )/ count( DISTINCT order_time ), 1 ) AS avg_order_num, ROUND( sum( mileage )/ count( DISTINCT order_time ), 3 ) AS avg_mileage FROM t1 GROUP BY driver_id, city ) SELECT city, driver_id,avg_grade,avg_order_num,avg_mileage FROM t2 WHERE rank_num = 1 ORDER BY avg_order_num