题解|窗口均值rank|#每个城市中评分最高的司机信息#

# SELECT driver_id
# FROM tb_get_car_order a
# LEFT JOIN tb_get_car_record b
# ON a.order_id = b.order_id
# GROUP BY city,driver_id
# HAVING MAX(AVG(grade))

# ## 日均接单量 = COUNT(order_id) / DATEDIFF(DAY,MAX(order_time),MIN(order_time))
# ## 日均里程数 = SUM(mileage) / DATEDIFF(DAY,MAX(order_time),MIN(order_time))
# ## 不正确！使用DATEDIFF计算的时间差默认每天都在接单，但实际情况不是应该计数不同的日期作为接单天数


# SELECT  city,driver_id,
#         ROUND(AVG(grade),1) AS avg_grade,
#         ROUND(COUNT(a.order_id) / COUNT(DISTINCT DATE(order_time)),1) AS avg_order_num,
#         ROUND(SUM(mileage) / COUNT(DISTINCT DATE(order_time)),3) AS avg_mileage
# FROM tb_get_car_order a
# LEFT JOIN tb_get_car_record b
# ON a.order_id = b.order_id
# GROUP BY city,driver_id
# ORDER BY avg_grade DESC
# LIMIT 1


# SELECT  city,driver_id,
#         ROUND(AVG(grade),1) AS avg_grade,
#         ROUND(COUNT(a.order_id) / COUNT(DISTINCT DATE(order_time)),1) AS avg_order_num,
#         ROUND(SUM(mileage) / COUNT(DISTINCT DATE(order_time)),3) AS avg_mileage
# FROM tb_get_car_order a
# LEFT JOIN tb_get_car_record b
# ON a.order_id = b.order_id
# GROUP BY city,driver_id

SELECT city,driver_id,ROUND(avg_grade,1),avg_order_num,avg_mileage
FROM(
    SELECT *,DENSE_RANK() OVER(PARTITION BY city ORDER BY avg_grade DESC) AS rk
    FROM(
        SELECT city,driver_id,AVG(grade) AS avg_grade,
               ROUND(COUNT(a.order_id) / COUNT(DISTINCT DATE(order_time)),1) AS avg_order_num,
               ROUND(SUM(mileage) / COUNT(DISTINCT DATE(order_time)),3) AS avg_mileage
        FROM tb_get_car_order a
        LEFT JOIN tb_get_car_record b
        ON a.order_id = b.order_id
        GROUP BY city,driver_id
    )t1
    )t2
WHERE rk = 1
ORDER BY avg_order_num


需要捋清楚思路：
第一层（最内层）SELECT 计算分城市、分司机的avg_grade;注意一定在最内层把计算分组顺清楚
第二层 SELECT 需要使用第一层的均值进行窗口函数的RANK，这时候的RANK只针对城市，选出不同城市得分最高的，所以PARTITION BY 后只跟city即可，不用同时跟city和driver_id,这里注意理解一下；
第三层 SELECT 对最终的结果进行输出。

窗口函数几个RANK的记忆：
ROW_NUMBER()就是每行都进行排序，按照row进行；
DENSE_RANK()理解为“密集”排序，值相同序号相同，但不会有序号不存在1，1，2,...序号是连续存在的，即“密集”DENSE;
RANK()和DENSE对应理解，即“非密集”，会有序号缺失，序号是不连续存在的，1，1，3，...