问题是:请统计每个城市中评分最高的司机平均评分、日均接单量和日均行驶里程数。
看到每个城市中评分最高的司机平均评分,我们很容易想到用dense_rank窗口函数去做,但是看到有题解是窗口函数的了,那我们就换一种思路不用窗口函数求解;
首先我们先把每个城市中每个司机的平均分求出来,:
select city,driver_id,avg(grade) avgd from tb_get_car_order a join tb_get_car_record b on a.order_id=b.order_id group by city,driver_id然后再套娃把每个城市中的最高评分求出来:
select city,max(t1.avgd) mavg from (select city,driver_id,avg(grade) avgd from tb_get_car_order a join tb_get_car_record b on a.order_id=b.order_id group by city,driver_id ) t1 group by city再和最开始求出城市中每个司机的平均分的那个表进行join连接,得到每个城市中平均分最高的司机
select t2.city city ,t1.driver_id driver_id,t2.mavg mavg from (select city,max(t1.avgd) mavg from (select city,driver_id,avg(grade) avgd from tb_get_car_order a join tb_get_car_record b on a.order_id=b.order_id group by city,driver_id ) t1 group by city ) t2 join (select city,driver_id,avg(grade) avgd from tb_get_car_order a join tb_get_car_record b on a.order_id=b.order_id group by city,driver_id ) t1 on t2.city=t1.city and t2.mavg=t1.avgd最后我们将上面表和打车订单表tb_get_car_order连接,求出每个城市,平均分最高的日均接单量和日均行驶里程数,完整代码如下
select t3.city,t3.driver_id,round(t3.mavg,1),round(count(a.order_id)/count(distinct date(a.order_time)),1) as ct, round(sum(a.mileage)/count(distinct date(a.order_time)),3) from tb_get_car_order a join (select t2.city city ,t1.driver_id driver_id,t2.mavg mavg from (select city,max(t1.avgd) mavg from (select city,driver_id,avg(grade) avgd from tb_get_car_order a join tb_get_car_record b on a.order_id=b.order_id group by city,driver_id ) t1 group by city ) t2 join (select city,driver_id,avg(grade) avgd from tb_get_car_order a join tb_get_car_record b on a.order_id=b.order_id group by city,driver_id ) t1 on t2.city=t1.city and t2.mavg=t1.avgd )t3 on a.driver_id=t3.driver_id group by t3.city,t3.driver_id order by ct;