题解 | #2021年国庆在北京接单3次及以上的司机统计信息#

问题：请统计2021年国庆7天期间在北京市接单至少3次的司机的平均接单数和平均兼职收入（暂不考虑平台佣金，直接计算完成的订单费用总额），结果保留3位小数。

1.将问题拆分为两个部分，一部分是哪些司机满足这些条件，另一部分是这些司机的指标计算。这样看就简单许多。我们先来求有哪些司机是被2021国庆期间在北京接单三次及以上的。

select  driver_id
from tb_get_car_order  as b join tb_get_car_record as a on a.order_id=b.order_id
where city='北京' and order_time between '2021-10-01' and '2021-10-07'
group by driver_id having count(order_time)>=3

运行结果如下：

2.利用上述结果对其进行计算即可。使用where driver_id in...即可完成操作，完整代码如下：

select city,round(count(order_time)/count(distinct driver_id),3) as avgo,
round(sum(fare)/count(distinct driver_id),3) as avgf
from tb_get_car_order  as b join tb_get_car_record as a on a.order_id=b.order_id
where city='北京' and order_time between '2021-10-01' and '2021-10-07'
and driver_id in 
(select  driver_id
from tb_get_car_order  as b join tb_get_car_record as a on a.order_id=b.order_id
where city='北京' and order_time between '2021-10-01' and '2021-10-07'
group by driver_id having count(order_time)>=3)