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

SELECT 
(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 '晚高峰'
WHEN (TIME(event_time) >= '20:00:00' AND TIME(event_time) <= '24:00:00' )
OR (TIME(event_time) >= '00:00:00' AND TIME(event_time) <'07:00:00')
THEN '休息时间'
ELSE NULL
END) AS tag,
COUNT(*) AS get_car_num,
ROUND(AVG(TIMESTAMPDIFF(SECOND,event_time,end_time) / 60),1) AS avg_wait_time,
ROUND(AVG(IF(mileage IS NULL,NULL,TIMESTAMPDIFF(SECOND,order_time,start_time) / 60)),1) AS avg_dispatch_time


FROM tb_get_car_record AS gcr
JOIN tb_get_car_order AS gco
ON gcr.order_id = gco.order_id
WHERE DAYOFWEEK(event_time) BETWEEN 2 AND 6
GROUP BY (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 '晚高峰'
WHEN (TIME(event_time) >= '20:00:00' AND TIME(event_time) <= '24:00:00' )
OR (TIME(event_time) >= '00:00:00' AND TIME(event_time) <'07:00:00')
THEN '休息时间'
ELSE NULL
END)
ORDER BY get_car_num ASC;