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

SELECT t1.city, t1.driver_id, t1.avg_grade, t1.avg_order_num, t1.avg_mileage
FROM(
SELECT tbr.city, tbo.driver_id, ROUND(AVG(tbo.grade), 1) AS avg_grade,
DENSE_RANK() OVER(PARTITION BY tbr.city ORDER BY AVG(tbo.grade) DESC) as rn,
ROUND(COUNT(*) / COUNT(DISTINCT(DATE(tbo.order_time))), 1) AS avg_order_num,
ROUND(SUM(tbo.mileage) / COUNT(DISTINCT(DATE(tbo.order_time))), 3) AS avg_mileage
FROM tb_get_car_order AS tbo
INNER JOIN tb_get_car_record AS tbr
ON tbo.order_id = tbr.order_id
GROUP BY tbr.city, tbo.driver_id) AS t1
WHERE t1.rn = 1
ORDER BY t1.avg_order_num;