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

1、分析题目：
统计周一到周五各时段的叫车量、平均等待接单时间和平均调度时间。全部以event_time-开始打车时间为时段划分依据，平均等待接单时间和平均调度时间均保留1位小数，平均调度时间仅计算完成了的订单，结果按叫车量升序排序。
①周1-周5：    dayofweek:1：星期天👉7：星期六  /  weekday:0：星期一👉6：星期日
②各时段：早高峰 [07:00:00 , 09:00:00)、工作时间 [09:00:00 , 17:00:00）、晚高峰 [17:00:00 , 20:00:00）、休息时间 [20:00:00 , 07:00:00） 👉  case when ＆ extract(hour from xxx) ＆ in ＆ between and
③保留1位小数：round(xxx,1)
④平均调度时间仅计算完成了的订单：start_time is not null

2、实操：
①创建临时的时间区间表：提取每个订单的 “hour” ，从而为后续分类做准备
with time
as(
    select extract(hour from t1.event_time) as difference,
    case when t1.event_time is not null then 1 else 0 end as car_num,
	注意：原因：由于展示结果精确到了秒，因此此处先按照 “秒” 进行计算更符合需求
    timestampdiff(second,t1.event_time,t2.order_time) as wait_time,
    timestampdiff(second,t2.order_time,t2.start_time) as dispatch_time
    from tb_get_car_record as t1
    left join tb_get_car_order as t2
    on t1.order_id = t2.order_id
	注意：此处要筛选周1-周5的数据
    where dayofweek(t1.event_time) between 2 and 6
)

②根据上一步挑选出的hour进行分类统计
select case when difference in (7,8) then '早高峰'
    when difference between 9 and 16 then '工作时间'
    when difference in (17,18,19) then '晚高峰'
    else '休息时间'
    end,
sum(car_num) as get_car_num,
round(avg(wait_time/60) ,1) as avg_wait_time,
round(avg(dispatch_time/60) ,1) as avg_dispatch_time
from time
group by 1
order by 2