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

 -- 最重要需要判定是否是工作日
 WITH t1 AS 
	 (SELECT r.event_time,
		r.end_time,
        o.order_time,
        o.start_time,
		CASE WHEN 7 <= HOUR(event_time) AND  HOUR(event_time) < 9 THEN '早高峰'
			WHEN 9 <= HOUR(event_time) AND  HOUR(event_time) < 17 THEN '工作时间'
			WHEN 17 <= HOUR(event_time) AND  HOUR(event_time) < 20 THEN '晚高峰'
			ELSE '休息时间'
		END AS period
	 FROM tb_get_car_record r
	 LEFT JOIN tb_get_car_order o USING(order_id)
	  -- 1为星期天，以此类推
     WHERE DAYOFWEEK(event_time) BETWEEN 2 AND 6)
SELECT 
	period,
    COUNT(period) AS get_car_num,
	ROUND(AVG(TIMESTAMPDIFF(SECOND, event_time, end_time)) / 60, 1) AS wait_time,
    ROUND(AVG(TIMESTAMPDIFF(SECOND, order_time, start_time)) / 60, 1) AS dispatch_time
FROM t1
GROUP BY period
ORDER BY get_car_num;