SQLでBOM表を作成（応用編）

boxcodeadmin

2020年12月27日

前回に引き続いてBOMの話。

ちなみに前回。

SQLでBOM表を作成（基礎編）

前回はBOM表作成にあたり必要なデータを一般的によく使われるSQLの再帰でもって作成しました。

今回はというと、タイトルは応用とありますが、こちらはただ応用言いたかっただけで、技術的には基礎の基礎です。

実質は基礎編パート2になります。

早速前回の構成マスタに対して、以下の「構成数=quantity」なるものを追加しました。

create table composition(
       parent varchar(3)
      ,child varchar(3)
      ,quantity numeric --追加
)

parent	child	quantity
null	001	1
001	002	2
001	003	3
003	004	4
003	005	5
005	006	6
005	007	7
006	008	8
006	009	9
006	010	10
009	011	11
009	012	12
009	013	13
010	014	14
010	015	15
null	A01	1
A01	009	2
A01	010	3
A01	B01	4

parentとchildについては前回ありましたとおり。

今回新規に追加した「quantity」は構成数を意味します。

新たに最終完成品のプロダクトにA01を追加しました。

単純な構成は以下の通りです。

しかし、実際は子品番を一つずつ組み合わせればプロダクトができるわけではありません。

それぞれの子品番を何個組み上げれば、親品番になると考えるのが普通です。

例に取るとネジなんか分かりやすいかと思います。

一つのプロダクトで持って、同じ形態のネジを複数使う。日曜大工をする、自作パソコンを組み立てる等されたことがある方は経験あるのではないでしょうか？

実際は以下のイメージとなります。

parent	child	quantity
null	A01	1
A01	009	2
A01	010	3
A01	B01	4

つまり、A01というプロダクトを作成するためには

009が2つ、010が3つ、B01が4つ必要になるということです。

さらに009と010にはそれぞれ配下に子品番が存在します。

そうしますと最終的にA01を組み立てるためには以下の子品番の数だけ必要となります。

parent	child	quantity
null	A01	1
A01	009	2
A01	010	3
A01	B01	4
009	011	22 (11×2)・・・A01を作るためには009が2つ必要
009	012	24 (12×2)
009	013	26 (13×2)
010	014	42 (14×3)
010	015	45 (15×3)

上記の通り、親品番の構成に合わせて、子品番の数量も調整しなければ、在庫データとの突き合わせができません。

結論から行きますと、SQLは以下となります。

新たな列として「quantity=構成数」と「usedquantity=実使用数」を追加しました。青い下線でマークされている部分です。

前回同様、非再帰と再帰とを組み込むことにより、必要数分の子品番を算出するようにされています。

with recursive rec_composition(level,parent,child,class,product,quantity,usedquantity)
 as 
 (
 select cast(1 as numeric) as level
       ,c.parent
       ,c.child
       ,cast(c.child as text) as class
       ,c.child as product
       ,c.quantity
       ,c.quantity as usedquantity
   from composition c
  where c.parent is null
 union 
 select p.level + 1 as level
       ,c.parent
       ,c.child
       ,p.class || '->' || c.child as class
       ,cast(substring(p.class from 1 for 3) as varchar(3)) as product
       ,c.quantity
       ,p.quantity * c.quantity as usedquantity
   from rec_composition p
  inner join composition c
     on p.child = c.parent
 )
 select level
       ,parent
       ,child
       ,class
       ,product
       ,quantity
       ,sum(usedquantity) as usedquantity
   from rec_composition
  group by level
       ,parent
       ,child
       ,class
       ,product
       ,quantity
 order by product
      ,level
      ,parent
      ,child

BOMのマスターとしては、これで完了となります。

続いての在庫データとの結合を考え、viewを作成しておくと、後々便利です。

仮に上記SQLは「vBom」というveiwにしておきます。

vBomを実行すると以下の結果となります。※A001のみ

level	parent	child	class	product	quantity	usedquantity
1	null	A01	A01	A01	1	1
2	A01	009	A01->009	A01	2	2
2	A01	010	A01->010	A01	3	3
2	A01	B01	A01->B01	A01	4	4
3	009	011	A01->009->011	A01	11	22
3	009	012	A01->009->012	A01	12	23
3	009	013	A01->009->013	A01	13	26
3	010	014	A01->010->014	A01	14	42
3	010	015	A01->010->015	A01	15	45

在庫データを作成します。

「inventory」という在庫テーブルを作成しました。

create table inventory(
       code varchar(3)
      ,quantity numeric
)

項目	説明
code	最終完成品や子品番を識別する商品コード
quantity	在庫数

今時点の在庫数は以下としました。

code	quantity
001	11
002	10
003	3
004	15
005	26
006	40
007	90
008	42
009	101
010	90
011	33
012	190
013	220
014	212
015	31
A01	0
B01	3

最後にvBomビューとinventoryテーブルを結合します。

select v.product
       ,v.level
       ,v.parent
       ,v.child
       ,v.class
       ,v.quantity
       ,v.usedquantity
       ,coalesce(i.quantity,0) as stock
   from vBom v
   left join inventory i
     on v.child = i.code
  order by v.product
      ,v.level
      ,v.parent
      ,v.child

結果は以下の通りです。※A01のみ

level	parent	child	class	product	quantity	usedquantity	stock
1	null	A01	A01	A01	1	1	0
2	A01	009	A01->009	A01	2	2	101
2	A01	010	A01->010	A01	3	3	90
2	A01	B01	A01->B01	A01	4	4	3
3	009	011	A01->009->011	A01	11	22	33
3	009	012	A01->009->012	A01	12	23	190
3	009	013	A01->009->013	A01	13	26	220
3	010	014	A01->010->014	A01	14	42	212
3	010	015	A01->010->015	A01	15	45	31