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 中国語 Web サイトの他の関連記事を参照してください。