数据仓库实践：使用SQL汇总BOM数据分析维度

背景

物料清单（Bill of Materials, BOM）主数据是指在制造业中，用于描述产品结构、组件和制造工艺的核心数据。

将BOM主数据引入数仓，可以保证数据流转和分析过程中保证与制造过程的一致，以确保分析结果的准确。

在ERP数据库或者PLM数据库中保存的BOM清单一般是一对多关系，

但从企业全局看，上阶料产品和下阶料（子阶料）一般是多对多的关系；

需要兼容的关系一般有半成品、替代料、共用料等；

BOM数据分析维度：重新汇总拓展BOM

在这个重新汇总的BOM维度数据中，需要兼容的汇总逻辑有：

BOM 的层次结构

全局BOM中的环形结构

替代料

企业全局下材料和产品组成用量总和

BOM 层次结构

平常的BOM明细数据中一般只有一层,这是为了保证单一和准确，

在Java、Python等可以使用循环递归的语言环境下，计算和存储成本非常低，

但对于常用SQL的数据分析场景下，SQL不擅长递归，所以一般需要将递归汇总的结果直接落地成表。

BOM 的环形结构

这里的环形结构是指在循环展BOM层次时，同一成品会通过不同路径同一材料。

在这中情况下，需要根据实际的情况求和或者去重。

替代料

全局BOM中不只是主料与成品有关系，替代料也是汇总计算的关键一环,所以在汇总代码中也要考虑。

数据读取：BOM明细生成

结构

手动模仿一般情况下BOM的数据明细表结构，一个是主料明细，一个是替代料明细；

以下是对应表DDL语句

create table if not exists clip_ods.bom_alternative
(
    product_code     varchar(100) not null comment '产品物料料号',
    main_code        varchar(100) not null comment '主料物料料号',
    alternative_code varchar(100) not null comment '替代物料料号',
    qpa              double       null comment '主料组成用量',
    alternative_qpa  double       null comment '替代用量',
    enable           varchar(100) null comment '启用状态',
    primary key (product_code, main_code, alternative_code)
) comment '替代料BOM明细';

create table if not exists clip_ods.bom_main
(
    product_code  varchar(100) not null comment '产品物料料号',
    material_code varchar(100) not null comment '子阶物料料号',
    qpa           double       null comment '组成用量',
    enable        varchar(100) null comment '启用状态',
    primary key (product_code, material_code)
) comment '主料BOM明细';

部分数据

主料