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

select city,driver_id,avg_grade,avg_order_num,avg_mileage from
(select 
city,
driver_id,
round(avg_grade,1)avg_grade,
round(order_num/workdays,1) as avg_order_num,
round(sum_mileage/workdays,3) as avg_mileage,
rank()over(partition by city order by avg_grade desc) as rk
from 
  (select
  city,
  driver_id,
  avg(grade) as avg_grade,
  count(order_id)as order_num,
  sum(mileage) as sum_mileage,
  count(distinct date(finish_time))as workdays
  from tb_get_car_record
  join tb_get_car_order using(order_id)
  group by driver_id,city) as tc) as td 
where rk=1
order by avg_order_num

千辛万苦写出来了，都是写的过程中知道要一层一层嵌套。本题关键在于窗口函数rank（），因为要求求每个城市中平均得分最高的数据，所以可以用到窗口函数，还要多加理解窗口函数。这里只是刚好只有北京的数据，所以只显示了北京，正常可能有其他的城市的rank1 显示出来