読者です 読者をやめる 読者になる 読者になる

kagamihogeの日記

kagamihogeの日記です。

OracleのPIVOTによる行列変換

DB SQL Oracle

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

やること

テスト科目ごとの平均点テーブルと、個人ごとテスト科目点数テーブルを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;