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

思路:

第一步: 先根据 driver_id 分组,统计单个 司机接单数量 >= 3 的司机 第二步: 再计算出单个司机的总钱数

select 
count(*) driver_order,
sum(fare) money
from tb_get_car_record t_r
join tb_get_car_order t_o
on t_r.order_id = t_o.order_id
where city = '北京'
and event_time BETWEEN '2021-10-01' and '2021-10-07'
GROUP BY driver_id HAVING driver_order >= 3
运行结果如下:
+--------------+-------+
| driver_order | money |
+--------------+-------+
|            3 |   105 |
|            4 |   137 |
+--------------+-------+
2 rows in set (0.00 sec)

第三步: 总订单数除以总条数，总钱数除以总条数

select '北京' city,
round(sum(driver_order)/count(*),3) avg_order_num,
round(sum(money)/count(*),3) avg_income
from(
select 
count(*) driver_order,
sum(fare) money
from tb_get_car_record t_r
join tb_get_car_order t_o
on t_r.order_id = t_o.order_id
where city = '北京'
and event_time BETWEEN '2021-10-01' and '2021-10-07'
GROUP BY driver_id HAVING driver_order >= 3)t

运行结果:
+--------+---------------+------------+
| city   | avg_order_num | avg_income |
+--------+---------------+------------+
| 北京   |         3.500 |        121 |
+--------+---------------+------------+
1 row in set (0.00 sec)