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

#
# 按照period分组

with t as(
select o.order_id, event_time, end_time, start_time, finish_time,order_time,
case 
    when '07:00:00' <= time(event_time) and time(event_time) < '09:00:00' then '早高峰'
    when '09:00:00' <= time(event_time) and time(event_time)< '17:00:00' then '工作时间'
    when '17:00:00' <= time(event_time) and time(event_time)< '20:00:00' then '晚高峰'
    else '休息时间'
    end as period

from tb_get_car_record r
join tb_get_car_order o on r.order_id = o.order_id
where weekday(event_time) between 0 and 4 #周一到周五
)

select
    period,
    count(order_id) 'get_car_num',
    round(avg(timestampdiff(second, event_time, end_time) / 60),1) 'avg_wait_time',
    round(avg(timestampdiff(second, order_time, start_time) / 60),1) 'avg_dispatch_time'
from t
group by period
order by get_car_num asc;

先计算period，然后根据period进行分组计算即可，注意要求的是工作日的时间