PostgreSQL で動的時間間隔内のイベントを効率的にカウントするにはどうすればよいですか?

時間間隔ごとにイベントを効率的にカウントする方法

はじめに

アプリケーションでは、特定の範囲内のイベントをカウントするなど、イベントベースのデータから洞察を抽出することが必要になることがよくあります。時間間隔。このタスクは、間隔サイズが動的に変化する場合に問題が発生します。

問題ステートメント

タイムスタンプ付きのイベントを含むデータ テーブルには、毎週、日次、週次などの任意の時間間隔でイベントをグループ化する動的 SQL クエリが必要です。 1 時間ごと、または 15 分間隔です。

ソリューション

Postgres 14 または新しい

Postgres バージョン 14 以降では、date_bin() 関数が簡単な解決策を提供します。データを含むタイムスロットごとに 1 行を取得するには、次のクエリを使用します。

SELECT date_bin('15 min', e.ts, '2018-05-01') AS start_time
     , count(e.ts) AS events
FROM   event e
GROUP  BY 1
ORDER  BY 1;

特定の日付以降のすべてのタイムスロットを取得するには、クエリを次のように調整します。

SELECT start_time, COALESCE(events, 0) AS events
FROM  (
   SELECT generate_series(timestamp '2018-05-01', max(ts), interval '15 min')
   FROM   event
   ) g(start_time)
LEFT   JOIN (
   SELECT date_bin('15 min', e.ts, '2018-05-01'), count(e.ts)
   FROM   event e
   WHERE  e.ts >= '2018-05-01'  -- filter early (optional)
   GROUP  BY 1
   ) e(start_time, events) USING (start_time)
ORDER  BY 1;

Postgres 13 以降

古い Postgres バージョンの場合、次のクエリは次のようになります。使用:

WITH grid AS (
   SELECT start_time
        , lead(start_time, 1, 'infinity') OVER (ORDER BY start_time) AS end_time
   FROM  (
      SELECT generate_series(min(ts), max(ts), interval '17 min') AS start_time
      FROM   event
      ) sub
   )
SELECT start_time, count(e.ts) AS events
FROM   grid       g
LEFT   JOIN event e ON e.ts >= g.start_time
                   AND e.ts <  g.end_time
GROUP  BY start_time
ORDER  BY start_time;

このクエリはタイム スロットごとに 1 行を提供し、イベントのないスロットのカウントは 0 になります。

最適化

間隔データを事前に保存イベント テーブルによりパフォーマンスが向上します。ただし、このアプローチではテーブル サイズが 2 倍になります。トレードオフが許容できる場合は、速度を大幅に向上させることができます。

追加の考慮事項

• 時間の上限と下限を正しく処理します。
• to_char を使用します。 () 関数を使用して、表示目的でタイムスタンプをフォーマットします。
• generate_series() 関数を使用して完全なセットを生成します。タイムスロットの数。

以上がPostgreSQL で動的時間間隔内のイベントを効率的にカウントするにはどうすればよいですか?の詳細内容です。詳細については、PHP 中国語 Web サイトの他の関連記事を参照してください。