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

WITH t AS
(
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 '晚高峰'
ELSE '休息时间'
END period,
TIMESTAMPDIFF(SECOND,event_time,end_time) wait_time,
TIMESTAMPDIFF(SECOND,order_time,start_time) dispatch_time
FROM tb_get_car_record r JOIN tb_get_car_order o ON r.order_id = o.order_id
WHERE DAYOFWEEK(event_time) BETWEEN 2 AND 6
)

SELECT period,COUNT(*) get_car_num,
ROUND(AVG(wait_time)/60,1) avg_wait_time,
ROUND(AVG(dispatch_time)/60,1) acg_dispatch_time
FROM t
GROUP BY period
ORDER BY get_car_num