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

题目不难 1.要点为 case when 分组，between 是闭区间所以不能用 2.timestampdiff不能用minute 而要用second 在除以六十，不然分钟差不准确 3.如何表示周几，可以使用date_format(," ") 其中常用的是 %Y 四位数表示年份 %m %d， %w不常用 显示周几 0为周日，6为周六

select 
case when right(event_time,8) <"09:00:00" and right(event_time,8)>="07:00:00" then "早高峰"
     when right(event_time,8) <"17:00:00" and right(event_time,8)>="09:00:00" then "工作时间"
     when right(event_time,8) <"20:00" and right(event_time,8)>="17:00:00" then "晚高峰"
     when right(event_time,8) <"07:00:00" or right(event_time,8)>="20:00:00" then "休息时间" end as period,
     count(*) as num,
     round(avg(timestampdiff(second,event_time,order_time)/60),1),
     round(avg(timestampdiff(second,order_time,start_time)/60),1)
from tb_get_car_record re inner join tb_get_car_order using(uid,order_id)
where date_format(event_time,"%w") between 1 and 5
group by period
order by num