union allの活用

ちょっと仕事で使った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の行）

問題は、社内移動の出荷をした時。この時は出荷元の在庫が減少する行と、出荷先の移動中数量が増加するという行の２行が発生することになる。（上の在庫テーブルのtxn_id = 10005、10006の行）ここが先のSQLと大きく異なってくるところ。

取引実績テーブルの１行につき、在庫テーブルへ２行Insertする・・・。これはどうすればええのんや・・・？

取引実績テーブルのレコードを判別して、社内移動の出荷だったら２通りのInsertを行う、みたいな処理をループせないかんのか？と初めは漠然とイメージした。

めんどくさ・・・。いやいや、動的な変数があるわけでもないのにループ処理なんて・・・もっとスマートな方法があるはず・・・。と、チームマネジャーに相談したら、CASE分岐するんじゃなくて、SELECT文を２種類作ってUNIONしたら？と言われた。

ほ？

なるほど〜！こういうことか！

UNIONというのは複数のSELECT文の結果を結合するコマンド。上のSELECT文というのは基本的に一番はじめに書いたSQLと同じ。下のSELECT文は、取引実績テーブル上の「出荷」かつ「社内移動」のレコードだけを抽出したもの。この２つをがっしゃんこさせたものを在庫テーブルに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とした方がパフォーマンスは良い。