前回に引き続いて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 |
以上がBOM表を作成するにあたって必要なデータの前処理でした。
最終的にはBOM表×現在庫データの構成となっています。
次回はこちらを使って、Tableauやグラフデータベースを使ってビジュアライズをいたします。
では。
今回使用したデータは以下に格納しています。