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

with t1 as(
    select tb2.order_id,event_time,order_time,start_time,finish_time
    from tb_get_car_order tb2
    left join  tb_get_car_record tb1
    on tb1.order_id=tb2.order_id
    WHERE DAYOFWEEK(date(event_time)) BETWEEN 2 AND 6
),
t2 as(select (case when date_format(event_time,'%H:%i:%s')>='09:00:00' and date_format(event_time,'%H:%i:%s')<'17:00:00' then '工作时间'
when (date_format(event_time,'%H:%i:%s')>='20:00:00' and date_format(event_time,'%H:%i:%s')<'23:59:59') or (date_format(event_time,'%H:%i:%s')>='00:00:00' and date_format(event_time,'%H:%i:%s')<'07:00:00')  then '休息时间'
when date_format(event_time,'%H:%i:%s')>='17:00:00' and date_format(event_time,'%H:%i:%s')<'20:00:00'  then '晚高峰'
when date_format(event_time,'%H:%i:%s')>='07:00:00' and date_format(event_time,'%H:%i:%s')<'09:00:00'  then '早高峰' end) as period,order_id,event_time,order_time,start_time,finish_time
from t1)
select period ,count(period)  get_car_num,round((sum(timestampdiff(second,event_time,order_time))/count(order_time))/60,1)   avg_wait_time,round((sum(timestampdiff(second,order_time,start_time))/count(start_time))/60,1) avg_dispatch_time
from t2
group by period
order by get_car_num