OracleのPIVOTによる行列変換

PivotとUnPivotはOracle11gR1の新機能で、select文での行列変換を容易に行うことができます。
図でイメージするOracle DatabaseのSQL全集第8回 PivotとUnPivot より抜粋

環境

Oracle Database Express Edition 11g Release 2をCentOS 6.4 x86_64上で動作
Oracle SQL Developer 4.0

やること

テスト科目ごとの平均点テーブルと、個人ごとテスト科目点数テーブルをjoinし、平均点との差を調べたい、とする。平均がテーブルに入ってる時点で恣意的な例だけど、そこは気にしない。

テスト科目ごとの平均点テーブルは行持ちで、こんな感じになってるとする。

年	科目	平均点
2010	KOKUGO	50
2010	SUGAKU	60
2010	EIGO	70

個人の点数は列持ちで、こんな感じになってるとする。

年	名前	国語	数学	英語
2010	kagami	60	65	70
2010	hoge	100	90	100

やることは、二つのテーブルで科目名が一致してるところを引き算してやるだけなのだが、ただ単にjoinするだけではどうもダメそうである。

ここでは行持ちテーブルをOracleのPIVOTで列持ちに変換してやることで解決を試みる。

準備

テスト科目ごとの平均点テーブル。

drop table AVERAGE_EAV PURGE;
CREATE TABLE AVERAGE_EAV 
(
  YEAR INTEGER
, KAMOKU_KEY VARCHAR2(20) 
, KAMOKU_VALUE INTEGER 
);
INSERT INTO "KAGAMIHOGE"."AVERAGE_EAV" (YEAR, KAMOKU_KEY, KAMOKU_VALUE) VALUES (2010, 'KOKUGO', 50);
INSERT INTO "KAGAMIHOGE"."AVERAGE_EAV" (YEAR, KAMOKU_KEY, KAMOKU_VALUE) VALUES (2010, 'SUGAKU', 60);
INSERT INTO "KAGAMIHOGE"."AVERAGE_EAV" (YEAR, KAMOKU_KEY, KAMOKU_VALUE) VALUES (2010, 'EIGO', 70);
INSERT INTO "KAGAMIHOGE"."AVERAGE_EAV" (YEAR, KAMOKU_KEY, KAMOKU_VALUE) VALUES (2020, 'KOKUGO', 45);
INSERT INTO "KAGAMIHOGE"."AVERAGE_EAV" (YEAR, KAMOKU_KEY, KAMOKU_VALUE) VALUES (2020, 'SUGAKU', 55);
INSERT INTO "KAGAMIHOGE"."AVERAGE_EAV" (YEAR, KAMOKU_KEY, KAMOKU_VALUE) VALUES (2020, 'EIGO', 65);
COMMIT;

個人ごとテスト科目点数テーブル。

drop table SCORES PURGE;
CREATE TABLE SCORES 
(
  YEAR INTEGER
, NAME VARCHAR2(20) 
, KOKUGO INTEGER 
, SUGAKU INTEGER 
, EIGO INTEGER 
);

INSERT INTO "KAGAMIHOGE"."SCORES" (YEAR, NAME, KOKUGO, SUGAKU, EIGO) VALUES (2010, 'kagami', '60', '65', '70');
INSERT INTO "KAGAMIHOGE"."SCORES" (YEAR, NAME, KOKUGO, SUGAKU, EIGO) VALUES (2010, 'hoge', '100', '90', '100');
INSERT INTO "KAGAMIHOGE"."SCORES" (YEAR, NAME, KOKUGO, SUGAKU, EIGO) VALUES (2010, 'foo', '90', '40', '95');
INSERT INTO "KAGAMIHOGE"."SCORES" (YEAR, NAME, KOKUGO, SUGAKU, EIGO) VALUES (2010, 'bar', '0', '0', '0');
INSERT INTO "KAGAMIHOGE"."SCORES" (YEAR, NAME, KOKUGO, SUGAKU, EIGO) VALUES (2020, 'kagami', '70', '75', '80');
INSERT INTO "KAGAMIHOGE"."SCORES" (YEAR, NAME, KOKUGO, SUGAKU, EIGO) VALUES (2020, 'hoge', '10', '100', '10');
INSERT INTO "KAGAMIHOGE"."SCORES" (YEAR, NAME, KOKUGO, SUGAKU, EIGO) VALUES (2020, 'foo', '90', '100', '100');
INSERT INTO "KAGAMIHOGE"."SCORES" (YEAR, NAME, KOKUGO, SUGAKU, EIGO) VALUES (2020, 'bar', '0', '0', '0');
COMMIT;

行列変換

まずはAVERAGE_EAVテーブルの行列変換をする。

SELECT year, avg_kokugo, avg_sugaku, avg_eigo
FROM  average_eav
PIVOT (MAX(kamoku_value) FOR kamoku_key IN(
                                           'KOKUGO' AS avg_kokugo,
                                           'SUGAKU' AS avg_sugaku,
                                           'EIGO' AS avg_eigo));

この例の場合、集約するものが無いのでMAXは必要無いのだけどPIVOTの性質上つける必要がある。わかりにくいのが「暗黙のgroup by」で、この例だとyear列で集約が行われる。実行結果はこんな感じ。

YEAR	AVG_KOKUGO	AVG_SUGAKU	AVG_EIGO
2010	50	60	70
2020	45	55	65

あとはyear列でjoinして科目のカラム名でを引き算する。

SELECT
  s.year,
  s.name,
  s.kokugo - avgs.avg_kokugo AS "国語",
  s.sugaku - avgs.avg_sugaku AS "数学",
  s.eigo   - avgs.avg_eigo   AS "英語"
FROM
  scores s join
(
  SELECT year, avg_kokugo, avg_sugaku, avg_eigo
  FROM  average_eav
  PIVOT (MAX(kamoku_value) FOR kamoku_key IN(
                                             'KOKUGO' AS avg_kokugo,
                                             'SUGAKU' AS avg_sugaku,
                                             'EIGO' AS avg_eigo))
) avgs
ON
  s.year = avgs.year;

実行結果はこんな感じ。

YEAR	NAME	国語	数学	英語
2010	kagami	10	5	0
2010	hoge	50	30	30
2010	foo	40	-20	25
2010	bar	-50	-60	-70
2020	kagami	25	20	15
2020	hoge	-35	45	-55
2020	foo	45	45	35
2020	bar	-45	-55	-65

なお、PIVOTの行列変換は下記のようにも書ける。

SELECT
  YEAR,
  MAX(decode(kamoku_key,'KOKUGO',kamoku_value)) AS avg_kokugo,
  MAX(decode(kamoku_key,'SUGAKU',kamoku_value)) AS avg_sugaku,
  MAX(decode(kamoku_key,'EIGO',kamoku_value)) AS avg_eigo
FROM average_eav
GROUP BY YEAR;

kagamihogeの日記

kagamihogeの日記です。

OracleのPIVOTによる行列変換

環境

やること

準備

行列変換

参考URL