はじめに

シェルスクリプトでSQLのJOINを実現する方法について、王道のjoinコマンドと、awkを使った方法をそれぞれ考えてみる。
またSQLのGROUP BYもawkで実現してみる。

前提

今回使用するデータとして、以下の商品マスタと注文トランザクションを用意する。

# products(pid, pname, price)表
$ cat products
p1 pencil 100
p2 pen 200
p3 eraser 150

# orders(oid, pid(FK(products.pid), cnt)表
$ cat orders
o1 p1 3
o1 p2 2
o2 p1 3
o2 p3 1

joinコマンドでJOIN

SQLのJOINと同等の働きをするコマンドがその名の通りjoinコマンドである。

SELECT oid, pid, pname, cnt
FROM products
JOIN orders
USING (pid)

このSQLの結果は次のようになる。

o1 p1 pencil 3
o1 p2 pen 2
o2 p1 pencil 3
o2 p3 eraser 1

joinコマンドでも同等の結果を得ることができるが、注意点として、joinコマンドを使う前に結合キーでソートしなければいけない。

$ sort orders -k2 | join -1 2 -2 1 - products
p1 o1 3 pencil 100
p1 o2 3 pencil 100
p2 o1 2 pen 200
p3 o2 1 eraser 150

ここで使ったコマンドオプションは各ファイルのキーのカラム位置を示すオプション-1、-2のみ。

SQLと全く同じように項目並べ替えたければ、awkでやってもいいが、標準の-oオプションがある。
-o FILENUM.FIELD...（コンマまたは空白で区切り）の書式で指定する。

-o FIELD-LIST...
出力行のフォーマットに FIELD-LIST を用いる。 FIELD-LIST の各要素は、一文字 0 ' または M.N の形式である。 ここで M はファイル番号で 1 ' または 2 'である。 N は正の整数で、フィールドの番号である。
フィールド指定 0 ' は join フィールドを表す。 ほとんどの場合は、 0 ' の機能は M.N を用いて join フィールドを明示的に指定するやり方でも再現可能であろう。 しかし、 (-a や -v オプションを使ったときに) ペアにならなかった行も表示する場合は、両方のファイルに そのような行があると、 FIELD-LIST で M.N を使うやり方では join フィールドを指定することはできない。 join にこの機能を与えるため、 POSIX で 0 ' フィールド指定の記述が発明された。
FIELD-LIST の各要素はコンマまたは空白で区切られる。 一つの -o オプションの後に複数の FIELD-LIST 引数を指定することも出来る。 -o オプション以降の 全てのリストの値は結合される。 FIELD-LIST の指定は、 (-a や -v オプションに由来するものも含め) 全ての出力行に適用される。

-oオプションとawkそれぞれの方法を書いてみるが、awkを使う例ではawkに渡される各列の順番を把握していなければいけないのに対して、-oオプションでは各ファイルの並び順を把握していればいいので、わかりやすく、awkを使うことはないだろう。

# -oオプションで指定
$ sort orders -k2 | join -1 2 -2 1 -o 1.1 1.2 2.2 1.3 - products
o1 p1 pencil 3
o2 p1 pencil 3
o1 p2 pen 2
o2 p3 eraser 1

# awkで指定
$ sort orders -k2 | join -1 2 -2 1 - products | awk '{print $2,$1,$4,$3}'
o1 p1 pencil 3
o2 p1 pencil 3
o1 p2 pen 2
o2 p3 eraser 1

他によくつかうjoinのオプションは次のようなもので、-aをつけると外部結合も表現できるし、-1,-2,-jで結合カラムも指定できる。

-a FILENUM
print unpairable lines coming from file FILENUM, where FILENUM is 1 or 2, corresponding to FILE1 or FILE2

-j FIELD
equivalent to ‘-1 FIELD -2 FIELD’

-t CHAR
use CHAR as input and output field separator

-1 FIELD
join on this FIELD of file 1

-2 FIELD
join on this FIELD of file 2

awkでJOIN

joinコマンドを使わずにawkで実現することもできる。ただし全くエレガントではない。awkの練習として書いてみる。

while read oid pid cnt
do
  pname=$(awk -v "pid=$pid" '$1==pid {print $2}' products)
  echo "$oid $pid $pname $cnt"
done < orders

-vオプションでシェル変数をawk変数に渡して、キー($1)と変数が等しい行のほしい列($2)を取得するというもの。

ほしい列が先のように$2の一つであればそれほど大変ではないが、複数列ほしいとなると、配列を使用することになる。
例えば次のようなSQLと同じ結果を取得する場合だ。

SELECT oid, pid, pname, price, cnt
FROM products
JOIN orders
USING (pid)

結果

o1 p1 pencil 100 3
o1 p2 pen 200 2
o2 p1 pencil 100 3
o2 p3 eraser 150 1

awkとbashの配列を使い、次のように書く。

while read oid pid cnt
do
  pinfo=($(awk -v "pid=$pid" '$1==pid {print $2, $3}' products))
  echo "$oid $pid ${pinfo[0]} ${pinfo[1]} $cnt"
done < orders

awkで複数列をprintして、その標準出力をpinfoという変数に格納することで、配列として扱う。

JOINと計算式

SELECTした値を掛けたりするような場合、シェルスクリプトでどのように表現するか、awkや「echo + bashでの計算」の例を記載する。

次のSQLを題材とする。

SELECT oid, pid, pname, price * cnt
FROM products
JOIN orders
USING (pid)

結果

o1 p1 pencil 300
o1 p2 pen 400
o2 p1 pencil 300
o2 p3 eraser 150

awkで計算する場合
joinコマンドで結合を行い、結果の出力にawkを使って掛け算を表現する。

sort orders -k2 | join -1 2 -2 1 -o 1.1 1.2 2.2 1.3 2.3 - products |
 awk '{print $1,$2,$3,($4*$5)}'

echo + bashでの計算の場合
awkで結合を行い、結果の出力にechoの-nオプションを使い、改行の抑制をして表示を調整する。またbashで計算する。

while read oid pid cnt
do
  pinfo=($(awk -v "pid=$pid" '$1==pid {print $2, $3}' products))
  echo -n "$oid $pid ${pinfo[0]} "
  echo $((${pinfo[1]} * $cnt))
done < orders

awkでGROUP BY

集計をawkで実行するには、awkの連想配列を使う。連想配列のキーにGROUP BY句に入れる値を設定する。SELECT句でSUMを行いたいのであれば、連想配列の値には足し込みをしてあげればよい。

$ cat orders
o1 p1 3
o1 p2 2
o2 p1 3
o2 p3 1
$ awk '{arr[$2]+=$3} END{for(i in arr) print i, arr[i]}' orders
p1 6
p2 2
p3 1

今回は前段でJOIN結合を取り上げているので、JOIN + GROUP BYのSQLと同じ結果をシェルスクリプトで実現してみたい。

SELECT oid, sum(price * cnt)
FROM products
JOIN orders
USING (pid)
GROUP BY oid

結果

o1 700
o2 450

GROUP BYする前のJOINだけの構文であれば、先にjoinコマンドで実現していた。今回のSQLに合うように射影を最小限に減らして再掲する。

$ sort orders -k2 | join -1 2 -2 1 -o 1.1 1.3 2.3 - products |
 awk '{print $1,($2*$3)}'
o1 300
o2 300
o1 400
o2 150

このコマンド結果、非常にシンプルになったので、はじめに解説したawkの集計方法を使う。

$ sort orders -k2 | join -1 2 -2 1 -o 1.1 1.3 2.3 - products |
 awk '{print $1,($2*$3)}' |
 awk '{arr[$1]+=$NF} END{for(i in arr) print i, arr[i]}'
o1 700
o2 450