如何在MySQL中统计多日时段内车辆每小时进出次数

现有表结构及数据 #

统计需求 #

需要统计不同日期下每小时的车辆ENTRY（进入）和EXIT（驶出）次数，期望输出格式如下：

现有尝试的SQL问题 #

已尝试以下SQL，但仅支持单日24小时统计，无法满足多日需求：

SELECT EXTRACT(HOUR FROM timestamp) as hour, 
       SUM(IF(action = 'ENTER', 1, 0)) as enter_count, 
       SUM(IF(action = 'EXIT', 1, 0)) as exit_count 
FROM vehicle_logs 
WHERE timestamp >= '2022-08-01 00:00:00' AND timestamp <= '2022-08-02 23:59:59' 
GROUP BY EXTRACT(HOUR FROM timestamp);

解决方案 #

1. 修正分组逻辑，包含日期+小时 #

原SQL只按小时分组，导致不同日期的同一小时数据被合并。需要将日期+小时作为分组依据，同时格式化输出为目标时段格式：

-- MySQL版本
SELECT 
    DATE_FORMAT(timestamp, '%y-%m-%d %H:00:00') AS 小时时段,
    SUM(CASE WHEN 操作类型 = 'ENTRY' THEN 1 ELSE 0 END) AS 进入次数,
    SUM(CASE WHEN 操作类型 = 'EXIT' THEN 1 ELSE 0 END) AS 驶出次数
FROM vehicle_logs
WHERE timestamp >= '2022-08-01 00:00:00' AND timestamp <= '2022-08-02 23:59:59'
GROUP BY DATE_FORMAT(timestamp, '%y-%m-%d %H')
ORDER BY 小时时段;

2. 补全无数据的小时时段（可选） #

如果需要展示所有小时（包括无车辆进出的时段），需先生成指定时间范围内的完整小时序列，再与统计结果关联：

-- MySQL版本，生成完整时段并关联统计
WITH RECURSIVE hour_range AS (
    SELECT '2022-08-01 00:00:00' AS hour_slot
    UNION ALL
    SELECT DATE_ADD(hour_slot, INTERVAL 1 HOUR)
    FROM hour_range
    WHERE hour_slot < '2022-08-02 23:00:00'
)
SELECT 
    ROW_NUMBER() OVER(ORDER BY hr.hour_slot) AS 编号,
    DATE_FORMAT(hr.hour_slot, '%y-%m-%d %H:00:00') AS 小时时段,
    COALESCE(vl.进入次数, 0) AS 进入次数,
    COALESCE(vl.驶出次数, 0) AS 驶出次数
FROM hour_range hr
LEFT JOIN (
    SELECT 
        DATE_FORMAT(timestamp, '%Y-%m-%d %H:00:00') AS hour_slot,
        SUM(CASE WHEN 操作类型 = 'ENTRY' THEN 1 ELSE 0 END) AS 进入次数,
        SUM(CASE WHEN 操作类型 = 'EXIT' THEN 1 ELSE 0 END) AS 驶出次数
    FROM vehicle_logs
    WHERE timestamp >= '2022-08-01 00:00:00' AND timestamp <= '2022-08-02 23:59:59'
    GROUP BY hour_slot
) vl ON hr.hour_slot = vl.hour_slot
ORDER BY hr.hour_slot;

说明：

• 用递归CTE生成指定时间范围内的所有小时时段
• 通过LEFT JOIN关联统计结果，COALESCE将无数据的NULL转为0
• ROW_NUMBER()生成连续的编号字段

内容的提问来源于stack exchange，提问作者Akhil Rana