tablefunc 拡張機能を使用して PostgreSQL でクロス集計クエリを作成する方法

PostgreSQL クロステーブル クエリの詳細な説明: tablefunc 拡張機能を使用してピボット テーブルを作成する

この記事では、tablefunc 拡張機能を使用して PostgreSQL でクロステーブル クエリ (クロスタブ クエリ) を作成し、データのピボット テーブル変換を実装する方法を詳しく紹介します。

クロステーブルクエリの作成

クロステーブル クエリは、データを表形式に変換します。行はカテゴリを表し、列は値を表します。 PostgreSQL は、tablefunc 拡張機能を通じてこの機能を実装します。

ダブルパラメータのクロステーブルクエリ構文:

<code class="language-sql">SELECT *
FROM crosstab(
  'SELECT row_name, category, value
   FROM base_table
   ORDER BY 1, 2',
  'SELECT DISTINCT attribute FROM base_table ORDER BY 1',
) AS ct (row_name text, column_1 type_1, ..., column_n type_n);</code>

欠落している属性の処理:

ベース テーブルに欠落している属性がある場合は、2 番目のパラメータを使用してクロステーブルに含める属性を指定できます。欠落している属性の値は空になります。

冗長入力行の処理:

• 単一パラメータ形式: 余分な行は、古い行から順に破棄されます。
• 2 つの引数形式: 以降の行は、同じカテゴリと属性の組み合わせの既存の値を上書きします。

高度なクロステーブルクエリ:

• 複数列ピボット: 最初のパラメーター クエリで複数の ORDER BY 句を使用します。
• 動的ピボットの代替: CASE および GROUP BY ステートメントを使用します。

psql で crosstabview を使用します (PostgreSQL 9.6 以降):

psql で crosstabview メタ コマンドを使用して、テーブル間のクエリを実行します。

<code class="language-sql">\crosstabview</code>

クエリの例:

次のテーブルの例を考えてみましょう:

<code class="language-sql">Section    Status    Count
A          Active    1
A          Inactive  2
B          Active    4
B          Inactive  5</code>

行としてセクション、列としてステータスを含むクロステーブルを作成するには:

<code class="language-sql">SELECT *
FROM crosstab(
  'SELECT section, status, COUNT(*) FROM tbl GROUP BY 1, 2',
  'SELECT DISTINCT status FROM tbl ORDER BY 1',
) AS ct (Section text, Active int, Inactive int);</code>

結果:

<code>Section    Active    Inactive
A          1         2
B          4         5</code>

以上がtablefunc 拡張機能を使用して PostgreSQL でクロス集計クエリを作成する方法の詳細内容です。詳細については、PHP 中国語 Web サイトの他の関連記事を参照してください。