BAD DB設計に対するSQLパターン応用編1 カンマ区切りのカラムを列(縦方向に分解)

今回は、BAD DB設計に対するSQLパターン入門編の応用編です。

1カラムの中にカンマ区切りでデータが入っているBAD DB設計、これもわりと良く見るパターンですね。これを列に分解したい場合がある。簡単そうで実はわりと難しいテーマです。残念ながら、ぐぐっても解決方法は出てこない。

• MySQLのViewでカンマ区切りの横データを縦に変換

↑これはいい線いってましたね。

SELECT '1,3,5,7,8'csv

1,3,5,7,8

これを列に変換

SELECT SUBSTRING_INDEX(SUBSTRING_INDEX(csv,',',v.n),',',-1) 
 FROM (SELECT '1,3,5,7,8'csv) c,
      (SELECT 1 n UNION ALL 
       SELECT 2   UNION ALL
       SELECT 3   UNION ALL
       SELECT 4   UNION ALL
       SELECT 5) v 
WHERE  LENGTH(csv)-LENGTH(REPLACE(csv,',',''))+1 >=v.n

	1
	3
	5
	7
	8

『単純結合』と『仮想表』を使うところは入門編とおなじで、これに加えてSUBSTRING_INDEXを2つ使って、カンマ区切りのカラムから指定位置のデータを取り出している。ちなみにFIND_IN_SETでは、カンマ区切りデータ中で一致するデータの位置を得る関数なので、似ているが使えない。

WHERE  LENGTH(csv)-LENGTH(REPLACE(csv,',',''))+1 >=v.n

WHERE句では、LENGTH関数とREPLACE関数を使用することで、カンマの数を取得している。

仮想表はカンマの数の最大数分だけ必要なので注意すること。
単純結合を使用するので、カンマの数が増えるほど、仮想表の列が大きくなり、その乗数分だけ処理が重くなる。