PostgreSQL で 1 分あたりの実行行数を効率的に計算するにはどうすればよいですか?

PostgreSQLにおける1分あたりの行数の効率的な計算方法

行数を取得

SELECT COUNT(id) AS count,
       EXTRACT(hour FROM "when") AS hour,
       EXTRACT(minute FROM "when") AS minute
FROM mytable
GROUP BY hour, minute;

このクエリは 1 分あたりの行数をカウントしますが、累計は提供しません。

実行中のカウントを取得

方法 1: アクティビティ記録のある分のみを返します

SELECT DISTINCT
       date_trunc('minute', "when") AS minute,
       count(*) OVER (ORDER BY date_trunc('minute', "when")) AS running_ct
FROM   mytable
ORDER  BY 1;

このクエリは、date_trunc() 関数を使用して、アクティブな分ごとの行数を返します。 ORDER BY 句を含むウィンドウ関数を使用して、実行の合計数を計算します。

アクティビティのない分が含まれます

方法 2: join でサブクエリを使用する

SELECT minute, sum(minute_ct) OVER (ORDER BY minute) AS running_ct
FROM  (
   SELECT date_trunc('minute', "when") AS minute,
          count(*) AS minute_ct
   FROM   tbl
   GROUP  BY 1
   ) sub
ORDER  BY 1;

このメソッドは、サブクエリ内の 1 分あたりの行数を集計します。次に、メイン クエリがこれを結合してカウントを蓄積し、アクティビティのない分も含めます。

方法 3: CTE を使用する (最速)

WITH cte AS (
   SELECT date_trunc('minute', "when") AS minute, count(*) AS minute_ct
   FROM   tbl
   GROUP  BY 1
   ) 
SELECT m.minute,
       COALESCE(sum(cte.minute_ct) OVER (ORDER BY m.minute), 0) AS running_ct
FROM  (
   SELECT generate_series(min(minute), max(minute), interval '1 min')
   FROM   cte
   ) m(minute)
LEFT   JOIN cte USING (minute)
ORDER  BY 1;

このメソッドは、CTE、サブクエリ、左結合を組み合わせたものです。これは、「when」インデックスを持つ大規模なデータ セットにとって効率的なアプローチです。

以上がPostgreSQL で 1 分あたりの実行行数を効率的に計算するにはどうすればよいですか?の詳細内容です。詳細については、PHP 中国語 Web サイトの他の関連記事を参照してください。