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

# 解题思路：用weekday（）找出周一到周五，找出早工晚休4个时间段，event_time分组聚合求各时段平均叫车量，order_time - event_time 为等待时间， start_time - order_time 为调度时间
with a as(
    select
    case 
    when hour(event_time) < 7 then '休息时间'
    when hour(event_time) < 9 then '早高峰'
    when hour(event_time) < 17 then '工作时间'
    when hour(event_time) < 20 then '晚高峰'
    else '休息时间'
    end period,
    count(event_time) get_car_num,
    round(avg(timestampdiff(second,event_time, order_time) / 60), 1) avg_wait_time,
    round(avg(timestampdiff(second, order_time, start_time) / 60), 1) avg_dispatch_time
    from tb_get_car_order o left join tb_get_car_record r on 
    o.order_id = r.order_id
    where weekday(event_time) between 0 and 4
    group by period
)
select * from a
order by get_car_num