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

with 
    t1 as(
        #将周一到周五的数据划分为时间段并且将每单的等待接单时间和调度时间算出
        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`,
            tgcr.order_id,
            unix_timestamp(end_time)-unix_timestamp(event_time) `wait_time`,
            if(start_time is null,null, unix_timestamp(start_time)-unix_timestamp(order_time)) `dispatch_time`
        from tb_get_car_record tgcr
        join tb_get_car_order tgco
        on tgcr.order_id=tgco.order_id
        where DayOfWeek(date(event_time)) between 2 and 6
    )

select 
    period,
    count(order_id) `get_car_num`,
    round(avg(wait_time)/60,1) `avg_wait_time`,
    round(avg(dispatch_time)/60,1) `avg_dispatch_time`
from t1
group by period
order by get_car_num