oracle 帮助计算分层数据集中的复数总和

Help calculating complex sum in hierarchical dataset

提问by Randy

I have an interesting SQL problem. I have a hierarchic table of parts that make a bill of material. similar to this:

我有一个有趣的 SQL 问题。我有一个制作物料清单的零件层次表。类似于：

ASSEMBLY
---------
parent_part_id
part_id
quantity

I get the hierarchy of this structure with a query like this:

我通过这样的查询获得了这个结构的层次结构：

SELECT level, part_id, quantity
from assembly
start with parent_part_id = 1
connect by parent_part_id = prior part_id;

the output might look like this:

输出可能如下所示：

level  part_id  quantity
-----  -------  ---------
1      2        2
2      3        10
1      4        2
2      5        1    
3      3        5

so far so good.

到目前为止，一切都很好。

the question is this: how do I calculate the total number of each part required in order to make the top level assembly (part 1)?

问题是：我如何计算制作顶级组件（第 1 部分）所需的每个零件的总数？

Grouping this result set by part and summing the quantity is not correct, since the quantity should be multiplied by the quantity of the part immediately above the current part in the hierarchy, recursively up the tree.

将这个结果集按部件分组并对数量求和是不正确的，因为数量应该乘以层次结构中当前部件正上方的部件数量，在树中递归向上。

I am thinking this is a LAG function, but having trouble visualizing it.

我认为这是一个 LAG 函数，但无法对其进行可视化。

edit: expected results:

编辑：预期结果：

part_id  quantity
-------  --------
2        2
3        30
4        2
5        2

more edit: i get interesting results with this query

更多编辑：我通过这个查询得到了有趣的结果

SELECT rownum, level lvl, part_id, quantity, unit_of_measure
                , connect_by_isleaf || sys_connect_by_path(quantity,'*') math
            from assembly
            start with parent_part_id = 1
            connect by parent_part_id = prior part_id

the math column returns a string representation of the calculation i want to perform :) for instance it may say:

数学列返回我要执行的计算的字符串表示形式 :) 例如它可能会说：

1*1*2*10

or something similar and appropriate... perhaps making a function to parse this and return the result will solve the problem.. anyone think this is outrageous?

或类似和适当的东西......也许制作一个函数来解析它并返回结果将解决问题......有人认为这太离谱了吗？

回答by René Nyffenegger

In Oracle 11 R2 its possible with a common table expression:

在 Oracle 11 R2 中，它可以使用common table expression：

The test data:

测试数据：

--  drop table assembly;

create table assembly (
  part_id              number, 
  parent_part_id       number,
  quantity             number
);

insert into assembly values (2, 1,  2);
insert into assembly values (3, 2, 10);
insert into assembly values (4, 1,  2);
insert into assembly values (5, 4,  1);
insert into assembly values (3, 5,  5);

The select statement:

选择语句：

select 
  part_id, 
  sum(quantity_used) as quantity
from (
  with assembly_hier (lvl, part_id, quantity, quantity_used) as (
    select 
      1        lvl,
      part_id,
      quantity ,
      quantity        quantity_used
    from
      assembly
    where
      parent_part_id = 1 
  union all
    select
      assembly_hier.lvl      + 1 lvl,
      assembly     .part_id,
      assembly     .quantity,
      assembly_hier.quantity_used * assembly.quantity quantity_used
    from
      assembly_hier, assembly
    where
      assembly_hier.part_id = assembly.parent_part_id
  )
  select * from assembly_hier
)
group by part_id
order by part_id;

EditPrior to Ora11R2, it might work with a model clause:

在 Ora11R2 之前编辑，它可能适用于model clause：

select 
  part_id,
  sum(quantity) quantity 
from (
  select
    lvl
    parent_part_id,
    part_id,
    quantity
  from (
    select 
      lvl,
      parent_part_id,
      part_id,
      quantity
    from (
      select  
        rownum r, 
        level lvl, 
        parent_part_id,
        part_id, 
        quantity
      from 
        assembly
      start with parent_part_id = 1
      connect by parent_part_id = prior part_id
    )
  )
  model
    dimension by (lvl, part_id)
    measures (quantity, parent_part_id)
    rules upsert (
       quantity[     any, any          ] order by lvl, part_id =   quantity[cv(lvl)  , cv(part_id)] * 
                                          nvl( quantity[cv(lvl)-1,    parent_part_id[cv(lvl), cv(part_id)] ], 1)
    )
)
group by part_id
order by part_id;

Edit IIAnother possibility would be to sum the logarithms of quantity and then take the sum's exponent:

编辑 II另一种可能性是对数量的对数求和，然后取总和的指数：

select 
  part_id,
  sum(quantity) quantity
from (
  select 
    part_id,
    exp(sum(quantity_ln) over (partition by new_start order by r)) quantity
  from (
    select 
      r,
      lvl,
      part_id,
      quantity_ln,
      sum(new_start) over(order by r) new_start
    from (
      select 
        rownum r, 
        level lvl, 
        part_id, 
        ln(quantity) quantity_ln,
        nvl(lag(connect_by_isleaf,1) over (order by rownum),0) new_start
      from assembly
      start with parent_part_id = 1
      connect by parent_part_id = prior part_id
    )
  )
)
group by part_id
order by part_id
;

回答by Randy

i ended up here: this works on oracle 10 and 11, the connect_by_isleaf can be used to adjust the logic whether you want to sum only the leafs, or all nodes.

我在这里结束：这适用于 oracle 10 和 11，connect_by_isleaf 可用于调整逻辑，无论您只想对叶子或所有节点求和。

select part_id,  new_rec.quantity*sum(math_calc( math,2)) m, unit_of_measure
from ( SELECT rownum, level lvl, part_id, quantity, unit_of_measure
            , connect_by_isleaf || sys_connect_by_path(quantity,'*') math
from assembly
start with parent_part_id = new_rec.part_id
connect by parent_part_id = prior part_id ) p
group by part_id, unit_of_measure