Mysql 解法, 执行用时: 36ms, 内存消耗: 6500KB, 提交时间: 2022-01-27

select
       (case
            when time(event_time) >= '07:00:00'
                and time(event_time) < '09:00:00' then '早高峰'
            when time(event_time) >= '09:00:00'
                and time(event_time) < '17:00:00' then '工作时间'
            when time(event_time) >= '17:00:00'
                and time(event_time) < '20:00:00' then '晚高峰'
            else '休息时间'
           end) as period,
       count(tgcr.order_id) as get_car_num,
       round(avg(timestampdiff(second,event_time,order_time)/60),1) as avg_wait_time,
       round(avg(timestampdiff(second,order_time,start_time)/60),1) as avg_dispatch_time
from tb_get_car_record as tgcr
join tb_get_car_order tgco on tgcr.order_id = tgco.order_id
where weekday(event_time) between 0 and 4
group by period
order by get_car_num

Mysql 解法, 执行用时: 36ms, 内存消耗: 6512KB, 提交时间: 2022-01-23

select case when hour(event_time) in (7,8) then '早高峰'
when hour(event_time) between 9 and 16 then '工作时间'
when hour(event_time) between 17 and 19 then '晚高峰'
else '休息时间' end as period,
count(event_time) as get_car_num,
round(avg(wait_time)/60,1) as avg_wait_time,
round(avg(dispatch_time)/60,1) as avg_dispatch_time
from
(select event_time,
timestampdiff(second,event_time, order_time) as wait_time,
timestampdiff(second,order_time, start_time) as dispatch_time
from tb_get_car_order tb1 inner join tb_get_car_record tb2
on tb1.order_id=tb2.order_id
where weekday(event_time) between 0 and 4) t1
group by period
order by get_car_num

Mysql 解法, 执行用时: 37ms, 内存消耗: 6400KB, 提交时间: 2022-01-22

select case  when hour(dt) in (7,8) then '早高峰'
             when hour(dt) between 9 and 16 then '工作时间'
             when hour(dt) between 17 and 19 then '晚高峰'
             else '休息时间' end as period,
             count(order_id) as get_car_num, 
             round(avg(wait_time),1) as avg_wait_time, 
             round(avg(dispatch_time),1) as avg_dispatch_time
from
(select order_id, date_format(event_time, '%T') dt, round(TIMESTAMPDIFF(second, event_time , end_time)/60,1) AS wait_time
from tb_get_car_record 
where WEEKDAY(event_time) BETWEEN 0 and 4
) as wait_t
join
(select order_id, round(TIMESTAMPDIFF(second, order_time , start_time)/60,1) AS dispatch_time
from tb_get_car_order
where WEEKDAY(order_time) BETWEEN 0 and 4
) as dispatch_t using(order_id)
group by period
order by get_car_num

Mysql 解法, 执行用时: 37ms, 内存消耗: 6400KB, 提交时间: 2022-01-03

SELECT period, COUNT(event_time) order_num, ROUND(AVG(wait_time), 1), ROUND(SUM(dispatch_time)/COUNT(dispatch_time), 1)
FROM(
	SELECT event_time,  CASE
			WHEN RIGHT(event_time, 8) >='07:00:00' AND RIGHT(event_time, 8) < '09:00:00' THEN '早高峰'
			WHEN RIGHT(event_time, 8) >='09:00:00' AND RIGHT(event_time, 8) < '17:00:00' THEN '工作时间'
			WHEN RIGHT(event_time, 8) >='17:00:00' AND RIGHT(event_time, 8) < '20:00:00' 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 tgcr
	LEFT JOIN tb_get_car_order
	USING(order_id)
    WHERE DATE_FORMAT(event_time, '%w') BETWEEN 1 AND 5
	) a 
GROUP BY period
ORDER BY order_num 

Mysql 解法, 执行用时: 37ms, 内存消耗: 6424KB, 提交时间: 2021-12-07

select
    case
        when subString_index(event_time, ' ', -1) between '07:00:00'
        and '08:59:59' then '早高峰'
        when subString_index(event_time, ' ', -1) between '09:00:00'
        and '16:59:59' then '工作时间'
        when subString_index(event_time, ' ', -1) between '17:00:00'
        and '19:59:59' then '晚高峰'
        else '休息时间'
    end period,
    count(1) AS get_car_num,
    round(
        avg(TIMESTAMPdiff(second, event_time, order_time))/60,
        1
    ) AS avg_wait_time,
    round(
        avg(Timestampdiff(second, order_time, start_time))/60,
        1
    ) AS avg_dispatch_time
from
    tb_get_car_record a
    JOIN tb_get_car_order b ON a.order_id = b.order_id
WHERE
     WEEKDAY(order_time) NOT IN(5, 6)
GROUP BY period
ORDER BY get_car_num