题解 | #某乎问答单日回答问题数大于等于3个的所有用户#
工作日各时段叫车量、等待接单时间和调度时间
http://www.nowcoder.com/practice/34f88f6d6dc549f6bc732eb2128aa338
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
查看12道真题和解析
