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

• 筛选工作日，创建period特征，同时直接结合订单表与记录表，计算等待时间，调做时间
• 之后直接根据period进行分组统计即可

WITH t1 AS(
    SELECT order_id
            , TIMESTAMPDIFF(SECOND, event_time, order_time)/60 AS wait_time
            , TIMESTAMPDIFF(SECOND, order_time, start_time)/60 AS dispatch_time
            ,CASE
                WHEN HOUR(event_time) BETWEEN 7 AND 8 THEN '早高峰'
                WHEN HOUR(event_time) BETWEEN 9 AND 16 THEN '工作时间'
                WHEN HOUR(event_time) BETWEEN 17 AND 19 THEN '晚高峰'
                ELSE '休息时间'
            END AS period
    FROM tb_get_car_order
    JOIN tb_get_car_record USING(order_id)
    WHERE DAYOFWEEK(event_time) BETWEEN 2 AND 6

)

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