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

-- 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

一步一步来，很容易一次过