時シリーズの集約データ：SQLの5分間隔

時系列データの分析には、データを特定の間隔にグループ化する必要があることがよくあります。このガイドは、SQLを使用してデータを5分間隔にグループ化する方法を示しており、定義された時間枠内でデータを集約する必要があるシナリオに対処します。 この例では、「Time」と「ID」テーブルのデータを使用して、「John」という名前の発生をカウントします。 課題は、個々のタイムスタンプのグループ化から5分間の間隔集計に移行することにあります。

さまざまなデータベースシステムのソリューション

最適なアプローチは、データベースシステムによって異なります。 PostgreSQLとMySQLのソリューションは次のとおりです。

postgresql

postgresqlは、抽出物（ '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;

from_unixtime（）。。

... を特定の where 句に置き換えることを忘れないでください。 節による順序は、結果の時系列提示を保証します。