PostgreSQL で列を行に転置する

1. 列を行に転置
2. PostgreSQL でテーブルを作成する
3. crosstab() 関数を使用して、PostgreSQL で列を行に転置する
4. unnest() 関数を使用して、PostgreSQL で列を行に転置する
5. まとめ

この記事は、入力で提供された列を使用して行に変換する方法について説明するために特別にまとめられています。

データを異なる形式で表示する必要がある場合が複数あります。 この記事では、データを通常の列から行に変換する方法について説明します。

記事全体を要約すると、解決が必要な問題は 1つのセクションで説明されています。 次に、この問題を解決するために、crosstab() と unnest() の 2つのメソッドが導入されました。

最後に、両方の方法をよりよく理解できるように、各方法の例について説明します。

列を行に転置

この記事で議論されている問題は、列を行に転置する方法です。 たとえば、次の表があります。

Std_no.	Name	Degree
  1		 A		  W
  2 	 B        X
  3  	 C        Y
  4  	 D        Z

この記事では、テーブルの列を行に変換する方法について説明します。 したがって、予想される解決策は次のようになります。

Std_no.		1		2		3		4
Name		A		B		C		D
Degree		W		X		Y		Z

この記事では、列を行に変換する手順について説明しています。 入力は、テーブルの列または配列の形式にすることができます。

この記事では、両方の可能性に対応しています。

PostgreSQL でテーブルを作成する

記事全体を通して理解しやすくするために、同じ表と値が使用されます。 これにより、さまざまなスキーマに時間を費やすのではなく、機能を理解することに焦点が移ります。

次のクエリは、テーブルを定義するために使用されます。

CREATE TABLE Student(Std_no int, StdName TEXT, StdDegree TEXT, CourseName TEXT);

テーブルが正常に作成されると、8つのレコードがテーブルに挿入されます。 記録は次のとおりです。

INSERT INTO Student(StdName, StdDegree, CourseName)
VALUES
('test1','att1','val1'),
('test1','att2','val2'),
('test1','att3','val3'),
('test1','att4','val4'),
('test2','att1','val5'),
('test2','att2','val6'),
('test2','att3','val7'),
('test2','att4','val8');

テーブルが作成され、レコードが追加されると、SELECT ステートメントを使用してテーブルの現在の状態を表示できます。

Select * from Student;

テーブルのスナップショットは次のとおりです。

これで、PostgreSQL で列を行に転置するのに役立つ 2つの関数の定義と使用法に移ることができます。

crosstab() 関数を使用して、PostgreSQL で列を行に転置する

crosstab() 関数とは何かについて説明しましょう。

この関数は、PostgreSQL バージョン 9.7 以降でサポートされています。 さまざまな引数を使用して、いくつかの方法で使用できます。

crosstab() 関数の構文は次のとおりです。

1. クロス集計(SQLテキスト)
2. クロスタブN(SQLテキスト)
3. crosstab(source_sql テキスト, category_sql テキスト)
4. crosstab(sql text, N integer)

各引数の動作と説明は、PostgreSQL のドキュメントで詳しく説明されています。

この記事では、crosstab(sql text) のみが必要です。 crosstab(sql text) の引数は SQL クエリです。

crosstab() 関数の仕組み

このセクションでは、crosstab() 関数の動作に焦点を当てます。 crosstab() 関数の概念を理解するために例を使用します。

crosstab() 関数を使用して作成されたクエリを次に示します。

SELECT *
FROM crosstab(
  'select StdName, StdDegree, CourseName
   from Student
   where StdDegree = 'att2'
   or StdDegree = 'att3'
   order by 1,2')
AS Student(row_name text, category_1 text, category_2 text, category_3 text);

これは、ネストされたクエリの概念を使用すると理解しやすくなります。 引用符で囲まれたクエリは、crosstab() 関数の引数としても機能する内部クエリです。 最初に実行されます。

引数のクエリは、StdDegree の値が att2 または att3 であるレコードを返します。 これは、見出し row_name、category_1、category_2、および category_3 を持つ crosstab() 関数によって示されます。

この例の結果は次のとおりです。

unnest() 関数を使用して、PostgreSQL で列を行に転置する

unnest() 関数について説明しましょう。 unnest() 関数は、PostgreSQL サーバーのバージョン 9.7 以降でサポートされている配列関数です。

サーバーのドキュメントに記載されている PostgreSQL には、他にも多数の配列関数が用意されています。

unnest() 関数の構文は次のとおりです。

unnest(anyarray)

unnest() 関数は、引数として配列または配列のセットを取ります。 配列を個別のレコードに展開し、結果をユーザーに表示します。

unnest() 関数は、1 次元配列と多次元配列をサポートします。

unnest() 関数の仕組み

このセクションでは、unnest() 関数の動作に焦点を当てます。 unnest() 関数の概念を理解するために例を使用します。

unnest() 関数は 2つの異なる方法で使用できます。 例 1 は、列を行に転置するために unnest() を使用できる最初の方法を示しています。

SELECT unnest('{Std_no, StdName, StdDegree, CourseName}'::text[]) AS col
	, unnest('{1,test1,att1,val1}'::text[]) AS row1
	, unnest('{2,test1,att2,val2}'::text[]) AS row2
	, unnest('{3,test1,att3,val3}'::text[]) AS row3
	, unnest('{4,test1,att4,val4}'::text[]) AS row4;

クエリは、引数として配列を受け取り、個別のレコードを返す unnest() 関数を使用します。 したがって、クエリの結果は列を行に置き換えます。

例 1 で説明したクエリは、わずかに異なる形式で記述できます。 どちらの例にも、列を行に変換する同じタスクを実行する同様のクエリがあります。

例 1 で説明したクエリの別の形式を次に示します。

SELECT * FROM unnest
    (
     '{Std_no, StdName, StdDegree, CourseName}'::text[]
     ,'{1,test1,att1,val1}'::text[]
     ,'{2,test1,att2,val2}'::text[]
     ,'{3,test1,att3,val3}'::text[]
     ,'{4,test1,att4,val4}'::text[]
	)
 AS t(col,row1,row2,row3,row4);

2つの例の違いを見ることができます。 例 1 で説明した最初のクエリでは、各 unnest() 関数が 1 次元配列に対応していました。

例 1 では、各 unnest() 関数に個別のヘッダーがあります。

この例では、多次元配列に対応する unnest() 関数が 1つだけ使用されています。 新しい各列のヘッダーは最後に記載されています。

このクエリは、例 1 と同じ結果を生成します。

まとめ

さまざまな関数と SQL ステートメントをテストして、列を行に転置できます。 転置すると、各行が新しい列に変換されます。

この動的な結果により、単一のクエリで列を行に転置することが困難になります。 したがって、このような問題を解決するために crosstab() および unnest() 関数が使用されます。