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

select period,
	   count(period) as 数量,
       round(avg(timestampdiff(second,event_time,end_time)/60),1) as 等待时间,
       round(avg(timestampdiff(second,order_time,start_time)/60),1) as 调度时间
       from
(select (case when date_format(event_time,'%T') >= '07:00:00' and date_format(event_time,'%T') < '09:00:00' then '早高峰'
            when date_format(event_time,'%T') >= '09:00:00' and date_format(event_time,'%T') < '17:00:00' then '工作时间'
            when date_format(event_time,'%T') >= '17:00:00' and date_format(event_time,'%T') < '20:00:00' then '晚高峰'
            else '休息时间' end)
            as period,
            event_time,end_time,tb_get_car_record.order_id,start_time,order_time
            from
            tb_get_car_record join tb_get_car_order on tb_get_car_record.order_id = tb_get_car_order.order_id and tb_get_car_record.uid = tb_get_car_order.uid
            where date_format(event_time,'%W') <> 'Saturday' and date_format(event_time,'%W') <> 'Sunday') a
            group by period
            order by 数量 asc;