MySQLでカンマ区切りの横持ちDBを縦持ちに変換するやつ

こんな感じにしたい

+------------------------------+----------------------+
| title                        | category             |
+------------------------------+----------------------+
| おまじない                    | 姉                   |
| お留守番                      | 義妹                 |
| まくあいげき                  | イチャラブ            |
| よびだし                      | 妹                   |
| れっつすたでぃ                | 従兄弟                |
| イかせたい交換条件             | 近親相姦              |
| バカンス                      | 褐色                 |
| ピンチランナーズHIGH          | 褐色,陸上,方言         |
| フラッピーキャット             | ギャル               |
| 近距離注意予報                | 妹,パイパン           |
+------------------------------+----------------------+

例えば上みたいな感じのDBがあったとして(何のデータかは聞かないこと)、次のようにしたいとする。

+------------------------------+----------------------+
| title                        | category             |
+------------------------------+----------------------+
| おまじない                    | 姉                   |
| お留守番                      | 義妹                 |
| まくあいげき                  | イチャラブ            |
| よびだし                      | 妹                   |
| れっつすたでぃ                | 従兄弟                |
| イかせたい交換条件             | 近親相姦              |
| バカンス                      | 褐色                 |
| ピンチランナーズHIGH          | 方言                  |
| ピンチランナーズHIGH          | 陸上                  |
| ピンチランナーズHIGH          | 褐色                  |
| フラッピーキャット             | ギャル               |
| 近距離注意予報                | 妹                    |
| 近距離注意予報                | パイパン              |
+------------------------------+----------------------+

本来せっかくのDBに対してCSVで値を入れることがそもそも勧められないことらしいですが、言われてなきゃ気づかねーよって感じです。DB構築する前に教えといてくださいよ。
ちなみにこの様に横持ち→縦持ちに変換することをピボットって言います。

どうやるのか

別のOSSDBや商用RDBMSならそれようの関数が用意されているのですが、MySQLにはそれがないようです。私はMySQLしか触ったことがない情弱なので、少々遠回りする必要があります。

select title, substring_index(substring_index(category, ',', CS), ',', - 1) as category
from
    (
           select 3 as CS, title, category
           from
               master_table
           limit 10
    ) as t1
where
    length(category) - length(replace(category, ',', '')) >= 2;
+------------------------------+----------------------+
| title                        | category             |
+------------------------------+----------------------+
| ピンチランナーズHIGH          | 方言                  |
+------------------------------+----------------------+

まずはカンマが2つの行を出力し、それに対して右端(3つ目)の値を出力します。

もう御察しだと思いますが、これをあと2回繰り返すだけです。
カンマが1つの行を出力し、右端の値を出力します。

select
    title, substring_index(substring_index(category, ',', CS), ',', - 1) as category
from
    (
        select 2 as CS, title, category
        from
            master_table
        limit 10
    ) as t2
where
    length(category) - length(replace(category, ',', '')) >= 1;
+------------------------------+----------------------+
| title                        | category             |
+------------------------------+----------------------+
| ピンチランナーズHIGH          | 陸上                  |
| 近距離注意予報                | パイパン              |
+------------------------------+----------------------+

3回目は割愛。

最後にunionでまとめる

一応最終的なクエリを載せておきます。

select *
from
    (
        select title, substring_index(substring_index(category, ',', CS), ',', - 1) as category
        from
            (
                   select 3 as CS, title, category
                   from master_table limit 10
            ) as t1
        where
            length(category) - length(replace(category, ',', '')) >= 2
        union
        select title, substring_index(substring_index(category, ',', CS), ',', - 1) as category
        from
            (
                   select 2 as CS, title, category
                   from master_table limit 10
            ) as t2
        where
            length(category) - length(replace(category, ',', '')) >= 1
        union
        select
            title, substring_index(substring_index(category, ',', CS), ',', - 1) as category
        from
            (
                select 1 as CS, title, category
                from
                    master_table
                limit 10
            ) as t3
        where
            length(category) - length(replace(category, ',', '')) >= 0
    ) T
order by title;

これは蛇足なのですが、union allだと重複を許し、unionは重複を削除するためソートを行っています。

カンマの数が増える分だけクエリの数を増やす必要があるので、正直面倒臭い。
やはりPostgreを学ぶ必要があるのでしょうか。

コメントを残す

メールアドレスが公開されることはありません。 * が付いている欄は必須項目です

CAPTCHA