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

時間間隔による行のカウント: 最良のアプローチを明らかにする

アプリケーションは多くの場合、タイムスタンプ付きのイベントを処理し、特定の時刻に基づいてデータを分析する必要があります。間隔。各間隔内のイベントの数を効果的にカウントするために、開発者はさまざまなアプローチを検討することがあります。

1.任意の間隔の動的 SQL クエリ

SQL クエリを使用して、任意の時間間隔でイベントをグループ化することは、効率的で推奨される方法です。ただし、Postgres はこの機能をネイティブにサポートしていません。

2.時間ソートを使用したブルート フォース メソッド

開始/終了時間枠内のすべてのレコードをクエリし、必要な間隔で手動で集計することは、単純ですが面倒なアプローチです。結果をタイムスタンプで並べ替える必要があり、動的な間隔のカスタマイズがありません。

3.間隔データの事前保存

イベント テーブルの間隔ごとに個別のフィールドを維持すると、実行時の計算を回避し、レポートを簡素化できます。ただし、この方法ではテーブル サイズが 2 倍になるため、最適ではない可能性があります。

ベスト プラクティス

ベスト プラクティスは、アプリケーションの特定の要件とデータ サイズによって異なります。

• かなりの数の間隔または任意の間隔のカスタマイズの場合、動的SQLクエリが適しています。 Postgres には組み込みのサポートがありませんが、lead() のようなウィンドウ関数を使用する回避策が存在します。
• 少数の固定間隔 (日や週など) の場合、 間隔データを事前に保存するクエリの実行は簡素化できますが、テーブル サイズは増加します。
• リアルタイム ソリューションの場合、またはテーブルの増加が問題となる場合懸念事項として、 時間ソートを使用した総当り方式の方が適切である可能性があります。

Postgres 14 と date_bin() 関数

Postgres 14 以降の場合、 date_bin() 関数を使用すると、タイムスタンプを間隔に直接グループ化できます。これにより、クエリが簡素化され、効率的な結果が得られます。

例: 15 分間隔でイベントをカウントする

指定された時間範囲内で 15 分間隔でイベントをカウントするにはdate_bin() を使用するfunction:

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 以前では、generate_series() および window 関数を使用した代替アプローチが利用可能です。

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