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

问题分解：

• 计算每次叫车的等待时间和调度时间（生成子表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;
``````