ちょっと仕事で使ったSQLの備忘録として。
下のような取引実績テーブルがあるとする。
txn_id | txn_ctg | org_ship_from | org_ship_to | item | qty | intransit_flag |
10001 | I | 東京 | こまちゃん缶バッチ | 200 | ||
10002 | O | 東京 | こまちゃんぬいぐるみ | 50 | ||
10003 | O | 大阪 | こまちゃん缶バッチ | 100 | ||
10004 | I | 名古屋 | こまちゃんタペストリー | 80 | ||
10005 | O | 名古屋 | 東京 | こまちゃん缶バッチ | 100 | Y |
10006 | O | 東京 | 大阪 | こまちゃんぬいぐるみ | 50 | Y |
10007 | I | 名古屋 | 東京 | こまちゃん缶バッチ | 100 | Y |
txn_ctgが「I」なら入荷、「O』なら出荷。どの商品がどの倉庫にいくつ入っていくつ出ていったのかが記録されている。intransit_flagが「Y」になってるのは社内での在庫移動(積送)を意味するものとする。
この取引実績テーブルからデータを抽出して、下のような在庫テーブルにレコードを追加していきたい。
org | item | qty | txn_id |
東京 | こまちゃん缶バッチ | 200 | 10001 |
東京 | こまちゃんぬいぐるみ | -50 | 10002 |
大阪 | こまちゃん缶バッチ | -100 | 10003 |
名古屋 | こまちゃんタペストリー | 80 | 10004 |
名古屋 | こまちゃん缶バッチ | -100 | 10005 |
東京 | こまちゃんぬいぐるみ | -50 | 10006 |
東京 | こまちゃん缶バッチ | 100 | 10007 |
各倉庫、各商品の数量の増減が記録されていく。
入庫の場合は数量が増える。出庫の場合は数量が減るので、負の数で表すものとする。
で、この実現方法だけど、出庫か入庫かによって若干の分岐はあるものの、基本的には取引実績テーブルの内容をそのままマッピングすればいいので、シンプルなSELECT INSERTコマンドで大丈夫。
insert into t_inv ( org , item , qty , txn_id ) select case x.txn_ctg when 'O' then x.org_ship_from else x.org_ship_to end case -- org , x.item -- item , case x.txn_ctg when 'O' then x.qty * (-1) else x.qty end case -- qty , x.txn_id -- txn_id from t_txn x ;
こんな感じ〜。
しかし皆さん、このままだと普通のINSERT文。ここからがマグマなんです!
要件を追加して、在庫テーブルに移動中数量(moving_qty)という項目を持たせたい。
移動中数量って何やねんという話は本筋じゃないのでざっくり説明するが、社内の在庫移動で、出荷はされたがまだ受入されていない(積送中の)在庫のことである。例えば、取引実績テーブルのtxn_id:10005の取引では名古屋倉庫から東京倉庫に缶バッチを100個出荷しているが、単純に名古屋倉庫から-100個しただけだと、東京で受入の取引が記録されるまでの間、全社在庫としてみた時に100個消失したことになる。あくまで社内移動なんだから、全社在庫は±0じゃないとおかしいよねぇ?という話。
だから、社内移動で商品を100個出荷した際は、出荷元倉庫で数量を100個減らすと同時に、出荷先倉庫の移動中数量に100個加えて帳尻を合わせる。逆に社内移動の在庫を受入した際は、受入した倉庫の在庫を増やすと同時に、移動中数量を減らして消し込む必要がある。
そういう要件を追加した在庫テーブルは下のようなイメージ。
org | item | qty | moving_qty | txn_id |
東京 | こまちゃん缶バッチ | 200 | 10001 | |
東京 | こまちゃんぬいぐるみ | -50 | 10002 | |
大阪 | こまちゃん缶バッチ | -100 | 10003 | |
名古屋 | こまちゃんタペストリー | 80 | 10004 | |
名古屋 | こまちゃん缶バッチ | -100 | 10005 | |
東京 | こまちゃん缶バッチ | 100 | 10005 | |
東京 | こまちゃんぬいぐるみ | -50 | 10006 | |
大阪 | こまちゃんぬいぐるみ | 50 | 10006 | |
東京 | こまちゃん缶バッチ | 100 | -100 | 10007 |
さて、これをどう実現するかだけども、まず、社内移動在庫を受入した時に移動中数量を消し込むというのはあまり問題ない。同じ行内で表現できるので。(上の在庫テーブルのtxn_id = 10007の行)
問題は、社内移動の出荷をした時。この時は出荷元の在庫が減少する行と、出荷先の移動中数量が増加するという行の2行が発生することになる。(上の在庫テーブルのtxn_id = 10005、10006の行)ここが先のSQLと大きく異なってくるところ。
取引実績テーブルの1行につき、在庫テーブルへ2行Insertする・・・。これはどうすればええのんや・・・?
取引実績テーブルのレコードを判別して、社内移動の出荷だったら2通りのInsertを行う、みたいな処理をループせないかんのか?と初めは漠然とイメージした。
めんどくさ・・・。いやいや、動的な変数があるわけでもないのにループ処理なんて・・・もっとスマートな方法があるはず・・・。と、チームマネジャーに相談したら、CASE分岐するんじゃなくて、SELECT文を2種類作ってUNIONしたら?と言われた。
ほ?
なるほど〜!こういうことか!
UNIONというのは複数のSELECT文の結果を結合するコマンド。上のSELECT文というのは基本的に一番はじめに書いたSQLと同じ。下のSELECT文は、取引実績テーブル上の「出荷」かつ「社内移動」のレコードだけを抽出したもの。この2つをがっしゃんこさせたものを在庫テーブルにInsertしようってこと。
つまり、
org | item | qty | moving_qty | txn_id |
東京 | こまちゃん缶バッチ | 200 | 10001 | |
東京 | こまちゃんぬいぐるみ | -50 | 10002 | |
大阪 | こまちゃん缶バッチ | -100 | 10003 | |
名古屋 | こまちゃんタペストリー | 80 | 10004 | |
名古屋 | こまちゃん缶バッチ | -100 | 10005 | |
東京 | こまちゃんぬいぐるみ | -50 | 10006 | |
東京 | こまちゃん缶バッチ | 100 | -100 | 10007 |
こういうSELECT結果と
org | item | qty | moving_qty | txn_id |
東京 | こまちゃん缶バッチ | 100 | 10005 | |
大阪 | こまちゃんぬいぐるみ | 50 | 10006 |
こういうSELECT結果をUNIONさせて在庫テーブルに入れようという話。
実際にSQLソースを書くとこんな感じ。
insert into t_inv
( org
, item
, qty
, moving_qty
, txn_id
)
select -- 1つ目のSELECT文
case x.txn_ctg
when 'O' then
x.org_ship_from
else
x.org_ship_to
end case -- org
, x.item -- item
, case x.txn_ctg
when 'O' then
x.qty * (-1)
else
x.qty
end case -- qty
, case
when x.txn_ctg = 'I' and x.intransit_flag = 'Y' then
x.qty * (-1)
else
null
end case -- moving_qty
, txn_id -- txn_id
from t_txn x
union all
select -- 2つ目のSELECT文
x.org_ship_to -- org
, x.item -- item
, null -- qty
, x.qty -- moving_qty
, x.txn_id -- txn_id
from t_txn x
where
x.txn_ctg = 'O' and x.intransit_flag = 'Y'
order by txn_id
;
これならループしなくていいし、Insert文を何度も書く必要もない。実にスマート。なるほど〜。条件によって結果が分かれる、となるとすぐにCASE分岐という発想になってしまいがちだったが、もっと柔軟にならなきゃダメなのね・・・。
ちなみに、今回はUNION ALLを使っているが、UNIONというコマンド単体でも使える。違いとしては、UNION ALLは単純に複数のSELECT結果を結合するだけなのに対して、UNIONは結合した結果から重複レコードを削除してくれるという機能がある。便利だけども、当然UNIONの方が重複チェックが入る分だけ処理が遅い。今回のようにWHERE句の抽出条件によって明らかに結果が重複しないと分かっている場合にはUNION ALLとした方がパフォーマンスは良い。