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

select 
(
    case when substr(event_time,12,8)>='07:00:00' and substr(event_time,12,8)<'09:00:00' then '早高峰'
    when substr(event_time,12,8)>='09:00:00' and substr(event_time,12,8)<'17:00:00' then '工作时间'
    when substr(event_time,12,8)>='17:00:00' and substr(event_time,12,8)<'20:00:00' then '晚高峰'
    else '休息时间' end

) as period,
count(event_time) as get_car_num,
round(avg(timestampdiff(second,event_time,end_time)/60),1) as avg_wait_time,
round(avg(timestampdiff(second,order_time,start_time)/60),1) as avg_wait_time

from tb_get_car_record tr
left join tb_get_car_order too
on tr.order_id = too.order_id

where weekday(event_time) between 0 and 4
group by period
order by get_car_num

一个查询就可以写出