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,
       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
   

   • event_time：表示事件发生的时间。
   • CASE 语句：根据 event_time 的小时数将时间段分类为“早高峰”、“工作时间”、“晚高峰”和“休息时间”。
   • TIMESTAMPDIFF(SECOND, event_time, end_time)：计算乘客从事件发生到结束时间的等待时间（秒）。
   • TIMESTAMPDIFF(SECOND, order_time, start_time)：计算从下单时间到开始服务时间的派单时间（秒）。
   • FROM tb_get_car_record JOIN tb_get_car_order USING (order_id)：从 tb_get_car_record 表和 tb_get_car_order 表中通过 order_id 字段进行内连接。
   • WHERE DAYOFWEEK(event_time) BETWEEN 2 AND 6：筛选出工作日（周一至周五）的数据。

2. 外层查询部分：

   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
       (子查询部分) as t_wait_dispatch_time
   GROUP BY
       period
   ORDER BY
       get_car_num;
   

   • 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：计算每个时间段的平均派单时间（将秒转换为分钟，并保留一位小数）。
   • GROUP BY period：按时间段进行分组。
   • ORDER BY get_car_num：按获取车辆的总数进行排序。