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

WITH t1 AS(
    SELECT o.order_id,
        CASE WHEN HOUR(r.event_time) BETWEEN 7 AND 8 THEN '早高峰'
        WHEN HOUR(r.event_time) BETWEEN 9 AND 16 THEN '工作时间'
        WHEN HOUR(r.event_time) BETWEEN 17 AND 19 THEN '晚高峰'
        ELSE '休息时间' END AS period,
        (UNIX_TIMESTAMP(r.end_time) - UNIX_TIMESTAMP(r.event_time)) / 60.0 wait_time,
        CASE 
            WHEN o.fare IS NOT NULL 
            THEN (UNIX_TIMESTAMP(o.start_time) - UNIX_TIMESTAMP(o.order_time)) / 60.0 
            ELSE NULL 
        END AS dispatch_time
    FROM tb_get_car_record r
    LEFT JOIN tb_get_car_order o
    ON o.order_id = r.order_id
    WHERE WEEKDAY(r.event_time) BETWEEN 0 AND 4
)

SELECT period,
    COUNT(*) get_car_num,
    ROUND(AVG(wait_time),1) avg_wait_time,
    ROUND(AVG(dispatch_time),1) avg_dispatch_time
FROM t1
GROUP BY period
ORDER BY get_car_num ASC