题解 | #店铺901国庆期间的7日动销率和滞销率# 通俗易懂

select period
,count(*) car_num
,round(avg(wait_time),1) avg_wait_time
,round(avg(dispatch_time),1) avg_wait_time
from(
        select timestampdiff(second,t1.event_time,t1.end_time)/60 wait_time,timestampdiff(second,t2.order_time,t2.start_time)/60 dispatch_time
        ,case when  date_format(event_time,'%H:%i:%S') between '09:00:00' and '16:59:59' then '工作时间'
              when  date_format(event_time,'%H:%i:%S') between '17:00:00' and '19:59:59' then '晚高峰'
              when  date_format(event_time,'%H:%i:%S') between '07:00:00' and '08:59:59' then '早高峰'
              else '休息时间' end period
        from tb_get_car_record t1 join tb_get_car_order t2 on t1.order_id=t2.order_id
        where weekday(event_time) between 0 and 4 )as T1
group by period
order by car_num asc