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

case when

select
  N.name,
  count(N.name),
  round(
    sum(TIMESTAMPDIFF(second, N.event_time, N.order_time)) / count(N.name) / 60,
    1
  ),
  round(
    sum(TIMESTAMPDIFF(second, N.order_time, N.start_time)) / count(N.start_time) / 60,
    1
  )
from
  (
    select
      (
        case
          when date_format(tgr.event_time, "%T") >= "07:00:00"
          and date_format(tgr.event_time, "%T") < "09:00:00" then "早高峰"
          when date_format(tgr.event_time, "%T") >= "17:00:00"
          and date_format(tgr.event_time, "%T") < "20:00:00" then "晚高峰"
          when date_format(tgr.event_time, "%T") >= "20:00:00"
          and date_format(tgr.event_time, "%T") < "7:00:00" then "休息时间"
          else "工作时间"
        end
      ) name,
      tgo.order_time,
      tgo.start_time,
      tgr.event_time
    from
      tb_get_car_order tgo
      inner join tb_get_car_record tgr on tgo.order_id = tgr.order_id
    where
      date_format(tgr.event_time, "%W") <> "Saturday"
      and date_format(tgr.event_time, "%W") <> "Sunday"
  ) N
group by
  N.name
order by
  count(N.name)