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

select city,
round(avg(cnt),3) as avg_order_num,
avg(income) as avg_income
from(
select tr.city,too.driver_id,
count(too.order_id) as cnt,
sum(too.fare) as income

from tb_get_car_record tr
join tb_get_car_order too
on tr.order_id = too.order_id

where tr.city = '北京'
and date(too.order_time) between '2021-10-01' and '2021-10-07'
group by tr.city,too.driver_id)a
where cnt>=3
group by city

本来多加了一个限制条件 fare is not null结果反而不对，原因：

`sum(too.fare) as income`，如果fare为null，sum会忽略null值，所以sum的结果可能不会包括这些订单，但count(order_id)仍然会计数这些订单。因此，当用户加上`fare is not null`时，实际上排除了这些fare为null的订单，导致count(order_id)减少