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

select C.city, round(avg(drive_tm),3) as avg_order_num, round(avg(fee),3) as avg_income from
(select city, driver_id, count(order_time) as drive_tm, sum(fare)as fee from 
tb_get_car_order left join
tb_get_car_record
on tb_get_car_order. order_id = tb_get_car_record.order_id
where city = "北京"
    and order_time is not null 
    and order_time >="2021-10-1"
    and order_time < "2021-10-8"
group by driver_id
having count(order_time) >=3) as C;

Step 1: 筛选出接单3次及其以上的司机的总收入和总接单量

筛选条件：城市是北京、接单时间在国庆、接3单及其以上

Step 2: 在此表基础上计算接单平均值和金额平均值

注意：此处使用order_time 字段而非 start_time 字段