[解決済み】PostgreSQL クロスタブクエリ

2022-04-02 09:35:36

質問

PostgreSQLでクロスタブ（crosstab）クエリを作成する方法を知っている人はいますか？

例えば、次のようなテーブルがあります。

Section    Status    Count
A          Active    1
A          Inactive  2
B          Active    4
B          Inactive  5

私は、クエリが次のクロスタブを返すようにしたいです。

Section    Active    Inactive
A          1         2
B          4         5

これは可能か？

どのように解決するのですか？

をインストールします。追加モジュール tablefunc 一度という機能を提供する、データベースごとに crosstab() . Postgres 9.1 以降は CREATE EXTENSION を使用します。

CREATE EXTENSION IF NOT EXISTS tablefunc;

テストケースの改善

CREATE TABLE tbl (
   section   text
 , status    text
 , ct        integer  -- "count" is a reserved word in standard SQL
);

INSERT INTO tbl VALUES 
  ('A', 'Active', 1), ('A', 'Inactive', 2)
, ('B', 'Active', 4), ('B', 'Inactive', 5)
                    , ('C', 'Inactive', 7);  -- ('C', 'Active') is missing

シンプルなフォーム - 属性の欠落には適合しない

crosstab(text) と 1 入力パラメータです。

SELECT *
FROM   crosstab(
   'SELECT section, status, ct
    FROM   tbl
    ORDER  BY 1,2'  -- needs to be "ORDER BY 1,2" here
   ) AS ct ("Section" text, "Active" int, "Inactive" int);

を返します。

 セクション｜アクティブ｜インアクティブ
---------+--------+----------
 A | 1 | 2
 B | 4 | 5
 C |      

7

 | -- !!

キャストやリネームが不要です。
注 不正確 の結果は C : 値 7 が最初の列に対して記入されます。この動作が望ましい場合もあるが、今回のユースケースには当てはまらない。
また、シンプルなフォームの場合、制限されるのは まさに 入力クエリに含まれる 3 つのカラムを指定します。行名 , カテゴリ , 値 . が入る余地はありません。 余分なカラム のように、2パラメータにすることもできます。

安全なフォーム

crosstab(text, text) と 2 入力パラメータを指定します。

SELECT *
FROM   crosstab(
   'SELECT section, status, ct
    FROM   tbl
    ORDER  BY 1,2'  -- could also just be "ORDER BY 1" here

  , $$VALUES ('Active'::text), ('Inactive')$$
   ) AS ct ("Section" text, "Active" int, "Inactive" int);

を返します。

 セクション｜アクティブ｜インアクティブ
---------+--------+----------
 A | 1 | 2
 B | 4 | 5
 C | |        

7

  -- !!

の正しい結果に注意してください。 C .
は 第2パラメータ を返す任意のクエリにすることができます。行属性ごとに、末尾のカラム定義の順序と一致する。多くの場合、このように基礎となるテーブルから個別の属性を問い合わせることになります。
```
  'SELECT DISTINCT attribute FROM tbl ORDER BY 1'
```

それはマニュアルに書いてありますよ。

どうせ列の定義リストにはすべての列を明記しなければならないので（あらかじめ定義された crosstabN() バリアント) で短いリストを提供する方が一般的に効率的です。 VALUES のような表現になります。

    $$VALUES ('Active'::text), ('Inactive')$$)

または（マニュアルにない）。

    $$SELECT unnest('{Active,Inactive}'::text[])$$  -- short syntax for long lists

を使いました。 ドルクオート を追加し、引用を容易にしました。
でカラムを出力することもできます。 異なる データ型と crosstab(text, text) - 値列のテキスト表現がターゲット型にとって有効な入力である限り、です。このようにして、異なる種類の属性を持ち、出力することができる。 text , date , numeric など、それぞれの属性に対応します。の末尾にコード例があります。章 crosstab(text, text) 取扱説明書中 .

db<>フィドルこちら

入力行の超過による影響

同じ ("row_name", "category") の組み合わせの行が重複している - 余分な入力行は別の方法で処理される - ということです。 (section, status) 上記の例では

は 1-パラメータ フォームは、利用可能な値の列を左から右へ埋めていきます。余分な値は破棄されます。

早く入力された行が勝ちです。

は 2パラメータ フォームでは、各入力値が専用のカラムに割り当てられ、以前の割り当てがすべて上書きされます。

後から入力した行が勝ち。

一般的には、最初から重複していることはありません。しかし、もしそうなら、ソート順を慎重に調整し、何が起こっているかを記録してください。

あるいは、気にしないのであれば、早く任意の結果を得ることです。ただ、その影響には注意しましょう。

応用例

Tablefuncを使った複数列でのピボット処理 - また、quot;extra columns"のデモも行っています。
CASEとGROUP BYを使用したピボットの動的な代替手段

`\crosstabview` psqlで

ポストグレス 9.6 デフォルトの対話型ターミナルにこのメタコマンドを追加しました。 psql . 最初に使用するクエリを実行することができます。 crosstab() パラメータに与え、それを \crosstabview (すぐに、または次のステップで)。のように。

db=> SELECT section, status, ct FROM tbl \crosstabview

上記と似たような結果ですが、これは クライアント側での表現機能 を排出しています。入力行は若干異なる扱いを受けるので、そのため ORDER BY は必要ありません。の詳細 \crosstabview をマニュアルに記載しています。そのページの下のほうに、さらにコード例があります。

Daniel Vérité (psql機能の作者)によるdba.SEでの関連回答です。

結果のテーブル定義が不明な場合、ピボットCROSS JOINを生成するにはどうすればよいですか？

[解決済み】PostgreSQL クロスタブ クエリ

質問