题解 | #工作日各时段叫车量、等待接单时间和调度时间#
工作日各时段叫车量、等待接单时间和调度时间
http://www.nowcoder.com/practice/34f88f6d6dc549f6bc732eb2128aa338
-- 周一到周五:dayofweek(),其中1表示周日,2表示周一
-- 两个以上分段,用CASE...WHEN...
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
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
) period_wait_dispatch_t
GROUP BY
period
ORDER BY
get_car_num