MySQL のピボットテーブル
-
MySQL で
MAX関数とIFステートメントを使用してピボットテーブルを作成する -
MySQL で
CASEステートメントを使用してピボットテーブルを作成する - MySQL で動的ピボット列を使用してピボットテーブルを作成する
この記事では、テーブルデータを行から列に変換する方法について説明します。このプロセスはピボットと呼ばれます。この変換の結果は通常、レポート生成に適した情報を提示する要約テーブルです。
MySQL には、ピボットテーブルを作成するための組み込み関数がないため、ピボットテーブルを生成するために MySQL クエリを作成する必要があります。幸い、MySQL を使用してピボットテーブルを作成するには、3つの異なる方法があります。
IFステートメントを使用して MySQL でピボットテーブルを作成しますCASEステートメントを使用して MySQL でピボットテーブルを作成します- 動的ピボット列を使用して MySQL でピボットテーブルを作成します
次のスクリプトは、3つの列（name、subjectid、および marks）を持つテーブル student を作成します。
create table student(name varchar(20),subjectid int(10), marks int(10));
ここでは、デモンストレーションのためにサンプルデータ値を student テーブルに挿入します。
insert into student values ('Sam',1,70);
insert into student values ('Sam',2,77);
insert into student values ('Sam',3,71);
insert into student values ('Sam',4,70);
insert into student values ('Sam',1,99);
insert into student values ('John',1,89);
insert into student values ('John',2,87);
insert into student values ('John',3,88);
insert into student values ('John',4,89);
insert into student values ('Martin',1,60);
insert into student values ('Martin',2,47);
insert into student values ('Martin',3,68);
insert into student values ('Martin',4,39);
これらの値を挿入した後、データベース内のレコードを表示するための select クエリを記述しましょう。
Select * from student;
出力は次のようになります。
MySQL で MAX 関数と IF ステートメントを使用してピボットテーブルを作成する
これは、問題に対する非常に簡単なアプローチです。このアプローチでは、MAX 関数と IF ステートメントを使用します。1つの科目の複数のエントリがデータベース内の学生に存在する場合、最大 2つのマークが必要になります。
SELECT クエリでこれを使用する方法を見てみましょう。
SELECT name,
MAX(IF(subjectid=1, marks, NULL)) AS Sub1,
MAX(IF(subjectid=2, marks, NULL)) AS Sub2,
MAX(IF(subjectid=3, marks, NULL)) AS Sub3,
MAX(IF(subjectid=4, marks, NULL)) AS Sub4
FROM student
GROUP BY name;
出力は次のようになります。
この出力は、各学生の科目ごとにまとめられた結果です。生徒ごとに 1つの行が必要なので、名前の列でグループ化する必要があります。また、列ごとに 1つの条件、つまり、サブジェクトごとに 1つの条件を指定する必要があります。
MySQL で CASE ステートメントを使用してピボットテーブルを作成する
これは、問題への簡単なアプローチでもあります。このアプローチでは、MAX 関数を CASE ステートメントと一緒に使用します。また、データベース内の学生に 1つの科目の複数のエントリが存在する場合、最大 2つのマークが付けられます。同様に、一部の生徒にマークが存在しない場合は、NULL 値を取ります。
SELECT クエリでこれを使用する方法を見てみましょう。
SELECT name,
MAX(CASE WHEN subjectid=1 THEN marks ELSE NULL END) AS Sub1,
MAX(CASE WHEN subjectid=2 THEN marks ELSE NULL END) AS Sub2,
MAX(CASE WHEN subjectid=3 THEN marks ELSE NULL END) AS Sub3,
MAX(CASE WHEN subjectid=4 THEN marks ELSE NULL END) AS Sub4
FROM student
GROUP BY name;
出力は次のようになります。
MySQL で動的ピボット列を使用してピボットテーブルを作成する
集計関数（MAX）、IF ステートメント、および CASE ステートメントは、上記の例のピボットテーブルを生成しました。このアプローチを使用することの欠点は、クエリの記述中に列見出しを知る必要があることです。列の数が増えると、コードも増えます。上記の両方のアプローチで、より小さな結果とすべての可能な値を得ることができます。
したがって、これらの制限を克服するために、動的ピボット列を使用できます。ここで、GROUP_CONCAT 関数は、PIVOT テーブル出力の列を動的に生成できます。
SET @sql = NULL;
SELECT
GROUP_CONCAT(DISTINCT CONCAT(
' MAX(CASE WHEN subjectid = ', subjectid, ' THEN marks ELSE 0 END)
AS "', subjectid, '"')
)
INTO @sql FROM student;
SET @sql = CONCAT('SELECT name, ', @sql,
' FROM student GROUP BY name');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
GROUP_CONCAT 関数では、DISTINCT キーワードを使用して、マークの一意のリストを取得します。そうしないと、リストが長すぎるため、クエリでエラーが発生します。Select * from @sql と書くと、いつでも動的クエリをチェックできます。
このクエリを実行すると、結果として次のようになります。
列ヘッダーはテーブルの値に基づいて動的に生成され、列ヘッダーは subjectid を表すことに注意してください。