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

# 统计周一到周五各时段的叫车量、平均等待接单时间和平均调度时间。

# 周一到周五
# WHERE DAYOFWEEK(event_time) BETWEEN 2 AND 6;

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

# 从开始打车到司机接单为等待接单时间: timestampdiff(minute, event_time, order_time)
# 等待时间包含取消的订单

# 从司机接单到上车为调度时间: timestampdiff(minute, order_time, start_time)
# 调度时间不包含取消的订单

# 叫车量：count(cr.*)

# 全部以event_time-开始打车时间为时段划分依据，
# 平均等待接单时间和平均调度时间均保留1位小数，
# 平均调度时间仅计算完成了的订单，结果按叫车量升序排序。

# 筛选条件：周一到周五
# 按时间段分组，计算叫车辆，平均等待时间，平均调度时间

select
    (case 
        when time(event_time) >= '07:00:00' and time(event_time) < '09:00:00' then '早高峰'
        when time(event_time) >= '09:00:00' and time(event_time) < '17:00:00' then '工作时间'
        when time(event_time) >= '17:00:00' and time(event_time) < '20:00:00' then '晚高峰' else '休息时间' end) as period
    , count(*) as get_car_num
    , round(avg(timestampdiff(second, event_time, order_time))/60, 1) as avg_wait_time 
    , round(avg(timestampdiff(second, order_time, start_time))/60, 1) as avg_dispatch_time
from tb_get_car_order as co
inner join tb_get_car_record as cr on co.order_id = cr.order_id 
where dayofweek(date(cr.event_time)) between 2 and 6
group by 1
order by get_car_num;