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

明确题意：

• 统计周一到周五各时段的叫车量、平均等待接单时间和平均调度时间
• 以event_time-开始打车时间为时段划分依据
• 平均等待接单时间和平均调度时间均保留1位小数
• 平均调度时间仅计算完成了的订单
• 结果按叫车量升序排序

问题拆解：

1. 指标：
   • 叫车辆
   • 平均等待接单时间=总的等待接单时间（打车时间-接单时间）/订单数目
   • 平均调度时间=总的调度时间（上车时间-接单时间）/订单数目
2. 筛选条件：周一到周五 -- WHERE DATE_FORMAT(event_time,'%W') NOT IN ( 'Saturday' ,'Sunday')
3. 新生成的时段字段 -- case when

解题流程：

1.先提取出求解指标所需要的数据：连接tb_get_car_record和tb_get_car ，根据order_id，筛选出工作日的记录

  select a.*,b.*
  from tb_get_car_record a
  inner join tb_get_car_order b
  on a.order_id=b.order_id
  where date_format(event_time,'%W') not in ('Saturday','Sunday')

2.在1.得到的表的基础上求出求解指标所需字段和新生成的时段字段

 select a.order_id
  ,TIMESTAMPDIFF(SECOND,event_time,order_time)/60 wait_time
  ,TIMESTAMPDIFF(SECOND,order_time,start_time)/60 dispatch_time
  ,CASE WHEN DATE_FORMAT(event_time,'%H-%i-%s') >= '07-00-00' AND DATE_FORMAT(event_time,'%H-%i-%s') < '09-00-00' THEN '早高峰'
                WHEN DATE_FORMAT(event_time,'%H-%i-%s') >= '09-00-00' AND DATE_FORMAT(event_time,'%H-%i-%s') < '17-00-00' THEN '工作时间'
                WHEN DATE_FORMAT(event_time,'%H-%i-%s') >= '17-00-00' AND DATE_FORMAT(event_time,'%H-%i-%s') < '20-00-00' THEN '晚高峰'
                ELSE '休息时间'
                end period
  from tb_get_car_record a
  inner join tb_get_car_order b
  on a.order_id=b.order_id
  where date_format(event_time,'%W') not in ('Saturday','Sunday')

3.根据时段字段分组聚合，求出所有指标，并按照叫车量排序

select period,count(*) get_car_num, ROUND(AVG(wait_time),1) avg_wait_time,
    ROUND(AVG(dispatch_time),1) avg_dispatch_time
from (
  select a.order_id
  ,TIMESTAMPDIFF(SECOND,event_time,order_time)/60 wait_time
  ,TIMESTAMPDIFF(SECOND,order_time,start_time)/60 dispatch_time
  ,CASE WHEN DATE_FORMAT(event_time,'%H-%i-%s') >= '07-00-00' AND DATE_FORMAT(event_time,'%H-%i-%s') < '09-00-00' THEN '早高峰'
                WHEN DATE_FORMAT(event_time,'%H-%i-%s') >= '09-00-00' AND DATE_FORMAT(event_time,'%H-%i-%s') < '17-00-00' THEN '工作时间'
                WHEN DATE_FORMAT(event_time,'%H-%i-%s') >= '17-00-00' AND DATE_FORMAT(event_time,'%H-%i-%s') < '20-00-00' THEN '晚高峰'
                ELSE '休息时间'
                end period
  from tb_get_car_record a
  inner join tb_get_car_order b
  on a.order_id=b.order_id
  where date_format(event_time,'%W') not in ('Saturday','Sunday')
) as t
group by period
ORDER BY get_car_num