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

WITH
    t1 AS (
        SELECT
            b.city,
            a.driver_id,
            ROUND(AVG(grade), 1) AS avg_grade
        FROM
            tb_get_car_order a
            LEFT JOIN tb_get_car_record b ON a.order_id = b.order_id
        WHERE
            start_time is not NULL
        GROUP BY
            b.city,
            a.driver_id
        ORDER BY
            b.city,
            a.driver_id
    ),
    t2 AS (
        SELECT
            city,
            driver_id,
            avg_grade,
            rank() over (
                PARTITION BY
                    city
                ORDER BY
                    avg_grade DESC
            ) AS rank_num
        FROM
            t1
    ),
    t3 AS (
        SELECT
            *
        FROM
            t2
        WHERE
            rank_num = 1
    ),
    t4 AS (
        SELECT
            b.city,
            a.driver_id,
            DATE (a.order_time) AS dt,
            IFNULL (a.mileage, 0) AS mileage
        FROM
            tb_get_car_order a
            LEFT JOIN tb_get_car_record b ON a.order_id = b.order_id
        WHERE
            (b.city, a.driver_id) IN (
                SELECT
                    t3.city,
                    t3.driver_id
                FROM
                    t3
            )
    ),
    t5 AS (
        SELECT
            city,
            driver_id,
            ROUND(count(driver_id) / count(DISTINCT dt), 1) as avg_order_num,
            ROUND(sum(mileage) / count(DISTINCT dt), 3) as avg_mileage
        FROM
            t4
        GROUP BY
            city,
            driver_id
    )
SELECT
    t3.city,
    t3.driver_id,
    t3.avg_grade,
    t5.avg_order_num,
    t5.avg_mileage
FROM
    t3
    LEFT JOIN t5 ON t3.driver_id = t5.driver_id
    AND t3.city = t5.city
ORDER BY
    t5.avg_order_num;

# 这道题的关键在于，评分均值是成单的评分，而接单数则是包括取消单子！还有一个就是时间距离计算！