如何使用 SQL 将时间序列数据分组为 5 分钟间隔？

聚合时间序列数据：SQL 中的 5 分钟间隔

分析时间序列数据通常需要将数据分组到特定的区间。本指南演示如何使用 SQL 将数据分组为 5 分钟间隔，解决需要在定义的时间范围内聚合数据的场景。 该示例使用“time”和“id”表中的数据，计算名称“John”的出现次数。 挑战在于从单独的时间戳分组转向 5 分钟间隔聚合。

不同数据库系统的解决方案

最佳方法因您的数据库系统而异。以下是 PostgreSQL 和 MySQL 的解决方案：

PostgreSQL

PostgreSQL 提供了一种灵活的方法，使用 extract('epoch') 来获取 Unix 时间戳（自纪元以来的秒数）和 INTERVAL:

SELECT
    date_trunc('minute', timestamp) + INTERVAL '5 minutes' * (extract(minute from timestamp)::int / 5) AS five_minute_interval,
    name,
    COUNT(b.name)
FROM time a, id b
WHERE ... -- Your WHERE clause here
GROUP BY five_minute_interval, name
ORDER BY five_minute_interval;

此查询首先使用 date_trunc 将时间戳截断为分钟。然后，它在原始时间戳的分钟的基础上加上 5 分钟的倍数来计算 5 分钟间隔。

MySQL

MySQL 使用 UNIX_TIMESTAMP() 和整数除法提供了一个更简单的解决方案：

SELECT
    FROM_UNIXTIME(FLOOR(UNIX_TIMESTAMP(timestamp) / 300) * 300) AS five_minute_interval,
    name,
    COUNT(b.name)
FROM time a, id b
WHERE ... -- Your WHERE clause here
GROUP BY five_minute_interval, name
ORDER BY five_minute_interval;

此查询将时间戳转换为 Unix 时间戳，执行整数除以 300（5 分钟内的秒数），然后使用 FROM_UNIXTIME().

将结果转换回时间戳

两个查询均按计算出的 5 分钟间隔和名称对结果进行分组，从而提供所需的聚合输出。请记住将 ... 替换为您的特定 WHERE 子句。 ORDER BY 子句确保结果按时间顺序呈现。

以上是如何使用 SQL 将时间序列数据分组为 5 分钟间隔？的详细内容。更多信息请关注PHP中文网其他相关文章！