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

#需求：统计周一到周五各时段的叫车量、平均等待接单时间和平均调度时间
#输出：period、get_car_num、avg_wait_time、avg_dispatch_time
#要求：period以event_time划分；等待接单时间和调度时间保留一位小数；avg_dispatch_time仅计算完成订单；输出按get_car_num升序
#时段定义：早高峰 07-09、工作时间 09-17、晚高峰 17-20、休息时间 20-07,左闭右开（即7:00:00算作早高峰，而9:00:00不算做早高峰）
#问题拆分：周一到周五：dayofweek(event_time) between 2 and 6
#区分时段：case when
#叫车量：count(1)
#等待接单时间：event_time与order_time之差，timestampdiff(second,event_time,order_time)
#调度时间：order_time与start_time之差，timestampdiff(second,order_time,start_time)
#平均：avg(时间/60),round保留一位
#思路：先做子查询，得到区分后的时段、等待接单时间和调度时间，并且限制时间范围（工作日），得到这样一张表后，再做avg，加上要求条件，输出数据
with t1 as(
  select timestampdiff(second,event_time,order_time) tt1,
  timestampdiff(second,order_time,start_time) tt2,
  #如果未完成，order_time为null，计算结果为null，会自动剔除null值，完成仅计算完成订单的要求
  case
  when hour(event_time) in(7,8) then "早高峰"
  when hour(event_time) between 9 and 16 then "工作时间"
  when hour(event_time) between 17 and 19 then "晚高峰"
  else "休息时间"
  end tt3
  #材料中的左闭右开要求
  from tb_get_car_order join tb_get_car_record using(order_id)
  where dayofweek(event_time) between 2 and 6
)
select tt3 period,count(1) get_car_num,
round(avg(tt1/60),1) avg_wait_time,round(avg(tt2/60),1) avg_dispatch_time
from t1
group by period
order by get_car_num