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

SELECT
CASE 
    WHEN HOUR(event_time) >= '9' AND HOUR(event_time) < '17' THEN '工作时间'
    WHEN HOUR(event_time) >= '20' OR HOUR(event_time) < '7' THEN '休息时间'
    WHEN HOUR(event_time) >= '17' AND HOUR(event_time) < '20' THEN '晚高峰' 
    ELSE '早高峰'
END AS period,
COUNT(event_time)AS get_car_num,
ROUND(AVG(TIMESTAMPDIFF(SECOND,event_time,order_time)/60), 1) AS avg_wait_time,
ROUND(AVG(TIMESTAMPDIFF(SECOND,order_time,start_time)/60), 1) AS avg_dispatch_time

FROM tb_get_car_order
LEFT JOIN tb_get_car_record
    USING(order_id)
 
WHERE 
DAYNAME(event_time) NOT IN ('saturday','sunday')

GROUP BY period

ORDER BY get_car_num