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

• 思路很常规，就是连接两个表，然后新建一列表示早高峰等，然后计算相应的值就好
• 这里主要记录一下我第一遍时的错误！忽略了周末不计算和调度时间只计算完成订单的
• 先放正确的代码，再放自己犯错误的代码

正确代码 ：
select
	period,
    count(*) as get_car_num,
    round(avg(wait_time)/60,1) as avg_wait_time,
    round(avg(dispatch_time)/60,1) as avg_wait_time #avg会忽略null
    from
(select
   order_id,
	timestampdiff(second,event_time,order_time) as wait_time,
    timestampdiff(second,order_time,start_time) as dispatch_time,
    case
		when date_format(event_time,'%H:%i:%s')>='07:00:00' and date_format(event_time,'%H:%i:%s')<'09:00:00' then '早高峰'
        when date_format(event_time,'%H:%i:%s')>='09:00:00' and date_format(event_time,'%H:%i:%s')<'17:00:00' then '工作时间'
		when date_format(event_time,'%H:%i:%s')>='17:00:00' and date_format(event_time,'%H:%i:%s')<'20:00:00' then '晚高峰'
        else '休息时间'
	end as period
from(
select 
	tgcr.order_id,
    event_time,
    order_time,
    start_time
from  tb_get_car_record tgcr
left join tb_get_car_order tgco
on tgcr.uid=tgco.uid and tgcr.order_id=tgco.order_id
where date_format(event_time,'%W') not in ('Saturday','Sunday')
)t1)t2
group by period
order by get_car_num

错误代码：自己反思出来的
select
	period,
    count(1) as get_car_num,
    round((sum(timestampdiff(second,event_time,end_time))/count(1))/60 ,1) as avg_wait_time,
	round((sum(timestampdiff(second,end_time,start_time))/count(1))/60 ,1) as avg_dispatch_time
#上面这一句有问题，因为调度时间只计算完成订单的，但是这里/count(1)或者count(*)就把没有完成订单的都计算进去了，意思就是count(*)或count(1)不忽略null
from(
select
	*,
    case
		when date_format(event_time,'%H:%i:%s')>='07:00:00' and date_format(event_time,'%H:%i:%s')<'09:00:00' then '早高峰'
        when date_format(event_time,'%H:%i:%s')>='09:00:00' and date_format(event_time,'%H:%i:%s')<'17:00:00' then '工作时间'
		when date_format(event_time,'%H:%i:%s')>='17:00:00' and date_format(event_time,'%H:%i:%s')<'20:00:00' then '晚高峰'
        else '休息时间'
	end as period
from(
select 
	tgcr.order_id,
    event_time,
    end_time,
    start_time
from  tb_get_car_record tgcr
left join tb_get_car_order tgco
on tgcr.uid=tgco.uid and tgcr.order_id=tgco.order_id
where date_format(event_time,'%W') not in ('Saturday','Sunday')#啊！我忘记要在周内这个条件了！学到了学到了
)t1)t2
group by period
order by get_car_num