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

WITH t AS
(
SELECT city,driver_id,ROUND(AVG(grade),1) avg_grade,
ROUND(COUNT(*)/COUNT(DISTINCT DATE(order_time)),1) avg_order_num,
ROUND(SUM(mileage)/COUNT(DISTINCT DATE(order_time)),3) acg_mileage,
DENSE_RANK()OVER(partition by city order by AVG(grade) DESC) dr
FROM tb_get_car_record r JOIN tb_get_car_order o ON r.order_id=o.order_id
GROUP BY city,driver_id
)

SELECT city,driver_id,avg_grade,avg_order_num,acg_mileage
FROM t
WHERE dr = 1
ORDER BY avg_order_num