数据仓库实践：使用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明细';

部分数据

主料

替代料

基础结构汇总

在使用BOM汇总时，一般需要BOM明细中的所有关系，

所以从局部看，单个成品与材料是一对多关系，

从全局看成品，半成品和最底层材料，是多对多关系。

所以我们需要将所有的关系找出来，然后经过一番去重和重新汇总。

以下是部分代码

# 基础结构汇总
select product_code,
       material_code,
       qpa,
       main,
       enable
from (select product_code,
             material_code,
             qpa,
             main,
             enable,
             # 汇总之后有一部分product_code, material_code会因为同时状态为有效和无效
             # 这里状态有效优先
             row_number() over (partition by product_code, material_code,enable order by case when main = 'Y' then 1 else 2 end) rn
      from (select product_code,
                   material_code,
                   # 相同产品和相同材料的情况下按product_code, material_code求和
                   # 这样能够得出在企业全局下此产品使用此材料的组成用量
                   # ，可按实际情况灵活使用
                   sum(qpa) as qpa,
                   'Y'         main,
                   enable
            from clip_ods.bom_main
            where enable = 'Y'
            group by product_code,
                     material_code,
                     enable
            # 数据会被去重，可按实际情况灵活使用
            union
            select product_code,
                   alternative_code,
                   qpa / alternative_qpa as qpa,
                   'N'                      main,
                   enable
            from clip_ods.bom_alternative
            where enable = 'Y') bom_ori) bom_unique
where rn = 1

递归

正常BOM不会只有一层，因为材料的上阶料不一定直接是最终成品，有可能是半成品，会形成【材料-半成品-成品】的关系，

所以为了完整展出每层关系，我们需要使用递归的方式，将基础结构多次汇总，

示例中将使用MySQL 语法，

除了用于循环的部分关键词，汇总的语句代码是最基础的SQL代码，可以使用其他的数据库方言翻译，比如Oracle。

建立存储过程

create
    definer = root@localhost procedure recursion_split_bom()
BEGIN
    # 定义递归层级
    DECLARE iter integer default 1;
    # 删除原有数据，重新写入全量数据
    truncate table clip_dwh.dim_bom;

    commit;
    # 基础结构直接写入
    insert into clip_dwh.dim_bom(product_code, material_code, bom_hierarchy, lvl, qpa, main, enable)
    select product_code,
           material_code,
           concat(product_code, '_', material_code) bom_hierarchy,
           iter                                     lvl,
           qpa,
           main,
           enable
    from (select product_code,
                 material_code,
                 qpa,
                 main,
                 enable
          from (select product_code,
                       material_code,
                       qpa,
                       main,
                       enable,
                       row_number() over (partition by product_code, material_code,enable order by case when main = 'Y' then 1 else 2 end) rn
                from (select product_code,
                             material_code,
                             sum(qpa) as qpa,
                             'Y'         main,
                             enable
                      from clip_ods.bom_main
                      where enable = 'Y'
                      group by product_code,
                               material_code,
                               enable
                      union
                      select product_code,
                             alternative_code,
                             qpa / alternative_qpa as qpa,
                             'N'                      main,
                             enable
                      from clip_ods.bom_alternative
                      where enable = 'Y') bom_ori) bom_unique
          where rn = 1) bom_basic;
    commit;
    
    # 每一次基础结构和已经写入的数据join关联都能关联出新一层
    # iter 循环层数视实际数据而定，一般是最深BOM的层数
    WHILE iter <= 10
        DO
            set iter = iter + 1;
            insert into clip_dwh.dim_bom(product_code, material_code, bom_hierarchy, lvl, qpa, main, enable)
            select bom_result.product_code,
                   bom_iter.material_code,
                   concat(bom_result.bom_hierarchy, '_', bom_iter.material_code) as bom_hierarchy,
                   iter                                                          as lvl,
                   bom_result.qpa * bom_iter.qpa                                 as qpa,
                   IF(bom_result.main = 'N', 'N', bom_iter.main)                 as main,
                   IF(bom_result.enable = 'N', 'N', bom_iter.enable)             as enable
            from (select product_code,
                         material_code,
                         qpa,
                         main,
                         enable
                  from (select product_code,
                               material_code,
                               qpa,
                               main,
                               enable,
                               row_number() over (partition by product_code, material_code,enable order by case when main = 'Y' then 1 else 2 end) rn
                        from (select product_code,
                                     material_code,
                                     sum(qpa) as qpa,
                                     'Y'         main,
                                     enable
                              from clip_ods.bom_main
                              where enable = 'Y'
                              group by product_code,
                                       material_code,
                                       enable
                              union
                              select product_code,
                                     alternative_code,
                                     qpa / alternative_qpa as qpa,
                                     'N'                      main,
                                     enable
                              from clip_ods.bom_alternative
                              where enable = 'Y') bom_ori) bom_unique
                  where rn = 1) bom_iter
                     inner join(select product_code,
                                       material_code,
                                       bom_hierarchy,
                                       lvl,
                                       qpa,
                                       main,
                                       enable
                                from clip_dwh.dim_bom) bom_result
                               on bom_iter.product_code = bom_result.material_code
            where bom_result.lvl >= iter - 1;
        END WHILE;
    commit;
END;

部分结果

结果数据结构

create table clip_dwh.dim_bom
(
    product_code  varchar(100) not null comment '上阶料',
    material_code varchar(100) not null comment '下阶料',
    bom_hierarchy varchar(260) not null comment 'BOM 分层',
    lvl           int          null comment '分层等级',
    qpa           double       null comment '组成用量',
    main          varchar(100) null comment '主料',
    enable        varchar(100) null comment '启用',
    primary key (product_code, material_code, bom_hierarchy)
);