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

这题重点就在于时间相关的函数timestampdiff(), time(), weekday() 的应用以及对于case when 的应用

1. 合并tb_get_car_record 以及 tb_get_car_order 利用weekday() 找到工作日产生的订单

select *
    from tb_get_car_record 
    inner join tb_get_car_order
    using(order_id)
    where weekday(event_time) between 0 and 4

2. 按照要求将划分period，统计等待接单时间 (end_time - event_time), 调度时间 (start_time - order_time)

select
    case 
        when time(event_time) between '07:00:00' AND '08:59:59' THEN '早高峰'
        when time(event_time) between '09:00:00' AND '16:59:59' Then '工作时间'
        when time(event_time) between '17:00:00' AND '19:59:59' then '晚高峰'
        ELSE '休息时间'
    END period,
    order_id, 
    timestampdiff(second,event_time,end_time)/60 as wait_time,
    timestampdiff(second,order_time,start_time)/60 as dispatch_time
    from tb_get_car_record 
    inner join tb_get_car_order
    using(order_id)
    where weekday(event_time) between 0 and 4

3. 再按照period统计后以get_car_num做升序

select period, 
    count(order_id) 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 time(event_time) between '07:00:00' AND '08:59:59' THEN '早高峰'
        when time(event_time) between '09:00:00' AND '16:59:59' Then '工作时间'
        when time(event_time) between '17:00:00' AND '19:59:59' then '晚高峰'
        ELSE '休息时间'
    END period,
    order_id, 
    timestampdiff(second,event_time,end_time)/60 as wait_time,
    timestampdiff(second,order_time,start_time)/60 as dispatch_time
    from tb_get_car_record 
    inner join tb_get_car_order
    using(order_id)
    where weekday(event_time) between 0 and 4
    ) temp 
    GROUP BY period
    order by get_car_num