シェルスクリプトでSQLのJOIN結合やGROUP BYを実現する

2017/01/16

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

目次

前提

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

joinコマンドでJOIN

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

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

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

ここで使ったコマンドオプションは各ファイルのキーのカラム位置を示すオプション-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を使うことはないだろう。

他によくつかう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の練習として書いてみる。

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

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

結果

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

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

JOINと計算式

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

次のSQLを題材とする。

結果

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

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

awkでGROUP BY

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

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

結果

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

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

-Linux, SQL
-