PostgreSQL でピボット テーブルを作成する

ピボット テーブルは、大規模なデータ フローを要約するために使用される、元のテーブルで既に指定されているいくつかの値を集計する傾向があります。 PostgreSQL では、これはカスタム N 値列で返されるテーブルであり、結果に要約された行のデータ型です。

今日は、既存の値を持つ定義済みのテーブルを使用して、PostgreSQL でピボット テーブルを作成する方法を学習します。

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

crosstab() は、PostgreSQL ドキュメントの TABLE_FUNC 見出しの下にあります。 複数行のテーブルを返す関数です。

構文：

crosstab ( sql text ) ? setof record

このクエリは、行名と N 値の列を含むピボット テーブルを生成します。ここで、N は、呼び出し元のクエリで指定された行の種類によって決まります。

crosstabN ( sql text ) , setof table_crosstab_N

このクエリは、行名と N 値列を含むピボット テーブルを生成します。 crosstab2、crosstab3、および crosstab4 は事前定義されています。

では、どのように使用できるか見てみましょう。 列を持つ APARTMENT という単純なテーブルを作成します。 ID、UNIT、PRICE、AREA。

例：

CREATE TABLE apartment (
   ID int PRIMARY KEY,
   UNIT int,
   PRICE int,
   AREA int
)

それでは、テーブルにいくつかの値を追加しましょう。

INSERT INTO apartment VALUES (1, 20, 200, 10) , (2, 20, 200, 9), (3, 50, 190, 8);

したがって、今のテーブルを見ると、次のようになります。

出力：

id    unit   price  area
1     20     200     10
2     20     200     9
3     50     190     8

ピボット テーブルでは、このテーブルは次のように表されます。

unit    ..180    190        200          210...
20         -      -      19/2 = 9.5       -
50         -      8         -             -

したがって、ここでは、特定の PRICES での各 UNIT の AREA の平均をとっています。 前述の PRICE で得られる平均 AREA を確認したいと考えています。

したがって、20 の UNIT に対して 200 の PRICE の平均 AREA を見つけた場合、平均は (10 + 9) / 2 = 8 として得られます。 このようにして、集計を含むピボット テーブルを見つけます。

これで概念は理解できたので、実装してみましょう。 次のようにクエリを記述できます。

Select UNIT, PRICE, avg(area)
from apartment
group by UNIT, PRICE

このクエリは、必要な基本機能を実装します。 GROUP BY 句を使用して、テーブル内の集計対象の列を取得します。

特定の UNIT と PRICE の AREA の平均を求めたいとします。 したがって、これら 2つの列を使用してグループ化します。 テーブルは次のように返されます。

出力：

unit   price        avg
50     190     8.0000000000000000
20     200     9.5000000000000000

さて、このテーブルも適切に機能しますが、先に進んで crosstab() を使用しましょう。 それがどのように機能するか、および制約を確認する必要があります。

crosstab() を使用するには、次の 2つの重要な点を確認する必要があります。

1. crosstab() は、行ごとに異なる値を持つ必要があります。
2. crosstab() は、各列のデータ型が同じである必要があります。

そのため、列のデータ型が異なることを確認してください。 さて、この GROUP BY クエリからの結果をピボットするために、次のように書きましょう。

select *
from crosstab
(
'Select UNIT::float, PRICE::float, avg(AREA)::float
from apartment
group by UNIT, PRICE') as ct(
    UNIT float,
    avge float
);

ここで考慮すべき重要な点がいくつかあります。 UNIT と avge の 2つの列で定義されているように、結果テーブル CT として返された crosstab から列を選択します。

グループ化後、APARTMENT から列を取得し、平均を見つけます。 このクエリ テーブルは、最終結果のためにピボットされます。

また、各列を同じデータ型に確実にキャストしていることにも注意してください。 もちろん、平均は倍精度または浮動小数点のいずれかになります。

したがって、たとえ不要であっても、int 列を float にキャストすることをお勧めします。 キャストせずにクエリを実行すると、次のようなエラーが返されます。

出力：

ERROR:  return and sql tuple descriptions are incompatible
SQL state: 42601

したがって、これを回避するために、float にキャストするか、float 型で値が挿入された新しいテーブルを作成するようにします。 タプルに互換性がないのは、データ型の違いです。

出力：

unit    avg
50       8
20      9.5

それ以外の場合は、crosstab() を使用する必要はありません。 GROUP BY を使用しても、平均を取得するための一意の値を持つテーブルが返されるため、効果的です。

crosstab() 関数を使用しなくても、次のような結果が得られます。

unit   price   avg
50     190      8
20     200     9.5

crosstab() で、必要に応じて 3つの列を持つ戻りリストを使用しても、UNIT と area の平均を含む値だけが返されることに気付くでしょう。 なぜ？

SQL ステートメントは一連のカテゴリと値を取り、この場合、カテゴリは UNIT になり、値は平均列になる傾向があります。 したがって、返されるテーブルには 2つの列のみが保持されます。

crosstab() がどのように機能するかがわかったので、これを使用できます。 異なる PRICES を持つ UNITS があり、次に AVG() を取得した場合、データに応じて N 個の列を持つことになります。

テーブルの値を次のように変更しましょう。

id    unit   price  area
1      20    200     10
2      20    170     9
3      50    190     8

これに対して crosstab() クエリを実行すると、次のようなものが返されます。

出力：

unit   avge   avge1
50      8    [NULL]
20     10      9

PRICE を変更すると、AREA 値が異なる平均値に分散する傾向があることがわかります。 これは、平均も価格でグループ化するために発生します。

同じ PRICE 値は、それらすべてに存在する場合、異なる値の平均を持ちますが、他の PRICE 値は、各 PRICE に対応する AREAS の平均のみで構成されます。 したがって、テーブルはこのようにソートされます。