# 每个城市中评分最高的司机日均接单量和里程数

### 问题分解：

• 计算每个司机的评分、日均接单量、日均里程和城市内评分排名（生成子表t_driver_rk）：
• 计算每个司机的评分等信息（生成子表t_driver_info）：
• 关联打车记录表和订单表：tb_get_car_record JOIN tb_get_car_order USING(order_id)
• 按司机和城市分组：GROUP BY 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
• 计算日均指标和排名：
• 日均订单量：order_num / work_days as avg_order_num
• 日均里程数：toal_mileage / work_days as avg_mileage
• 计算城市内的评分排名，允许并列第一：RANK() over(PARTITION BY city ORDER BY avg_grade DESC) as rk
• 保留小数位数：ROUND(x, 1)
• 筛选每个城市的第一名：WHERE rk = 1

### 细节问题：

• 表头重命名：as
• 按日均接单数升序排序：ORDER BY avg_order_num;

### 完整代码：

``````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;
``````