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

1. 合并tb_get_car_record以及tb_get_car_order获取国庆期间在北京产生的订单明细

select *
	FROM tb_get_car_record
    inner join tb_get_car_order
    using(order_id)
    where city = '北京' AND date(event_time) between '2021-10-01' AND '2021-10-07'

2. 对这些订单按driver_id汇总，并找到北京接单3次以上的司机的汇总信息

SELECT  driver_id, 
	count(order_id) as order_num, 
    sum(fare) as total_inc
    FROM tb_get_car_record
    inner join tb_get_car_order
    using(order_id)
    where city = '北京' AND date(event_time) between '2021-10-01' AND '2021-10-07'
    GROUP BY driver_id
    HAVING order_num >=3

3. 将3次以上的司机的汇总信息再次汇总获得所需的统计信息

select '北京' as city, 
    round(avg(order_num),3) as avg_order_num, 
    round(avg(total_inc),3) as avg_income 
    from (
        SELECT  driver_id, 
      	count(order_id) as order_num, 
      	sum(fare) as total_inc
        FROM tb_get_car_record
        inner join tb_get_car_order
        using(order_id)
        where city = '北京' AND date(event_time) between '2021-10-01' AND '2021-10-07'
        GROUP BY driver_id
        HAVING order_num >=3
    ) temp