如何使用 SQL 將時間序列資料分組為 5 分鐘間隔？

聚合時間序列資料：SQL 中的 5 分鐘間隔

分析時間序列資料通常需要將資料分組到特定的區間。本指南示範如何使用 SQL 將資料分組為 5 分鐘間隔，以解決需要在定義的時間範圍內聚合資料的場景。 此範例使用「time」和「id」表中的數據，計算名稱「John」的出現次數。 挑戰在於從單獨的時間戳分組轉向 5 分鐘間隔聚合。

不同資料庫系統的解決方案

最佳方法因您的資料庫系統而異。以下是 PostgreSQL 和 MySQL 的解決方案：

PostgreSQL

PostgreSQL 提供了一種靈活的方法，使用 extract('epoch') 來取得 Unix 時間戳記（自紀元以來的秒數）和 INTERVAL:

<code class="language-sql">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;</code>

此查詢首先使用 date_trunc 將時間戳截斷為分鐘。然後，它在原始時間戳的分鐘的基礎上加上 5 分鐘的倍數來計算 5 分鐘間隔。

MySQL

MySQL 使用 UNIX_TIMESTAMP() 和整數除法提供了一個更簡單的解：

<code class="language-sql">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;</code>

此查詢將時間戳記轉換為 Unix 時間戳，執行整數除以 300（5 分鐘內的秒數），然後使用 FROM_UNIXTIME().

將結果轉換回時間戳

兩個查詢均以計算出的 5 分鐘間隔和名稱對結果進行分組，從而提供所需的聚合輸出。請記得將 ... 替換為您的特定 WHERE 子句。 ORDER BY 子句確保結果以時間順序呈現。

以上是如何使用 SQL 將時間序列資料分組為 5 分鐘間隔？的詳細內容。更多資訊請關注PHP中文網其他相關文章！