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

# 求每个城市中评分最高的司机的三个指标
# 评分是指平均分

# 按城市分组求司机的评分均值，取最大值对应的司机
# 针对这些司机求平均评分、日均接单量和日均行驶里程数
# 未完成的接单也算作接单数；
# 只有接单成功的次数，才用于计算平均评分

# 临时表，用于查询每个城市的每个司机的聚合信息
WITH temp_0 AS(
	# 查询城市、司机id、接单成功的总订单数、平均评分、开窗函数求每个城市的最大平均评分、跑单的天数（要去重）、订单总数（不管接没接成）、里程总数
    SELECT city, driver_id, COUNT(fare) order_grade, ROUND(SUM(grade)/COUNT(fare), 1) avg_grade, MAX(ROUND(SUM(grade)/COUNT(fare), 1)) OVER(PARTITION BY city) max_avg_grade, COUNT(DISTINCT DATE(finish_time)) dt, COUNT(*) order_cnt, SUM(mileage) mileage_all
    FROM tb_get_car_record a
    JOIN tb_get_car_order b USING(order_id)
    GROUP BY city, driver_id
    )

# 主查询，查询目标信息
SELECT city, driver_id, avg_grade, ROUND(order_cnt / dt, 1) avg_order_num, ROUND(mileage_all / dt, 3) avg_mileage
FROM temp_0
# 使用临时表，找到满足平均评分为每个城市最大值的司机，这样就算一个城市有很多司机都是最大值，也能输出
WHERE driver_id IN (
    SELECT driver_id
    FROM temp_0
    WHERE avg_grade = max_avg_grade
    )
ORDER BY avg_order_num

思路：

1.一个临时表，先求每个城市的每个司机的聚合信息

2.计算字段求最终信息，并且使用临时表求每个城市符合要求的司机

其实是先求指定id，再根据id求其信息的增强版