Crosstab() 関数を使用して PostgreSQL でピボット テーブルを作成する方法

PostgreSQL とcrosstab() 関数を使用してピボット テーブルを作成する

データ分析では、さまざまな変数間の関係を調査することがよくあります。ピボット テーブルは、データを要約して表示するための強力なツールであり、傾向やパターンを簡単に特定できます。この記事では、PostgreSQL を使用してピボット テーブルを作成する方法を説明します。

質問:

PostgreSQL に、次の列を持つ listings という名前のテーブルがあるとします:

• id
• neighborhood(近所)
• bedrooms(寝室の数)
• price(価格)

寝室数を列、近隣地域を行として、各近隣地域の平均価格を表示するクロス集計クエリを作成する必要があります。出力は次の形式である必要があります:

街区	0	1	2	3
市中心	189000	325000	-	450000
河景	250000	300000	350000	-

解決策:

PostgreSQL でピボット テーブルを作成するには、AVG() 集計関数を、tablefunc モジュールが提供する crosstab() 関数と組み合わせて使用​​できます。ステップバイステップのガイドは次のとおりです:

1. 寝室数ごとの平均価格を計算します:

<code class="language-sql">SELECT neighborhood, bedrooms, AVG(price)
FROM listings
GROUP BY 1, 2
ORDER BY 1, 2;</code>

2. クロス集計クエリを作成します:

<code class="language-sql">SELECT *
FROM crosstab(
   'SELECT neighborhood, bedrooms, AVG(price)::INT
    FROM listings
    GROUP BY 1, 2
    ORDER BY 1, 2;'

  , $$SELECT UNNEST('{0,1,2,3}'::INT[])::TEXT$$
   ) AS ct ("neighborhood" TEXT, "0" INT, "1" INT, "2" INT, "3" INT);</code>

説明:

• 最初のクエリは、各地区の寝室数ごとの平均価格を計算します。
• crosstab() 関数は、最初のクエリの結果を入力として受け取り、ピボット テーブルを作成します。 crosstab() 関数に渡される文字列は、ヘッダーに配置される列の値を指定します。
• ::INT キャストは、出力で丸められた値を取得するために、AVG() の結果を整数に変換するために使用されます。

追加メモ:

• tablefunc 機能を使用するには、crosstab() モジュールをインストールする必要があります。
• 集計関数で FILTER 句を使用してピボット テーブルを作成することもできますが、一般に crosstab() 関数よりも遅くなります。
• PostgreSQL には他にも、ピボット テーブルの作成に使用できるさまざまなメソッドや拡張機能があります。特定のニーズに最適なソリューションを見つけるには、ドキュメントを参照してください。

以上がCrosstab() 関数を使用して PostgreSQL でピボット テーブルを作成する方法の詳細内容です。詳細については、PHP 中国語 Web サイトの他の関連記事を参照してください。