SQLアンチパターン・ジェイウォークのクエリをシェルでやる

SQLアンチパターンという本があります。 その本の1章がジェイウォーク(信号無視)。ジェイウォークで紹介されているようなデータがtsvファイルとして手元にある場合に、SQLではなくシェルでなんとかするお話です。

SQLアンチパターン

SQLアンチパターン

試しに使った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

おしまい

シェルはおもしろいですね!