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

時間間隔によるイベントの効率的なカウント

問題ステートメント

タイムスタンプ付きイベントを含むイベント テーブルを考えてみましょう。目的は、日、時間、またはユーザー定義の間隔など、特定の時間間隔内に発生したイベントの数をレポートすることです。目標は、Postgres の単一 SQL クエリを通じてこの情報を動的に生成するための最も効率的なアプローチを決定することです。

解決策

Date_bin() の使用 (Postgres) 14 以降)

Postgres 14 では、 「date_bin()」関数は、この問題に対する直接的な解決策を提供します:

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;

完全な行セットの生成 (Postgres 13 以降)

以前の場合Postgres のバージョンでは、次のクエリはタイム スロットの完全なセットを生成し、LEFT JOIN を実行して各タイム スロット内のイベントをカウントします。間隔:

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;

考慮事項

• 「generate_series()」には Postgres 9.6 以降が必要です。
• 大規模なデータセットの場合、テーブルに別の「間隔」フィールドを作成し、時間間隔の値を事前に保存すると、パフォーマンスが向上します。ただし、追加のストレージ容量が必要です。
• 時間間隔を指定する場合は、「interval '17 min」などの適切な Postgres 構文を使用します。
• 「BETWEEN」演算子を使用してフィルタリングできます。特定の時間範囲内のイベントを処理しますが、上限と下限を適切に処理するようにしてください。
• 「to_char()」を使用してフォーマットします。 start_time 列は、ORDER BY 操作に影響を与えずに表示する目的で使用されます。

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