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

with valid_order as (
    select
        t1.city as city,
        t2.driver_id as driver_id,
        count(t2.order_id) as order_num,
        sum(ifnull(fare,0)) as income
    from 
        tb_get_car_record t1
        join tb_get_car_order t2
        on t1.order_id = t2.order_id
    where city = '北京' and 
        date(t2.order_time) between '2021-10-01' and '2021-10-07' and
        date(t2.finish_time) between '2021-10-01' and '2021-10-07'
    group by t1.city, t2.driver_id
    having count(*) >= 3
)
select 
    city,
    round(avg(order_num), 3) as avg_order_num,
    round(avg(income), 3) as avg_income
from valid_order
group by city

注意：接单但被取消的订单也需要算在订单数量中！！！！所以不能加上start_time is not null