题解 | #工作日各时段叫车量、等待接单时间和调度时间#
工作日各时段叫车量、等待接单时间和调度时间
https://www.nowcoder.com/practice/34f88f6d6dc549f6bc732eb2128aa338
-- weekday(日期) 可以返回0-6,0是周一,6是周日 -- 接单时间;应该用打车订单表tb_get_car_order看,因为记录表未必接单 -- 调度时间,应该用打车订单表tb_get_car_order看,order_time - start_time (start_time可以为null 要过滤) -- 叫车量,from用户打车记录表tb_get_car_record SELECT CASE WHEN time_format(event_time,'%H%m%d') + 0 >= 70000 AND time_format(event_time,'%H%m%d') + 0 < 90000 THEN '早高峰' WHEN time_format(event_time,'%H%m%d') + 0 >= 90000 AND time_format(event_time,'%H%m%d') + 0 < 170000 THEN '工作时间' WHEN time_format(event_time,'%H%m%d') + 0 >= 170000 AND time_format(event_time,'%H%m%d') + 0 < 200000 THEN '晚高峰' ELSE '休息时间' END 'period', COUNT(DISTINCT t1.id) 'get_car_num', ROUND(AVG(TIMESTAMPDIFF(SECOND,EVENT_TIME , ORDER_TIME))/60,1) 'avg_wait_time', ROUND(AVG(TIMESTAMPDIFF(SECOND,ORDER_TIME , START_TIME))/60,1) 'avg_dispatch_time' FROM tb_get_car_record t1 LEFT JOIN tb_get_car_order t2 using(order_id) WHERE WEEKDAY(EVENT_TIME) <5 GROUP BY 1 ORDER BY 2 ASC , 3 ASC
一步一步来,很容易一次过