一、知识点梳理和拓展

比较常规的知识点，涉及到星期的取值，我复习了下date_format，对时间换算的知识点，如下：

二、题目解读

统计周一到周五各时段的叫车量、平均等待接单时间和平均调度时间。
1)三个相关指标的定义
  • 叫车量：统计order_id。
  • 等待时间：从开始打车到司机接单，event_time和order_time的时间差，因而order_time不能为空。
  • 调度时间：从司机接单到上车，order_time和start_time的时间差，且订单状态为已完成订单，因而fare不能为空（fare不为空，start_time也肯定部为空）。
2)条件：周一到周五各时段
  • 各个时段用case when 来进行定义。
  • 周一到周五用date_format(字段名,‘%w’)来进行定义。
3）题目中隐藏的坑
这里有一个小坑，统计时间差要先以秒为单位统计，计算平均值的时候需要将秒转换成分（除以60）来计算。
3）字段需求
  • period：时间段划分
  • get_car_num：叫车量
  • avg_wait_time ：平均等待时间
  • avg_dispatch_time ：平均调度时间

三、解题步骤

梳理完了以后，发现整个解题步骤就2步：1）连接两张表建立一张子表：计算每个订单的等待时间和调度时间，并时间段进行赋值定义，并限定条件为周1到周5。2）建完表，通过对时间段进行聚类后，计算各个时段的叫车量和调度时间。

1）建立子表统计每一各订单的等待时间，调度时间，并对时间段进行定义
  • 等待时间 
IF(order_time IS NOT NULL,TIMESTAMPDIFF(SECOND,event_time,order_time),NULL) wait_time, #计算叫车等待时间，order_time非空难
  • 调度时间 
IF(fare IS NOT NULL,TIMESTAMPDIFF(SECOND,order_time,start_time),NULL) dispatch_time,#计算车辆调度时间，仅计算完成的订单，那么fare非空
  • 选择时间周1—周5 
DATE_FORMAT(event_time,'%w') BETWEEN 1 AND 5; #周1到周5
  • 对时间段进行定义 
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 period
2）对时间段进行聚类，求各时段的叫车量、平均等待接单时间和平均调度时间。结果按叫车量升序排序，平均等待接单时间和平均调度时间保留1位小数
  • 叫车量 
COUNT(order_id) get_car_num
  • 平均等待时间和平均调度时间 
ROUND(AVG(wait_time/60),1)avg_wait_time #平均等待时间
ROUND(AVG(dispatch_time/60),1)avg_dispatch_time #平均调度时间
  • 完整代码 
WITH t1 AS(
SELECT  tb_get_car_record.order_id, #订单号用来计算叫车量
    IF(order_time IS NOT NULL,TIMESTAMPDIFF(SECOND,event_time,order_time),NULL) wait_time, #计算叫车等待时间，order_time非空难
    IF(fare IS NOT NULL,TIMESTAMPDIFF(SECOND,order_time,start_time),NULL) dispatch_time,#计算车辆调度时间，仅计算完成的订单，那么fare非空
	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 period
FROM tb_get_car_record LEFT JOIN tb_get_car_order USING(order_id)
WHERE DATE_FORMAT(event_time,'%w') BETWEEN 1 AND 5
)

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;