题解|TIME函数 工作日写法| 叫车量、等待接单和调度时间

## 等待时间：接单时间-打车时间 order_time - event_time
## 调度时间：上车时间-接单时间 start_time = order_time
## 打车数量：COUNT(order_id) AS get_car_num
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) AS period,
    COUNT(order_id) 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
JOIN tb_get_car_record
USING (order_id)
WHERE WEEKDAY(event_time) BETWEEN 0 AND 4
GROUP BY period
ORDER BY get_car_num