【MySQL】カンマ区切りになっているテキストのカラムを縦持ちにする
MySQL 限定テクニック。
項目がいくつあるか事前にわかっていれば、SUBSTRING_INDEX
関数を使って縦持ちにできる。
項目数が不定だとたぶんつらい。(試してない)
例として、下のテーブル favorites
の favorites
カラムに、
カンマ区切りで好きな言語が3つ入っているとする。
CREATE TABLE favorites ( name VARCHAR(16) PRIMARY KEY , favorites VARCHAR(255) NOT NULL );
中身は下のとおり。
name | favorites |
---|---|
太郎 | C,Rust,Scheme |
花子 | Ruby,Python,APL |
とりあえずインラインビューで 1 .. 3 までの補助テーブル L
(loop のつもり) をつくっておいて、直積をとる。
結果の6行に対して、SUBSTRING_INDEX
を適用して分割した値を得ることができる。
SELECT name , SUBSTRING_INDEX(SUBSTRING_INDEX(favorites, ",", L.`index`), ",", -1) AS favorite FROM favorites CROSS JOIN ( SELECT 1 AS `index` UNION SELECT 2 UNION SELECT 3 ) L ORDER BY name ;
かくして、縦持ちの結果が得られる。
name | favorite |
---|---|
太郎 | C |
太郎 | Rust |
太郎 | Scheme |
花子 | Ruby |
花子 | Python |
花子 | APL |
ズンドコしたい (2)
こっちは入力を5つ区切りにして、それぞれが 00001 の並びになっているかどうかをチェックする。 (仕様はこちらが正しいらしい?)
$ cat /dev/urandom \ | LANG=C tr -dc "01" \ | fold -w1 \ | paste -d' ' - - - - - \ | (perl -nle 'print; if ($_ eq "0 0 0 0 1") { exit 0; }'; echo 2) \ | perl -ple 'BEGIN { @table = qw/ズン ドコ キ・ヨ・シ!/ } s/(\d)/$table[$1]/ge' \ ドコ ドコ ズン ドコ ドコ ズン ドコ ズン ズン ズン ドコ ズン ズン ズン ドコ ズン ドコ ズン ズン ドコ ズン ドコ ズン ドコ ドコ ドコ ドコ ズン ズン ズン ズン ズン ドコ ズン ズン ズン ドコ ドコ ドコ ドコ ズン ドコ ドコ ズン ドコ ドコ ドコ ズン ドコ ズン ドコ ズン ドコ ドコ ズン ズン ドコ ドコ ズン ドコ ズン ズン ドコ ズン ドコ ドコ ドコ ドコ ドコ ズン ドコ ズン ドコ ズン ドコ ドコ ドコ ドコ ズン ズン ズン ドコ ズン ズン ズン ドコ ドコ ドコ ズン ドコ ドコ ズン ズン ズン ドコ ズン ドコ ドコ ドコ ドコ ズン ズン ズン ドコ ズン ズン ドコ ズン ズン ズン ドコ ズン ドコ ドコ ドコ ズン ズン ズン ドコ ドコ ズン ズン ズン ズン ズン ズン ズン ドコ ドコ ドコ ズン ズン ズン ズン ドコ キ・ヨ・シ!
ズンドコしたい
ストリーム上の 0 と 1 の並びを sed
に取り込んで、00001 かどうかを DFA っぽく検査する。
$ cat /dev/urandom \ | LANG=C tr -dc "01" \ | fold -w1 \ | (sed -nf dfa.sed; echo 2) \ | perl -ple 'BEGIN { @table = qw/ズン ドコ キ・ヨ・シ!/; } s/(.)/$table[$1]/e;' \ | paste -sd$'\\0' - ズンズンドコドコズンドコズンドコドコドコドコズンズンズンズンドコキ・ヨ・シ!
肝心の dfa.sed は下記の通り。 状態をラベルとして表現している。
s1 から始まって、s5 が「ドコ」を判定する状態。「ドコ」であったら、その後 quit に遷移して受理状態となる。
#!/usr/bin/env sed :s1 /0/ bs2 b restart :s2 p; n /0/ bs3 b restart :s3 p; n /0/ bs4 b restart :s4 p; n /0/ bs5 b restart :s5 p; n /1/ bquit bs5 :quit p; q :restart p; n bs1