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

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 / order_date_cnt, 1) AS avg_order_num,
      ROUND(total_mileage / order_date_cnt, 3) AS avg_mileage,
      RANK() OVER(
        PARTITION BY city
        ORDER BY
          avg_grade DESC
      ) AS rank_grade
    FROM
      (
        SELECT
          city,
          driver_id,
          COUNT(DISTINCT DATE(order_time)) AS order_date_cnt,
          COUNT(order_id) AS order_num,
          AVG(grade) AS avg_grade,
          SUM(mileage) AS total_mileage
        FROM
          tb_get_car_record
          JOIN tb_get_car_order USING(order_id)
        GROUP BY
          city,
          driver_id
      ) driver_information
  ) rank_grade_t
WHERE
  rank_grade = 1
ORDER BY
  avg_mileage