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

with tmp as (
    select
        event_time,
        case when hour(event_time) in (7,8) then '早高峰'
             when hour(event_time) between 9  and 16 then '工作时间'
             when hour(event_time) in (17,18,19) then '晚高峰'
             else '休息时间' end  perid,
        timestampdiff(second ,event_time,end_time) wait_time,
        timestampdiff(second ,order_time,start_time) diaodu_time
    from tb_get_car_order a join tb_get_car_record b on a.order_id = b.order_id
    where DAYOFWEEK(event_time) BETWEEN 2 AND 6
)
select perid,count(*) get_car_num,round(avg(wait_time)/60,1) avg_wait_time,round(avg(diaodu_time)/60,1) avg_dispatch_time
from tmp
group by perid
order by get_car_num;

先使用case when 得到每个每行数据属于哪个period时段，计算等待时间、调度时间

然后按照period进行groupby count* 得到每个period的接单数 并avg时间

重点是使用到了星期函数DAYOFWEEK