背景
在制造业财务数据分析建设过程中，有时需要通过BOM汇总计算材料的单价，一般会有采购核价，库存成本，还有下阶材料单价按用量汇总得到的单价参与。
这些单价来源一般会根据优先级获取并在计算后作为最终的BOM 单价结果。参与财务三大报表中的损益表计算。
获取数据：采购核价、币种和汇率
采购核价
核价一般会包括成品和底层材料，而且优先级一般会比较高，所以这里直接使用核价的方式设置一个成品的单价，以用于测试。
库存成本
除了采购核价外，库存成本一般是覆盖范围比较广的单价，能够覆盖到最底层的大量材料料号。
这里我们在初始化时设置最底层材料成本时，将使用库存成本金额。
币种
为了兼容多个货币和地区，价格会有一个字段表示币种。金额则是此币种的单价金额。
同时需要带上一个汇率明细。以满足各个币种单价金额的汇算。
获取数据：BOM汇总结构
初始化时使用核价和成本单间仍然不能完全覆盖企业内使用的所有的材料，尤其是BOM结构复杂、材料料号过多的情况下。
所以一般情况下，企业会使用BOM关系，将材料的单价汇总计算到上阶材料。
本文将使用 bom 结构重新汇总后的BOM数据分析维度，具体可见：
数据仓库实践：使用SQL汇总BOM数据分析维度 http://t.csdnimg.cn/gZ1pS
部分 dim_bom 结构
初始化
使用BOM关联并计算核价和成本单价
insertinto dw_mt_bom_unit_price_mview(material_code_prod,
material_code,
currency_cost,
price_cost_ori,
price_cost,
currency_verify,
price_verify_ori,
price_verify,
price_calculate)select*from(with tmp_prod as(select material_code material_code_prod,
material_code,null currency_cost,nullas price_cost_ori,nullas price_cost,
c.currency_verify,
c.price_verify as price_verify_ori,
d.exchange_rate * IFNULL(c.price_verify,0)as price_verify,null price_calculate
from(selectdistinct bom_subordinate.product_code as material_code
# BOM 中只在 product_code 字段存在 不在 material_code 存在代表 该物料没有上阶物料 推断出本身是成品from(select product_code
from dim_bom) bom_subordinate
leftjoin(select material_code
from dim_bom) bom_superior
on bom_subordinate.product_code = bom_superior.material_code
where bom_superior.material_code isnull) a
# 采购核价leftjoin(SELECT material_code mat_code,
currency currency_verify,
amount_price price_verify
FROM clip_ods.price_verify) c
on a.material_code = c.mat_code
# 汇率leftjoin(select currency_transaction,
exchange_rate
from clip_ods.currency_exchange
where currency_base ='CNY') d
on c.currency_verify = d.currency_transaction),
tmp_mat as(select product_code material_code_prod,
material_code,
currency_cost,
price_cost as price_cost_ori,casewhen price_cost isnullthen0else price_cost * e.exchange_rate endas price_cost,
currency_verify,
price_verify as price_verify_ori,casewhen price_verify isnullthen0else price_verify * d.exchange_rate endas price_verify,casewhen price_cost isnotnullthen price_cost * e.exchange_rate
when price_verify isnotnullthen price_verify * d.exchange_rate
else0end price_calculate
from(select a.product_code,
a.material_code
from(selectdistinct bom_superior.product_code,
bom_superior.material_code
from(select product_code,
material_code
from dim_bom) bom_superior
leftjoin(select product_code
from dim_bom) bom_subordinate
on bom_superior.material_code = bom_subordinate.product_code
where bom_subordinate.product_code isnull) a
innerjoin(selectdistinct bom_subordinate.product_code prod_code
from(select product_code
from dim_bom) bom_subordinate
leftjoin(select material_code
from dim_bom) bom_superior
on bom_subordinate.product_code = bom_superior.material_code
where bom_superior.material_code isnull) b
on a.product_code = b.prod_code) a
leftjoin(SELECT material_code mat_code,
currency currency_cost,# 库存成本
amount_price price_cost
from clip_ods.price_cost) b
on a.material_code = b.mat_code
leftjoin(SELECT material_code mat_code,
currency currency_verify,# 核价
amount_price price_verify
FROM clip_ods.price_verify) c
on a.material_code = c.mat_code
leftjoin(select currency_transaction,
exchange_rate
from clip_ods.currency_exchange
where currency_base ='CNY') d
on c.currency_verify = d.currency_transaction
leftjoin(select currency_transaction,
exchange_rate
from clip_ods.currency_exchange
where currency_base ='CNY') e
on b.currency_cost = e.currency_transaction),
tmp_semi as(select productid material_code_prod,
material_code,
currency_cost,
price_cost as price_cost_ori,casewhen price_cost isnullthen0else price_cost * e.exchange_rate endas price_cost,
currency_verify,
price_verify as price_verify_ori,casewhen price_verify isnullthen0else price_verify * d.exchange_rate endas price_verify,
price_cost * e.exchange_rate price_calculate
from(select a.productid,
a.material_code
from(select b.productid,
b.material_code
from(selectdistinct a.productid
from(select product_code productid
from dim_bom) a
leftjoin(select material_code subprodid
from dim_bom) b
on a.productid = b.subprodid
where b.subprodid isnull) a
innerjoin(selectdistinct product_code productid,
material_code
from dim_bom) b
on a.productid = b.productid) a
leftjoin(select material_code_prod,
material_code
from tmp_mat
where price_calculate isnotnull) b
on a.material_code = b.material_code
and a.productid = b.material_code_prod
where b.material_code isnull) a
leftjoin(SELECT material_code mat_code,
currency currency_cost,# 库存成本
amount_price price_cost
from clip_ods.price_cost) b
on a.material_code = b.mat_code
leftjoin(SELECT material_code mat_code,
currency currency_verify,# 核价
amount_price price_verify
FROM clip_ods.price_verify) c
on a.material_code = c.mat_code
leftjoin(select currency_transaction,
exchange_rate
from clip_ods.currency_exchange
where currency_base ='CNY') d
on c.currency_verify = d.currency_transaction
leftjoin(select currency_transaction,
exchange_rate
from clip_ods.currency_exchange
where currency_base ='CNY') e
on b.currency_cost = e.currency_transaction)select material_code_prod,
material_code,
currency_cost,
price_cost_ori,
price_cost,
currency_verify,
price_verify_ori,
price_verify,
price_calculate
from tmp_prod
unionselect material_code_prod,
material_code,
currency_cost,
price_cost_ori,
price_cost,
currency_verify,
price_verify_ori,
price_verify,
price_calculate
from tmp_mat
unionselect material_code_prod,
material_code,
currency_cost,
price_cost_ori,
price_cost,
currency_verify,
price_verify_ori,
price_verify,
price_calculate
from tmp_semi) initial_tmp
按BOM结构汇总
从最底层材料向上汇总，这里需要保证所有的最底层材料都有初始化后的单价，即使单价值为0；
因为如果下阶材料单价为空（Null）, 系统将无法判断此空值（Null）是否有意义，
而且，系统和开发人员所处的角色自身没有权限决定【单价为空时设置为0】，因为有可能这个单价另有其他来源。
如果财务相关对接人员有确定为空时单价设置为0，则应该在初始化时设置为0；
同时【下阶材料单价为空时不汇总】这一操作会在递归汇总中作为判断【该材料的下阶材料是否在之前的递归过程中计算完成】的标准；
最底层材料为空则会导致此材料所有的上阶材料【等待此材料计算出单价值后参与上阶材料的计算】；
insertinto dw_mt_bom_unit_price_mview(material_code_prod,
material_code,
currency_cost,
price_cost_ori,
price_cost,
currency_verify,
price_verify_ori,
price_verify,
price_calculate)select material_code_prod,
material_code,
currency_cost,
price_cost_ori,
price_cost,
currency_verify,
price_verify_ori,
price_verify,
price_calculate
from(select material_code_prod,
a.material_code,
currency_cost,
price_cost_ori,
price_cost,
currency_verify,
price_verify_ori,
price_verify,
a.price_calculate
from(select material_code,sum(bom_cost) price_calculate
from(select material_code,
bom_cost,avg(symbol_contrains)over(partitionby material_code) a2
from(select b.material_code,
amount_base,
PRICE_CALCULATE,
amount_base * PRICE_CALCULATE bom_cost,IF(PRICE_CALCULATE isnotnull,1,0) symbol_contrains
from(select product_code material_code,
material_code subprodid,
qpa amount_base
from dim_bom
where lvl =1) a
innerjoin(selectdistinct material_code
from dw_mt_bom_unit_price_mview
where PRICE_CALCULATE isnull) b
on a.material_code = b.material_code
leftjoin(selectdistinct material_code,
price_calculate
from dw_mt_bom_unit_price_mview) c
on a.subprodid = c.material_code) a) a
where a2 =1groupby material_code) a
innerjoin(select material_code_prod,
material_code,
currency_cost,
price_cost_ori,
price_cost,
currency_verify,
price_verify_ori,
price_verify
from dw_mt_bom_unit_price_mview) b
on a.material_code = b.material_code) iter_result
onduplicatekeyupdate currency_cost =values(currency_cost),
price_cost_ori=values(price_cost_ori),
currency_verify=values(currency_verify),
price_verify_ori=values(price_verify_ori),
price_verify=values(price_verify),
price_calculate=values(price_calculate);
汇总部分结果
使用存储过程运行的结果
参与计算的表结构
createtable currency_exchange
(
currency_transaction varchar(100)notnullcomment'交易币种',
currency_base varchar(100)notnullcomment'基础币种',
exchange_rate decimal(24,8)nullcomment'汇率',primarykey(currency_transaction, currency_base))comment'汇率明细';createtable price_cost
(
material_code varchar(100)notnullcomment'料号',
currency varchar(100)notnullcomment'交易币种',
amount_price decimal(24,8)nullcomment'价格',primarykey(material_code, currency))comment'成本单价明细';createtable price_verify
(
material_code varchar(100)notnullcomment'料号',
currency varchar(100)notnullcomment'交易币种',
amount_price decimal(24,8)nullcomment'价格',primarykey(material_code, currency))comment'核价单价明细';createtable clip_dwh.dim_bom
(
product_code varchar(100)notnullcomment'上阶料',
material_code varchar(100)notnullcomment'下阶料',
bom_hierarchy varchar(260)notnullcomment'BOM 分层',
lvl intnullcomment'分层等级',
qpa doublenullcomment'组成用量',
main varchar(100)nullcomment'主料',enablevarchar(100)nullcomment'启用',primarykey(product_code, material_code, bom_hierarchy))comment'BOM 明细';
完整存储过程代码
createdefiner= root@localhostprocedure recursion_bom_unit_price()BEGINDECLARE iter integerdefault1;truncatetable clip_dwh.dw_mt_bom_unit_price_mview;commit;insertinto dw_mt_bom_unit_price_mview(material_code_prod,
material_code,
currency_cost,
price_cost_ori,
price_cost,
currency_verify,
price_verify_ori,
price_verify,
price_calculate)select*from(with tmp_prod as(select material_code material_code_prod,
material_code,null currency_cost,nullas price_cost_ori,nullas price_cost,
c.currency_verify,
c.price_verify as price_verify_ori,
d.exchange_rate * IFNULL(c.price_verify,0)as price_verify,null price_calculate
from(selectdistinct bom_subordinate.product_code as material_code
# BOM 中只在 product_code 字段存在 不在 material_code 存在代表 该物料没有上阶物料 推断出本身是成品from(select product_code
from dim_bom) bom_subordinate
leftjoin(select material_code
from dim_bom) bom_superior
on bom_subordinate.product_code = bom_superior.material_code
where bom_superior.material_code isnull) a
# 采购核价leftjoin(SELECT material_code mat_code,
currency currency_verify,
amount_price price_verify
FROM clip_ods.price_verify) c
on a.material_code = c.mat_code
# 汇率leftjoin(select currency_transaction,
exchange_rate
from clip_ods.currency_exchange
where currency_base ='CNY') d
on c.currency_verify = d.currency_transaction),
tmp_mat as(select product_code material_code_prod,
material_code,
currency_cost,
price_cost as price_cost_ori,casewhen price_cost isnullthen0else price_cost * e.exchange_rate endas price_cost,
currency_verify,
price_verify as price_verify_ori,casewhen price_verify isnullthen0else price_verify * d.exchange_rate endas price_verify,casewhen price_cost isnotnullthen price_cost * e.exchange_rate
when price_verify isnotnullthen price_verify * d.exchange_rate
else0end price_calculate
from(select a.product_code,
a.material_code
from(selectdistinct bom_superior.product_code,
bom_superior.material_code
from(select product_code,
material_code
from dim_bom) bom_superior
leftjoin(select product_code
from dim_bom) bom_subordinate
on bom_superior.material_code = bom_subordinate.product_code
# BOM 中只在 material_code 字段存在 不在 product_code 存在代表 该物料没有下阶物料 推断出本身是最底层材料where bom_subordinate.product_code isnull) a
innerjoin(selectdistinct bom_subordinate.product_code prod_code
from(select product_code
from dim_bom) bom_subordinate
leftjoin(select material_code
from dim_bom) bom_superior
on bom_subordinate.product_code = bom_superior.material_code
# 与 BOM中的最上层成品 join 保证不会有多余的材料料号参与where bom_superior.material_code isnull) b
on a.product_code = b.prod_code) a
leftjoin(SELECT material_code mat_code,
currency currency_cost,# 库存成本
amount_price price_cost
from clip_ods.price_cost) b
on a.material_code = b.mat_code
leftjoin(SELECT material_code mat_code,
currency currency_verify,# 核价
amount_price price_verify
FROM clip_ods.price_verify) c
on a.material_code = c.mat_code
leftjoin(select currency_transaction,
exchange_rate
from clip_ods.currency_exchange
where currency_base ='CNY') d
on c.currency_verify = d.currency_transaction
leftjoin(select currency_transaction,
exchange_rate
from clip_ods.currency_exchange
where currency_base ='CNY') e
on b.currency_cost = e.currency_transaction),
tmp_semi as(select productid material_code_prod,
material_code,
currency_cost,
price_cost as price_cost_ori,casewhen price_cost isnullthen0else price_cost * e.exchange_rate endas price_cost,
currency_verify,
price_verify as price_verify_ori,casewhen price_verify isnullthen0else price_verify * d.exchange_rate endas price_verify,
price_cost * e.exchange_rate price_calculate
from(select a.productid,
a.material_code
# 然后是 成品 和 材料 之间 的 半成品料号，使用join保证不会有多余料号参与from(select b.productid,
b.material_code
from(selectdistinct a.productid
from(select product_code productid
from dim_bom) a
leftjoin(select material_code subprodid
from dim_bom) b
on a.productid = b.subprodid
where b.subprodid isnull) a
innerjoin(selectdistinct product_code productid,
material_code
from dim_bom) b
on a.productid = b.productid) a
leftjoin(select material_code_prod,
material_code
from tmp_mat
where price_calculate isnotnull) b
on a.material_code = b.material_code
and a.productid = b.material_code_prod
where b.material_code isnull) a
leftjoin(SELECT material_code mat_code,
currency currency_cost,# 库存成本
amount_price price_cost
from clip_ods.price_cost) b
on a.material_code = b.mat_code
leftjoin(SELECT material_code mat_code,
currency currency_verify,# 核价
amount_price price_verify
FROM clip_ods.price_verify) c
on a.material_code = c.mat_code
leftjoin(select currency_transaction,
exchange_rate
from clip_ods.currency_exchange
where currency_base ='CNY') d
on c.currency_verify = d.currency_transaction
leftjoin(select currency_transaction,
exchange_rate
from clip_ods.currency_exchange
where currency_base ='CNY') e
on b.currency_cost = e.currency_transaction)# 成品、材料、半成品 结构相同，使用union拼接再插入select material_code_prod,
material_code,
currency_cost,
price_cost_ori,
price_cost,
currency_verify,
price_verify_ori,
price_verify,
price_calculate
from tmp_prod
unionselect material_code_prod,
material_code,
currency_cost,
price_cost_ori,
price_cost,
currency_verify,
price_verify_ori,
price_verify,
price_calculate
from tmp_mat
unionselect material_code_prod,
material_code,
currency_cost,
price_cost_ori,
price_cost,
currency_verify,
price_verify_ori,
price_verify,
price_calculate
from tmp_semi) initial_tmp;commit;WHILE iter <=10DOset iter = iter +1;insertinto dw_mt_bom_unit_price_mview(material_code_prod,
material_code,
currency_cost,
price_cost_ori,
price_cost,
currency_verify,
price_verify_ori,
price_verify,
price_calculate)select material_code_prod,
material_code,
currency_cost,
price_cost_ori,
price_cost,
currency_verify,
price_verify_ori,
price_verify,
price_calculate
from(select material_code_prod,
a.material_code,
currency_cost,
price_cost_ori,
price_cost,
currency_verify,
price_verify_ori,
price_verify,
a.price_calculate
from(select material_code,sum(bom_cost) price_calculate
from(select material_code,
bom_cost,avg(symbol_contrains)over(partitionby material_code) a2
# 使用 平均数 保证 此 材料 的所有下阶材料都存在，如果不存在计算出来的材料单价会有问题from(select b.material_code,
amount_base,
PRICE_CALCULATE,
amount_base * PRICE_CALCULATE bom_cost,IF(PRICE_CALCULATE isnotnull,1,0) symbol_contrains
from(select product_code material_code,
material_code subprodid,
qpa amount_base
from dim_bom
where lvl =1) a
innerjoin(selectdistinct material_code
from dw_mt_bom_unit_price_mview
where PRICE_CALCULATE isnull) b
on a.material_code = b.material_code
leftjoin(selectdistinct material_code,
price_calculate
from dw_mt_bom_unit_price_mview) c
on a.subprodid = c.material_code) a) a
where a2 =1groupby material_code) a
innerjoin(select material_code_prod,
material_code,
currency_cost,
price_cost_ori,
price_cost,
currency_verify,
price_verify_ori,
price_verify
from dw_mt_bom_unit_price_mview) b
on a.material_code = b.material_code) iter_result
# 出现重复键时覆盖，因为我们一般在初始化时已写入此主键，覆盖重复键则是为了更新onduplicatekeyupdate currency_cost =values(currency_cost),
price_cost_ori=values(price_cost_ori),
currency_verify=values(currency_verify),
price_verify_ori=values(price_verify_ori),
price_verify=values(price_verify),
price_calculate=values(price_calculate);ENDWHILE;commit;END;
本文转载自: https://blog.csdn.net/qq_38605114/article/details/140507401
版权归原作者 The_Singing_Towers 所有， 如有侵权，请联系我们删除。
版权归原作者 The_Singing_Towers 所有， 如有侵权，请联系我们删除。