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

• 叫车量：统计order_id。
• 等待时间：从开始打车到司机接单，event_time和order_time的时间差，因而order_time不能为空。
• 调度时间：从司机接单到上车，order_time和start_time的时间差，且订单状态为已完成订单，因而fare不能为空（fare不为空，start_time也肯定部为空）。

• 各个时段用case when 来进行定义。
• 周一到周五用date_format(字段名,‘%w’)来进行定义。

• period：时间段划分
• get_car_num：叫车量
• avg_wait_time ：平均等待时间
• avg_dispatch_time ：平均调度时间

• 等待时间

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

• 叫车量

• 平均等待时间和平均调度时间

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;