SQLアンチパターンという本があります。 その本の1章がジェイウォーク（信号無視）。ジェイウォークで紹介されているようなデータがtsvファイルとして手元にある場合に、SQLではなくシェルでなんとかするお話です。
- 作者: Bill Karwin,和田卓人,和田省二,児島修
- 出版社/メーカー: オライリージャパン
- 発売日: 2013/01/26
- メディア: 大型本
- 購入: 9人 クリック: 698回
- この商品を含むブログ (46件) を見る
試しに使ったMySQLのバージョンは5.7です。
ジェイウォーク
製品テーブルとアカウントテーブルがあり、製品ごとに複数人の担当者（アカウント）がいる、とする
CREATE TABLE `accounts` ( `account_id` bigint(20) unsigned NOT NULL AUTO_INCREMENT, `account_name` varchar(20) COLLATE utf8mb4_bin DEFAULT NULL, PRIMARY KEY (`account_id`), UNIQUE KEY `account_id` (`account_id`) ) CREATE TABLE `products` ( `product_id` bigint(20) unsigned NOT NULL AUTO_INCREMENT, `product_name` varchar(1000) COLLATE utf8mb4_bin DEFAULT NULL, `account_id` varchar(100) COLLATE utf8mb4_bin DEFAULT NULL, PRIMARY KEY (`product_id`), UNIQUE KEY `product_id` (`product_id`) )
mysql> SELECT * FROM products; +------------+---------------------+------------+ | product_id | product_name | account_id | +------------+---------------------+------------+ | 1 | Visual TurboBuilder | 12,34 | | 2 | hoge fuga | 555,666 | +------------+---------------------+------------+ mysql> SELECT * FROM accounts; +------------+--------------+ | account_id | account_name | +------------+--------------+ | 12 | taro | | 34 | hanako | | 555 | goro | +------------+--------------+
製品テーブルのaccount_idカラムに、複数のアカウントIDをカンマ区切りの文字列として保持するアンチパターン。
tsvファイル
同じようなtsvファイルがあるとする。
$ cat products.tsv product_id product_name account_id 1 Visual TurboBuilder 12,34 2 hoge fuga 555,666,777 $ cat accounts.tsv account_id account_name 12 taro 34 hanako 555 goro
SQL vs シェル
特定のアカウントに関連する製品の検索
account_id=12に関連するproductsを検索する。
SQLアンチパターンで紹介されているSQLは以下。正規表現を使った WHERE 句の指定がなかなか強烈...。
SELECT * FROM products; +------------+---------------------+-------------+ | product_id | product_name | account_id | +------------+---------------------+-------------+ | 1 | Visual TurboBuilder | 12,34 | | 2 | hoge fuga | 555,666,777 | +------------+---------------------+-------------+
SELECT * FROM products WHERE account_id REGEXP '[[:<:]]12[[:>:]]'; +------------+---------------------+------------+ | product_id | product_name | account_id | +------------+---------------------+------------+ | 1 | Visual TurboBuilder | 12,34 | +------------+---------------------+------------+
対して、シェルでやってみる。 NR で行番号を取得してヘッダー行かそれ以外の行かで分岐、ヘッダー行以外の場合は $3 （3カラム目の値）と正規表現を使って行を絞り込む。
$ cat products.tsv product_id product_name account_id 1 Visual TurboBuilder 12,34 2 hoge fuga 555,666,777
$ cat products.tsv | awk -F '\t' '{
> if (NR == 1) { print } #ヘッダー行を出力
> else if ($3 ~ /12/) { print } #ヘッダー行以外は account_id=12 で絞り込み
> }'
product_id product_name account_id
1 Visual TurboBuilder 12,34
ヘッダー行の出力をなんとかして if をなくしたいなぁ、いい方法ないだろうか？
特定の製品に関連するアカウントの検索
product_id=1に関連するアカウント情報の一覧を取得する。
SQLアンチパターンで紹介されているSQLは以下。
SELECT * FROM products; +------------+---------------------+-------------+ | product_id | product_name | account_id | +------------+---------------------+-------------+ | 1 | Visual TurboBuilder | 12,34 | | 2 | hoge fuga | 555,666,777 | +------------+---------------------+-------------+ SELECT * FROM accounts; +------------+--------------+ | account_id | account_name | +------------+--------------+ | 12 | taro | | 34 | hanako | | 555 | goro | +------------+--------------+
SELECT * FROM products AS p INNER JOIN accounts AS a ON p.account_id REGEXP CONCAT('[[:<:]]', a.account_id, '[[:>:]]') WHERE p.product_id = 1; +------------+---------------------+------------+------------+--------------+ | product_id | product_name | account_id | account_id | account_name | +------------+---------------------+------------+------------+--------------+ | 1 | Visual TurboBuilder | 12,34 | 12 | taro | | 1 | Visual TurboBuilder | 12,34 | 34 | hanako | +------------+---------------------+------------+------------+--------------+
シェルでやってみる。
$ cat products.tsv product_id product_name account_id 1 Visual TurboBuilder 12,34 2 hoge fuga 555,666,777 $ cat accounts.tsv account_id account_name 12 taro 34 hanako 555 goro
ヘッダー行の取り扱いはさっきと同じ。 split を使ってカンマ区切りの文字列を配列にセット、配列に対するループ内で print することで、account_id毎の行に展開する。結果は、tmp_product.tsvというファイルに書き込んでおく。
$ cat products.tsv | awk -F '\t' '{
> if (NR == 1) { print } #ヘッダー行を出力
> else if ($1 == 1) { #ヘッダー行以外は product_id=1 で絞り込み
> split($3, arr, ","); #splitでカンマで分割
> for (i in arr) {
> print $1 "\t" $2 "\t" arr[i] #複数行に展開
> }
> }
> }' > tmp_product.tsv
$ cat tmp_product.tsv
product_id product_name account_id
1 Visual TurboBuilder 12
1 Visual TurboBuilder 34
作成したtmp_product.tsvとaccounts.tsvとを、account_id列でjoinする。タブ文字区切りにしたいので -t オプションを使うが "\t" という指定をしてもタブ文字として扱ってくれないので、bashの ${string}の記法を使って指定する（この記法は何か呼び名はあるのかな？）。
$ join --header -t $'\t' -1 3 -2 1 tmp_product.tsv accounts.tsv account_id product_id product_name account_name 12 1 Visual TurboBuilder taro 34 1 Visual TurboBuilder hanako
できた！
ワンライナーで書きたい場合は、bashだとプロセス置換を使ってjoinコマンドで読む。
$ join --header -t $'\t' -1 3 -2 1 <(cat products.tsv | awk -F '\t' '{ if (NR == 1) { print } else if ($1 == 1) { split($3, arr, ","); for (i in arr) { print $1 "\t" $2 "\t" arr[i] }}}') accounts.tsv
account_id product_id product_name account_name
12 1 Visual TurboBuilder taro
34 1 Visual TurboBuilder hanako
集約クエリ
product毎に関連するaccountの数を取得する。
SQLアンチパターンで紹介されてるクエリは以下。文字列長からカンマ以外の文字列長を引くことでカンマの数を取得して +1 する。これもなかなか強烈。
SELECT product_id, LENGTH(account_id) - LENGTH(REPLACE(account_id, ',', '')) + 1 AS contracts_per_product FROM products; +------------+-----------------------+ | product_id | contracts_per_product | +------------+-----------------------+ | 1 | 2 | | 2 | 3 | +------------+-----------------------+
これはシェルなら簡単。awkのsplitが配列の要素の数を返してくれるので、それを出力すればOK!
$ cat products.tsv | awk -F '\t' '
> BEGIN { print "product_id" "\t" "contracts_per_product"} #BEGINでヘッダー行を出力する
> NR > 1 {
> n = split($3, arr, ",");
> print $1 "\t" n
> }'
product_id contracts_per_product
1 2
2 3
おしまい
シェルはおもしろいですね！
