题解 | #店铺901国庆期间的7日动销率和滞销率# 通俗易懂
工作日各时段叫车量、等待接单时间和调度时间
http://www.nowcoder.com/practice/34f88f6d6dc549f6bc732eb2128aa338
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