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

with t1 as(select driver_id,count(tbr.order_id) as count_order,sum(ifnull(fare,0)) as total_money
from tb_get_car_order tbr
left join tb_get_car_record tbd
on tbr.order_id=tbd.order_id
where city='北京' and event_time between '2021-10-01 00:00:00' and '2021-10-07 23:59:59'
group by driver_id
having count(tbr.order_id) >=3)


select '北京' as city,round(avg(count_order),3) as avg_order_num,round(avg(total_money),3) as avg_income
from t1

1. 通过order id 连接表
2. 过滤条件 北京 且时间在国庆期间
3. 按照司机分组
4. 计算司机的总订单数和总盈利
5. 对分组后数据 筛选 订单数大于等于3的
6. 查询上述表 avg函数计算列平均值