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

#需求：统计每个城市中评分最高的司机平均评分、日均接单量和日均行驶里程数
#输出：city、driver_id、avg_grade、avg_order_num、avg_mileage
#要求：只输出平均评分最高的司机的数据；avg_grade和avg_order_num保留一位小数，avg_mileage保留三位，按avg_order_num升序输出
#要用到的数据：city、driver_id、grade、order_time、mileage，表链接
#拆分问题：每个城市中平均评分最高的司机：开窗rank，排序平均分，取第一的值
#平均评分：avg(grade)
#日均接单量和里程数：都涉及到日均，先取得天数count(distinct date(order_time)),再以接单量和里程数除以天数
with t1 as(
  select city,driver_id,avg(grade) tt1,count(distinct date(order_time)) tt2,
  count(order_time) tt3,sum(mileage) tt4
  from tb_get_car_order join tb_get_car_record using(order_id)
  group by city,driver_id
)
select city,driver_id,avg_grade,avg_order_num,avg_mileage
#因为要对ck1进行筛选，但不输出ck1，所以再套一层select
from(
  select city,driver_id,round(tt1,1) avg_grade,
  round(tt3/tt2,1) avg_order_num,round(tt4/tt2,3) avg_mileage,
  rank()over(partition by city order by tt1 desc) ck1
  from t1
)t2
where ck1=1
order by avg_order_num