题解 | #工作日各时段叫车量、等待接单时间和调度时间#

select
period,
count(distinct id) get_car_num,
round(sum(wait_time)/count(wait_time)/60,1) avg_wait_time,
round(sum(dispatch_time)/count(case when dispatch_time is not null then dispatch_time end)/60,1) avg_dispatch_time
from 
(
    select
    distinct a.id,
    case when hour(a.event_time) >=7 and hour(a.event_time) <9 then '早高峰'
    when hour(a.event_time) >=9 and hour(a.event_time) <17 then '工作时间'
    when hour(a.event_time) >=17 and hour(a.event_time) <20 then '晚高峰'
    when hour(a.event_time) >=20 or hour(a.event_time) <7 then '休息时间' 
    end as period,
    timestampdiff(second,a.event_time,b.order_time) wait_time,
    case when b.finish_time is not null then 
    timestampdiff(second,b.order_time,b.start_time) 
    else null end dispatch_time
    from 
    tb_get_car_record a
    left join 
    tb_get_car_order b
    on a.order_id=b.order_id
    where dayofweek((a.event_time)) between 2 and 6
) c
group by period
order by get_car_num,avg_wait_time

把工作日这个条件忘记了，

dayofweek((a.event_time)) between 2 and 6