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

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 (
    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
) as t_wait_dispatch_time
GROUP BY period
ORDER BY get_car_num;

1. 子查询 (SELECT event_time, ... FROM tb_get_car_record JOIN tb_get_car_order USING(order_id) WHERE DAYOFWEEK(event_time) BETWEEN 2 AND 6)：

   • 这个子查询首先通过 order_id 将 tb_get_car_record 和 tb_get_car_order 两个表格连接起来。
   • WHERE DAYOFWEEK(event_time) BETWEEN 2 AND 6 确保只选择星期一至星期五的数据，因为 DAYOFWEEK() 函数返回的值是 1（星期日）到 7（星期六）。

2. 时间段分类 (CASE WHEN HOUR(event_time) IN (7, 8) THEN '早高峰' ... ELSE '休息时间' END as period)：

   • 使用 CASE 语句根据 event_time 的小时数将时间段分类为“早高峰”、“工作时间”、“晚高峰”或“休息时间”。

3. 等待时间和派单时间的计算：

   • TIMESTAMPDIFF(SECOND, event_time, end_time) as wait_time 计算从 event_time 到 end_time 的时间差（以秒为单位），这代表等待时间。
   • TIMESTAMPDIFF(SECOND, order_time, start_time) as dispatch_time 计算从 order_time 到 start_time 的时间差（以秒为单位），这代表派单时间。

4. 外部查询 (SELECT period, COUNT(1) as get_car_num, ... FROM (...) as t_wait_dispatch_time GROUP BY period ORDER BY get_car_num)：

   • 这个查询使用子查询的结果作为临时表 t_wait_dispatch_time。
   • COUNT(1) as get_car_num 计算每个时间段内的获取车辆次数。
   • ROUND(AVG(wait_time/60), 1) 和 ROUND(AVG(dispatch_time/60), 1) 分别计算每个时间段内的平均等待时间和平均派单时间，将秒转换为分钟，并保留一位小数。
   • GROUP BY period 按时间段分组结果。
   • ORDER BY get_car_num 按获取车辆的次数排序结果。