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

明确题意：

统计周一到周五各时段的叫车量、平均等待接单时间和平均调度时间。

全部以开始打车时间为时段划分依据，平均等待接单时间和平均调度时间均保留1位小数，平均调度时间仅计算完成了的订单，结果按叫车量升序排序。

问题分解：

• 计算每次叫车的等待时间和调度时间（生成子表t_wait_dispatch_time）：
  • 关联打车记录和订单表：tb_get_car_record JOIN tb_get_car_order USING(order_id)
  • 筛选工作日的记录（周一到周五）：WHERE DAYOFWEEK(event_time) BETWEEN 2 AND 6
  • 转换打车时间所属时段：CASE WHEN HOUR(event_time) IN (7, 8) THEN '早高峰' ... END as period
  • 计算等待接单时间：TIMESTAMPDIFF(SECOND, event_time, end_time) as wait_time
  • 计算调度时间：TIMESTAMPDIFF(SECOND, order_time, start_time) as dispatch_time
• 按时段分组：GROUP BY period
• 计算叫车量：COUNT(1) as get_car_num
• 计算平均等待接单时间：AVG(wait_time/60) as avg_wait_time
• 计算平均调度时间：AVG(dispatch_time/60) as avg_dispatch_time
• 保留1位小数：ROUND(x, 1)

细节问题：

• 表头重命名：as
• 按叫车量升序排序：ORDER BY get_car_num

完整代码：

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;