«
»

Oracleの行列変換でサクッと回転

2019 年 2 月 18 日 by yasukuni

とある業務の中で、非正規化のDB構造に直面した際に
行列変換という関数がoracleにあったのでメモしておきます。
そんなに頻度は無いですが他のRDBMSでは面倒な変換がサクッとできるようなので、記憶の片隅にあると便利だと思います。

サンプルデータを作成

まず、今回の説明のために直面したDB構造のサンプルデータを作成します。
ぱっと思いつかなかったので「都道府県〇〇ランキング」にします。

/*都道府県マスタ*/
create table pref(
	prefid int not null,
	name varchar(255) not null,
	primary key(prefid)
);
insert into pref values(3, '岩手県');
insert into pref values(4, '宮城県');;
insert into pref values(28, '兵庫県');
insert into pref values(33, '岡山県')
insert into pref values(34, '広島県');

/*都道府県○○ランキング*/
create table  ranking(
	id int not null,
	title varchar(255) not null,
	pref1 int null,
	pref2 int null,
	pref3 int null,
	pref4 int null,
	pref5 int null,
	primary key(id)
);
insert into ranking values(1, '牡蠣の生産量TOP5', 34, 4, 33, 3, 28);

こんな感じでサンプルのデータができました。

oracleの行列変換を使わない場合

標準のSQLで対応すると1カラムずつちぎって、UNIONで縦につなげます。
(例では、順序が重要なので、ちぎる時にRANKという列を追加しています)

SELECT
	RNK.TITLE, PREF.NAME
FROM 
	(
		      SELECT ID, TITLE, 1 as RANK, PREF1 AS PERFID FROM ranking
		UNION SELECT ID, TITLE, 2 as RANK, PREF2 AS PERFID FROM ranking
		UNION SELECT ID, TITLE, 3 as RANK, PREF3 AS PERFID FROM ranking
		UNION SELECT ID, TITLE, 4 as RANK, PREF4 AS PERFID FROM ranking
		UNION SELECT ID, TITLE, 5 as RANK, PREF5 AS PERFID FROM ranking
	) RNK
	LEFT OUTER JOIN PREF
		ON(RNK.PERFID = pref.PREFID)
WHERE
	RNK.ID = 1 
ORDER BY
	RNK.RANK ASC

実行するとこうなります。普通です。
ただ、繰り返しの列が増える分だけUNIONが増えていきます。

oracleの行列変換を使う場合

ミニマムで実行する場合のSQLは以下の通り。

SELECT * FROM ranking 
UNPIVOT(foo FOR bar IN(PREF1,PREF2,PREF3,PREF4,PREF5))

‘foo’、’bar’の部分は列名になるので任意の名前をセットし
INの後に、行持ちに変換したい列名を指定します。

最終型は以下の通りです。
この行列変換を使用するとあのUNIONの繰り返しがなくなるのでスッキリした表現になります。

SELECT
	RNK.TITLE, PREF.NAME
FROM 
	(SELECT * FROM ranking 
          UNPIVOT(prefid FOR colname IN(PREF1,PREF2,PREF3,PREF4,PREF5)) 
          WHERE ID = 2
        ) RNK 
	LEFT OUTER JOIN PREF ON(RNK.PREFID = PREF.PREFID)
ORDER BY
	RNK.COLNAME

たまーに忘れた頃こういうデータ構造に出会います。
正規化できない場合でoracleだったときは、
何かサクッと出来たハズと思い出してみてください。

タグ: ,

TrackBack