SQL でクロス集計クエリを効率的に実行するにはどうすればよいですか?

CASE と GROUP BY を使用して PIVOT を動的に置き換えます

質問:

以下の表に示されているデータは、行と列で構成されています。目標は、これを動的な列数を持つテーブルに変換することです。各列は、指定されたカテゴリによってグループ化された値を表します。

id	feh	bar
1	10	A
2	20	A
3	3	B
4	4	B
5	5	C
6	6	D
7	7	D
8	8	D

期待される出力:

bar	val1	val2	val3
A	10	20
B	3	4
C	5
D	6	7	8

元のクエリ:

次のクエリは CASE 式と GROUP BY を使用して、目的の結果を実現します。

<code class="language-sql">SELECT bar, 
MAX(CASE WHEN abc."row" = 1 THEN feh ELSE NULL END) AS "val1",
MAX(CASE WHEN abc."row" = 2 THEN feh ELSE NULL END) AS "val2",
MAX(CASE WHEN abc."row" = 3 THEN feh ELSE NULL END) AS "val3"
FROM
(
  SELECT bar, feh, row_number() OVER (partition by bar) as row
  FROM "Foo"
 ) abc
GROUP BY bar </code>

効率的なクロス集計の代替案:

効率と読みやすさを向上させるために、tablefunc モジュールのクロス集計関数を使用して動的ソリューションを実装できます。以下に例を示します:

<code class="language-sql">SELECT * FROM crosstab(
  'SELECT bar, 1 AS cat, feh
   FROM tbl_org
   ORDER BY bar, feh')
 AS ct (bar text, val1 int, val2 int, val3 int);  -- 更多列？</code>

複数の値の処理:

同じカテゴリに複数の値があるシナリオの場合、クロス集計関数は次の形式に拡張できます:

<code class="language-sql">SELECT * FROM crosstab(
  'SELECT bar, val, feh
   FROM tbl_org
   ORDER BY 1, 2')
 AS ct (bar text, val1 int, val2 int, val3 int);  -- 更多列？</code>

組み込みクロス集計関数:

tablefunc モジュールは、特定の数の列に対して事前定義されたクロス集計関数も提供します。

<code class="language-sql">SELECT * FROM crosstab3('SELECT row_name, attrib, val FROM tbl ORDER BY 1,2');</code>

これらの関数は呼び出しを簡単にし、デフォルトでテキスト データを処理します。

動的戻り値の型:

tablefunc はプロセスを簡素化しますが、動的な戻り値の型の処理には制限があります。この問題を解決するには、PL/pgSQL 関数の使用や動的 SQL ステートメントの作成など、他の方法が検討できます。

以上がSQL でクロス集計クエリを効率的に実行するにはどうすればよいですか?の詳細内容です。詳細については、PHP 中国語 Web サイトの他の関連記事を参照してください。