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

-- 周一到周五：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