【场景】：在某个数据的基础上再计算

【分类】：in子查询、where (a,b) in、group by having

分析思路

注意：

1.外键是order_id，不能用uid做连接 2.取消了订单也算接单，不要把问题想的太复杂

关键是理解业务

首先，搞清楚5个时间的业务关系

event_time-打车时间, end_time-打车结束时间, order_time-接单时间, start_time-开始计费的上车时间, finish_time-订单完成时间

（1）统计2021年国庆7天期间在北京市接单至少3次的司机

  • [条件]：date(order_time) between '20211001' and '20211007' and city = '北京'
  • [使用]：group by driver_id having count(order_time) >= 3

（2）统计2021年国庆7天期间在北京市接单至少3次的司机的平均接单数和平均兼职收入，结果保留3位小数

  • [使用]：sum() ;count()

求解代码

方法一

with子句

with
    main as(
        #统计2021年国庆7天期间在北京市接单至少3次的司机
        select
            driver_id,
            sum(fare) as fare_sum,
            count(order_time) as order_num
        from tb_get_car_order
        join tb_get_car_record using(order_id )
        where date(order_time) between '20211001' and '20211007'
        and city = '北京'
        group by driver_id having count(order_time) >= 3
    )
#统计2021年国庆7天期间在北京市接单至少3次的司机的平均接单数和平均兼职收入（暂不考虑平台佣金，直接计算完成的订单费用总额），结果保留3位小数。
select
    '北京' as city,
    round(sum(order_num)/count(distinct driver_id),3) as avg_order_num,
    round(sum(fare_sum)/count(distinct driver_id),3) as avg_income
from main

方法二

多表连接

#统计2021年国庆7天期间在北京市接单至少3次的司机的平均接单数和平均兼职收入（暂不考虑平台佣金，直接计算完成的订单费用总额），结果保留3位小数。
select
    '北京' as city,
    round(sum(order_num)/count(distinct driver_id),3) as avg_order_num,
    round(sum(fare_sum)/count(distinct driver_id),3) as avg_income
from(
    #统计2021年国庆7天期间在北京市接单至少3次的司机
    select
        driver_id,
        sum(fare) as fare_sum,
        count(order_time) as order_num
    from tb_get_car_order
    join tb_get_car_record using(order_id )
    where date(order_time) between '20211001' and '20211007'
    and city = '北京'
    group by driver_id having count(order_time) >= 3
) main

方法三

in子查询

#统计2021年国庆7天期间在北京市接单至少3次的司机的平均接单数和平均兼职收入（暂不考虑平台佣金，直接计算完成的订单费用总额），结果保留3位小数。
select
    city,
    round(count(order_id)/count(distinct driver_id),3) as avg_order_num,
    round(sum(fare)/count(distinct driver_id),3) as avg_income
from tb_get_car_order
join tb_get_car_record using(order_id )
where driver_id in(
    #统计2021年国庆7天期间在北京市接单至少3次的司机
    select
        driver_id
    from tb_get_car_order
    join tb_get_car_record using(order_id )
    where date(order_time) between '20211001' and '20211007'
    and city = '北京'
    group by driver_id having count(order_time) >= 3
)
and date(order_time) between '20211001' and '20211007'
and city = '北京'