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

明确题意：

统计每个城市中评分最高的司机平均评分、日均接单量和日均行驶里程数。有多个司机评分并列最高时，都输出。

平均评分和日均接单量保留1位小数，日均行驶里程数保留3位小数，按日均接单数升序排序。

问题分解：

• 计算每个司机的评分、日均接单量、日均里程和城市内评分排名（生成子表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;