需汇总父项数量以计算缩进式物料清单总用量

我完全懂你现在的困境——折腾好几个小时在BOM的累计数量计算上却没进展，真的让人头大！你需要计算的ROLLED_PARENT_QTY是当前部件所有上层父项数量的乘积，TOTAL_QTY则是这个乘积再乘以当前部件的comp_qty，对吧？

先回顾下你已经实现的获取PARENT_QTY的SQL：

select 
    end_part_id, 
    sort_seq_no, 
    indented_lvl, 
    comp_qty, 
    (select distinct first_value(a.comp_qty) over (order by a.sort_seq_no desc, TRIM(a.indented_lvl) desc) 
     from report_table a 
     where a.end_part_id = b.end_part_id 
       and a.sort_seq_no < b.sort_seq_no 
       and TRIM(a.indented_lvl) < TRIM(b.indented_lvl)) as "PARENT_QTY" 
from report_table b

要新增那两个字段，我们得用Oracle的层次查询（CONNECT BY）来追踪每个部件的完整父项链，然后计算乘积。因为你用的是Oracle 10.2，下面提供两种可行的解决方案：

方法1：使用自定义函数（直观易懂） #

首先创建一个用来拆分字符串并计算乘积的函数：

CREATE OR REPLACE FUNCTION multiply_str(p_str VARCHAR2, p_delimiter VARCHAR2) RETURN NUMBER IS
    v_total NUMBER := 1;
    v_token VARCHAR2(100);
    v_pos NUMBER;
BEGIN
    IF p_str IS NULL THEN RETURN 1; END IF;
    v_pos := INSTR(p_str, p_delimiter);
    WHILE v_pos > 0 LOOP
        v_token := SUBSTR(p_str, 1, v_pos - 1);
        v_total := v_total * TO_NUMBER(v_token);
        p_str := SUBSTR(p_str, v_pos + LENGTH(p_delimiter));
        v_pos := INSTR(p_str, p_delimiter);
    END LOOP;
    v_total := v_total * TO_NUMBER(p_str);
    RETURN v_total;
END;
/

然后用层次查询和这个函数来计算目标字段：

SELECT 
    end_part_id,
    sort_seq_no,
    indented_lvl,
    comp_qty,
    PARENT_QTY,
    -- 顶层部件的ROLLED_PARENT_QTY为1，其他为所有父项数量的乘积
    CASE WHEN TRIM(indented_lvl) = '1' THEN 1
         ELSE multiply_str(SYS_CONNECT_BY_PATH(comp_qty, '*'), '*') END AS "ROLLED_PARENT_QTY",
    -- TOTAL_QTY = ROLLED_PARENT_QTY * 当前部件数量
    CASE WHEN TRIM(indented_lvl) = '1' THEN comp_qty
         ELSE multiply_str(SYS_CONNECT_BY_PATH(comp_qty, '*'), '*') * comp_qty END AS "TOTAL_QTY"
FROM (
    SELECT 
        b.end_part_id,
        b.sort_seq_no,
        b.indented_lvl,
        b.comp_qty,
        (select distinct first_value(a.comp_qty) over (order by a.sort_seq_no desc, TRIM(a.indented_lvl) desc) 
         from report_table a 
         where a.end_part_id = b.end_part_id 
           and a.sort_seq_no < b.sort_seq_no 
           and TRIM(a.indented_lvl) < TRIM(b.indented_lvl)) as "PARENT_QTY",
        -- 找到当前部件的直接父项（层级减1且排序号最近的项）
        (SELECT MAX(a.sort_seq_no) 
         FROM report_table a 
         WHERE a.end_part_id = b.end_part_id 
           AND a.sort_seq_no < b.sort_seq_no 
           AND TRIM(a.indented_lvl) = TRIM(b.indented_lvl) - 1) AS parent_sort_seq
    FROM report_table b
) t
-- 构建BOM的层次结构
CONNECT BY PRIOR sort_seq_no = parent_sort_seq
START WITH parent_sort_seq IS NULL
-- 按预期顺序输出
ORDER BY end_part_id, sort_seq_no;

方法2：不使用自定义函数（利用数学函数） #

如果不想创建自定义函数，可以用LN（自然对数）和EXP（指数）的特性——乘积的对数等于对数的和，这样就能用窗口函数计算累计和再转成乘积：

SELECT 
    end_part_id,
    sort_seq_no,
    indented_lvl,
    comp_qty,
    PARENT_QTY,
    NVL(EXP(SUM(LN(comp_qty)) OVER (PARTITION BY end_part_id 
                                    ORDER BY sort_seq_no 
                                    ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING 
                                    WHERE TRIM(indented_lvl) < CURRENT_TRIM_LVL)), 1) AS "ROLLED_PARENT_QTY",
    NVL(EXP(SUM(LN(comp_qty)) OVER (PARTITION BY end_part_id 
                                    ORDER BY sort_seq_no 
                                    ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING 
                                    WHERE TRIM(indented_lvl) < CURRENT_TRIM_LVL)), 1) * comp_qty AS "TOTAL_QTY"
FROM (
    SELECT 
        b.end_part_id,
        b.sort_seq_no,
        b.indented_lvl,
        TRIM(b.indented_lvl) AS CURRENT_TRIM_LVL,
        b.comp_qty,
        (select distinct first_value(a.comp_qty) over (order by a.sort_seq_no desc, TRIM(a.indented_lvl) desc) 
         from report_table a 
         where a.end_part_id = b.end_part_id 
           and a.sort_seq_no < b.sort_seq_no 
           and TRIM(a.indented_lvl) < TRIM(b.indented_lvl)) as "PARENT_QTY"
    FROM report_table b
) t
ORDER BY end_part_id, sort_seq_no;

预期结果验证 #

运行上述SQL后，你会得到和预期一致的结果：

END_PART_ID SORT_SEQ_NO INDENTED_LVL COMP_QTY PARENT_QTY ROLLED_PARENT_QTY TOTAL_QTY
PARTX       1           1            2        1          1                 2
PARTX       2           2            5        2          2                 10
PARTX       3           3            2        5          10                20
PARTX       4           4            1        2          20                20
PARTX       5           5            1        2          20                20
PARTX       6           6            1        2          20                20
PARTX       7           5            4        1          20                80
PARTX       8           6            1        4          80                80
PARTX       9           2            7        2          2                 14
PARTX       10          3            2        7          14                28
PARTX       11          3            2        7          14                28
PARTX       12          4            1        2          28                28
PARTX       13          4            1        2          28                28
PARTX       14          3            8        7          14                112
PARTX       15          1            1        1          1                 1
PARTX       16          2            7        1          1                 7
PARTX       17          3            2        7          7                 14
PARTX       18          3            2        7          7                 14
PARTX       19          4            1        2          14                14
PARTX       20          4            1        2          14                14

内容的提问来源于stack exchange，提问作者David L