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

select period, count(period)as get_car_num, round(avg(wait_time),1)as avg_wait_time, round(avg(dispatch_time),1)as avg_dispatch_time from (SELECT (case when date_format(event_time,'%T')>='07:00:00'and date_format(event_time,'%T')<'09:00:00' then'早高峰' when date_format(event_time,'%T')>='09:00:00'and date_format(event_time,'%T')<'17:00:00' then'工作时间' when date_format(event_time,'%T')>='17:00:00'and date_format(event_time,'%T')<'20:00:00' then'晚高峰' else '休息时间' end)as period, round(TIMESTAMPDIFF(second,event_time,order_time)/60,1)as wait_time, round(TIMESTAMPDIFF(second,order_time,start_time)/60,1)as dispatch_time FROM tb_get_car_order o left join tb_get_car_record r on o.order_id=r.order_id where (WEEKDAY(order_time) between 0 and 4) and event_time is not null)t GROUP by period order by get_car_num