一.项目介绍
最近新改进了一版生产齐套性看板，应用到另外一个工厂上，因此再次将其中计算模型设计、看板展示介绍清楚。
. .
齐套性模型，主要围绕一份【产品生产计划表】展开，一份生产计划会大概覆盖未来60天的成品生产计划需求。需要将排产计划表按照时【时间序列】+【成品BOM层级】展开，【成品BOM层级】指的是“成品→待包品→物料”。 在展开到待包品时，考虑待包品要前置5/7天准备好。
然后再次将展开到待包品层级的需求计划表，按照“待包品→物料”的层级展开，在展开到物料时，考虑物料要前置7/9天准备好。
当成品的需求计划展开到物料层级的需求时，这个时候可以反向去考虑：根据当前成品、待包品、物料的库存&在途库存，能否满足这份生产计划去生产成品？中间哪些物料会发生短缺？这份表格会反向指导业务告诉业务人员，哪些物料可以提前准备；还可以对生产计划表进行调整，给需要排产的成品排个生产优先级。
二.实现过程的难点
在供应链相关计算模型中，有一个类似计算每天产销存的问题，用PYthon好实现，但用Excel、sql等不好实现。这个问题就是：已知第一天(11月28日)的期初库存a，即将入库b,消耗量c,那本日(11.28)库存结余A=max(a+b-c,0); 那么第二天(11月29日)的期初库存a1=A=max(a+b-c,0)。
这就是一个简单的递归过程，PYthon可以写循环实现。但当时我们的项目以FineBI\dataworks调度等工具为主，FineBI不支持python代码输入的方式写入,因此最后只能用sql语言在dataworks里面写调度。
1.递归实现的功能
其实把逻辑写好，丢AI里面去写，然后再改也行。dataworks里面PYthon模块真的不好用。
#re = DataFrame(o.get_table('ads_wlqt_transto_daibaopin'))
# 1.日期升序
# 2.循环
# 遍历主键，【工厂+产品编码+BOM版本+子项sku】，
# {
# 对每个【工厂+产品编码+BOM版本+子项sku】
# 如果【时间标签】=当前，
# 【本日期初库存】=该行的【待包品库存】+该行的【待包品未入库】
# 【本日库存结余】=该行的【待包品库存】+该行的【待包品未入库】-该行的【转待包品需求量】；（【本日库存结余】如果小于0，则赋值为0）
# 【本日净需求量】=该行的【转待包品需求量】-该行的【待包品库存】-该行的【待包品未入库】；（【本日净需求量】如果小于0，则赋值为0）
#
# 如果【时间标签】=未来，
# 【本日期初库存】=上一行的【本日库存结余】+该行的【待包品未入库】
# 【本日库存结余】=上一行的【本日库存结余】+该行的【待包品未入库】-该行的【转待包品需求量】；（【本日库存结余】如果小于0，则赋值为0）
# 【本日净需求量】=该行的【转待包品需求量】-上一行的【本日库存结余】-该行的【待包品未入库】；（【本日净需求量】如果小于0，则赋值为0）
# }
写完之后半天跑不出结果，避雷dataworks里面写PYthon。
2.最后用SQL里面写的递归
反正这种自连接的写法，我很少遇到。
-- 设置递归CTE最大迭代次数
set odps.sql.rcte.max.iterate.num=100;
-- 初始化排序临时表
DROP TABLE IF EXISTS temp_ads_wlqt_transto_yuanliao_sorted;
CREATE TABLE IF NOT EXISTS temp_ads_wlqt_transto_yuanliao_sorted AS
SELECT *
FROM ads_wlqt_transto_yuanliao
ORDER BY 工厂, 产品编码, bom版本, 子项sku编码, 原料sku编码, 日期
;
-- 为每个分组添加行号
DROP TABLE IF EXISTS temp_ads_wlqt_transto_yuanliao_with_rownum;
CREATE TABLE IF NOT EXISTS temp_ads_wlqt_transto_yuanliao_with_rownum AS
SELECT *,
ROW_NUMBER() OVER (PARTITION BY 工厂, 产品编码, bom版本, 子项sku编码, 原料sku编码 ORDER BY 日期) AS rn
FROM temp_ads_wlqt_transto_yuanliao_sorted
;
-- 计算本日期初库存、本日库存结余和本日净需求量
DROP TABLE IF EXISTS ads_wlqt_transto_yuanliao_calculated;
CREATE TABLE IF NOT EXISTS ads_wlqt_transto_yuanliao_calculated AS
WITH RECURSIVE cte AS (
-- 基础部分：处理每个分组的第一行
SELECT 工厂,
产品编码,
bom版本,
子项sku编码,
原料sku编码,
日期,
时间标签,
原料库存,
原料未入库,
转原料需求量,
rn,
-- 本日期初库存
CASE WHEN 时间标签 = '当前' THEN
原料库存 + COALESCE(原料未入库, 0)
ELSE 0 -- 初始值，会在递归中被覆盖
END AS 本日期初库存,
-- 本日库存结余
CASE WHEN 时间标签 = '当前' THEN
CASE WHEN 原料库存 + COALESCE(原料未入库, 0) - COALESCE(转原料需求量, 0) < 0 THEN 0
ELSE 原料库存 + COALESCE(原料未入库, 0) - COALESCE(转原料需求量, 0)
END
ELSE 0 -- 初始值
END AS 本日库存结余,
-- 本日净需求量
CASE WHEN 时间标签 = '当前' THEN
CASE WHEN 转原料需求量 - 原料库存 - COALESCE(原料未入库, 0) < 0 THEN 0
ELSE 转原料需求量 - 原料库存 - COALESCE(原料未入库, 0)
END
ELSE 0 -- 初始值
END AS 本日净需求量
FROM temp_ads_wlqt_transto_yuanliao_with_rownum
WHERE rn = 1
UNION ALL
-- 递归部分：处理后续行
SELECT t.工厂,
t.产品编码,
t.bom版本,
t.子项sku编码,
t.原料sku编码,
t.日期,
t.时间标签,
t.原料库存,
t.原料未入库,
t.转原料需求量,
t.rn,
-- 本日期初库存
CASE WHEN t.时间标签 = '未来' THEN
c.本日库存结余 + COALESCE(t.原料未入库, 0)
ELSE
t.原料库存 + COALESCE(t.原料未入库, 0)
END AS 本日期初库存,
-- 本日库存结余
CASE WHEN t.时间标签 = '未来' THEN
CASE WHEN c.本日库存结余 + COALESCE(t.原料未入库, 0) - COALESCE(t.转原料需求量, 0) < 0 THEN 0
ELSE c.本日库存结余 + COALESCE(t.原料未入库, 0) - COALESCE(t.转原料需求量, 0)
END
ELSE
CASE WHEN t.原料库存 + COALESCE(t.原料未入库, 0) - COALESCE(t.转原料需求量, 0) < 0 THEN 0
ELSE t.原料库存 + COALESCE(t.原料未入库, 0) - COALESCE(t.转原料需求量, 0)
END
END AS 本日库存结余,
-- 本日净需求量
CASE WHEN t.时间标签 = '未来' THEN
CASE WHEN t.转原料需求量 - c.本日库存结余 - COALESCE(t.原料未入库, 0) < 0 THEN 0
ELSE t.转原料需求量 - c.本日库存结余 - COALESCE(t.原料未入库, 0)
END
ELSE
CASE WHEN t.转原料需求量 - t.原料库存 - COALESCE(t.原料未入库, 0) < 0 THEN 0
ELSE t.转原料需求量 - t.原料库存 - COALESCE(t.原料未入库, 0)
END
END AS 本日净需求量
FROM temp_ads_wlqt_transto_yuanliao_with_rownum t
JOIN cte c
ON t.工厂 = c.工厂
AND t.产品编码 = c.产品编码
AND t.bom版本 = c.bom版本
AND t.子项sku编码 = c.子项sku编码
AND t.原料sku编码 = c.原料sku编码
AND t.rn = c.rn + 1
)
SELECT *,
GETDATE() AS etl_time
FROM cte
ORDER BY 工厂, 产品编码, bom版本, 子项sku编码, 原料sku编码, 日期;
三.BI展示
最终在线版链接，物料齐套性项目：pcdemo.finebi.com/webroot/dec…
1.成品齐套
展示成品缺货信息、与成品缺货相关的待包物料信息。
①点击周维度的成品不齐套率柱状图，可联动下面表格【成品缺货明细】【成品关联的待包品】【成品关联的物料】
②点击【成品缺货明细】的日期、BOM版本，可联动右侧的待包品缺货日期及数量、物料缺货日期及数量。
2.物料齐套
物料齐套看板主要展示按照现有的成品生产计划，展开到BOM层级，看各个物料缺货情况，及其缺货会导致待包品缺货情况、成品缺货情况。
