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

WITH t1 AS (
	SELECT 
		event_time,
        CASE
            WHEN HOUR(event_time) IN (7, 8) THEN '早高峰'
            WHEN HOUR(event_time) BETWEEN 9 AND 16 THEN '工作时间'
            WHEN HOUR(event_time) IN (17, 18, 19) THEN '晚高峰'
            ELSE '休息时间'
        END as period,
        TIMESTAMPDIFF(SECOND, event_time, end_time) as wait_time,
        TIMESTAMPDIFF(SECOND, order_time, start_time) as dispatch_time
    FROM tb_get_car_record
    JOIN tb_get_car_order USING(order_id)
    WHERE DAYOFWEEK(event_time) BETWEEN 2 AND 6
)
SELECT period, COUNT(1) as get_car_num,
    ROUND(AVG(wait_time/60), 1) as avg_wait_time,
    ROUND(AVG(dispatch_time/60), 1) as avg_dispatch_time
FROM t1
GROUP BY period
ORDER BY get_car_num;

# 这道题的关键在于时间函数的使用，内连接可能好一点！（这个需要测试！）