插件窝 干货文章 如何用 MySQL 统计一天数据量,并将其划分为 5 分钟一个区间?

如何用 MySQL 统计一天数据量,并将其划分为 5 分钟一个区间?

INTERVAL TIME 区间 column 466    来源:    2025-03-31

MySQL 统计一天数据量并按5分钟区间划分

要统计一天内的数据量并按5分钟一个区间进行划分,可以使用MySQL的日期时间函数和GROUP BY子句。以下是几种实现方法:

方法一:使用DATE_FORMAT和FLOOR函数

SELECT 
    DATE_FORMAT(
        DATE_ADD(
            '2023-01-01', 
            INTERVAL FLOOR(TIME_TO_SEC(time_column)/300)*300 SECOND
        ), 
        '%Y-%m-%d %H:%i:00'
    ) AS time_interval,
    COUNT(*) AS count
FROM your_table
WHERE DATE(time_column) = '2023-01-01'
GROUP BY time_interval
ORDER BY time_interval;

方法二:使用TIMESTAMP DIV方法(MySQL 5.7+)

SELECT 
    FROM_UNIXTIME(UNIX_TIMESTAMP(time_column) DIV 300 * 300) AS time_interval,
    COUNT(*) AS count
FROM your_table
WHERE DATE(time_column) = '2023-01-01'
GROUP BY time_interval
ORDER BY time_interval;

方法三:使用DATE_FORMAT和MINUTE函数

SELECT 
    CONCAT(
        DATE_FORMAT(time_column, '%Y-%m-%d %H:'),
        LPAD(FLOOR(MINUTE(time_column)/5)*5, 2, '0'),
        ':00'
    ) AS time_interval,
    COUNT(*) AS count
FROM your_table
WHERE DATE(time_column) = '2023-01-01'
GROUP BY time_interval
ORDER BY time_interval;

注意事项

  1. your_table 替换为你的实际表名
  2. time_column 替换为包含时间戳的列名
  3. '2023-01-01' 替换为你想要查询的具体日期
  4. 300秒 = 5分钟(这是计算的基础)

高级版本:确保显示所有时间区间(包括空区间)

如果需要确保显示所有5分钟区间(即使某些区间没有数据),可以使用生成的时间序列与你的数据LEFT JOIN:

WITH RECURSIVE time_intervals AS (
    SELECT 
        CAST('2023-01-01 00:00:00' AS DATETIME) AS interval_start,
        CAST('2023-01-01 00:05:00' AS DATETIME) AS interval_end
    UNION ALL
    SELECT 
        DATE_ADD(interval_start, INTERVAL 5 MINUTE),
        DATE_ADD(interval_end, INTERVAL 5 MINUTE)
    FROM time_intervals
    WHERE interval_start < '2023-01-01 23:55:00'
)
SELECT 
    ti.interval_start,
    COUNT(y.id) AS count
FROM time_intervals ti
LEFT JOIN your_table y ON y.time_column >= ti.interval_start 
                      AND y.time_column < ti.interval_end
GROUP BY ti.interval_start
ORDER BY ti.interval_start;

这种方法会显示从00:00到23:55的所有5分钟区间,即使某些区间没有数据也会显示为0。