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

# 指标：平均接单数=接单数/司机数
# 平均兼职收入=兼职总收入/司机人数
# 1.平均接单数
# 1.1 统计出每个司机的接单数，筛选出接单数至少3次的记录
# select driver_id,count(*)
# from tb_get_car_record tgcr
# inner join tb_get_car_order tgco 
# on tgcr.order_id=tgco.order_id
# where date(order_time) between '2021-10-01' and '2021-10-07'
# and city='北京'
# group by driver_id
# having count(*)>=3
# 1.2 平均接单数
# select round(sum(cnt)/count(driver_id),3) avg_order_num
# from (
#   select driver_id,count(*) cnt
#   from tb_get_car_record tgcr
#   inner join tb_get_car_order tgco 
#   on tgcr.order_id=tgco.order_id
#   where date(order_time) between '2021-10-01' and '2021-10-07'
#   and city='北京'
#   group by driver_id
#   having count(*)>=3
# ) t
# 2.平均兼职收入
# 2.1 统计每个司机的兼职收入，筛选出接单至少为3次的记录
# select driver_id,sum(fare)
# from tb_get_car_record tgcr
# inner join tb_get_car_order tgco 
# on tgcr.order_id=tgco.order_id
# where date(order_time) between '2021-10-01' and '2021-10-07'
# and city='北京'
# group by driver_id
# having count(*)>=3
# 2.2 平均兼职收入
# select round(sum(income)/count(driver_id),3) avg_income
# from (
#   select driver_id,sum(fare) income
#   from tb_get_car_record tgcr
#   inner join tb_get_car_order tgco 
#   on tgcr.order_id=tgco.order_id
#   where date(order_time) between '2021-10-01' and '2021-10-07'
#   and city='北京'
#   group by driver_id
#   having count(*)>=3
# ) t
# 3.整合
select '北京' as city,round(sum(cnt)/count(driver_id),3) avg_order_num
,round(sum(income)/count(driver_id),3) avg_income
from (
  select driver_id,count(*) cnt,sum(fare) income
  from tb_get_car_record tgcr
  inner join tb_get_car_order tgco 
  on tgcr.order_id=tgco.order_id
  where date(order_time) between '2021-10-01' and '2021-10-07'
  and city='北京'
  group by driver_id
  having count(*)>=3
) t