题解 | #某乎问答单日回答问题数大于等于3个的所有用户#

select 
period,
count(order_id) get_car_num,
round(avg(wait_time),1) avg_wait_time,
round(avg(dispatch_time),1) avg_dispatch_time
from (
select t2.order_id,if(weekday(date(event_time))<5,1,0) xingqi,
case when
hour(event_time) in (7,8) then '早高峰' 
when hour(event_time) in (17,18,19)  then '晚高峰'
when hour(event_time) >=9  and hour(event_time) < 17 then '工作时间'
else '休息时间' end period ,
timestampdiff(second,event_time,end_time)/60 wait_time	,
timestampdiff(second,order_time,start_time)/60 dispatch_time 
from tb_get_car_record t1
left join tb_get_car_order t2
on t1.order_id=t2.order_id  ) a
where xingqi=1
group by period
order by get_car_num