帮助计算层次数据集中的复杂和 [英] Help calculating complex sum in hierarchical dataset

问题描述

我有一个有趣的SQL问题.我有一个构成物料清单的零件层次结构表.与此类似:

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

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.

问题是这样的:如何计算制造顶层组件(第1部分)所需的每个零件的总数?

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)?

将结果集按零件分组并求和是不正确的，因为应将数量乘以层次结构中当前零件正上方的零件的数量，然后递归到树上.

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.

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

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

预期结果:

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?

推荐答案

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

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

测试数据:

--  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);

select语句:

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;

编辑在Ora11R2之前，它可以与model clause一起使用:

Edit Prior to Ora11R2, it might work with a 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;

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

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

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
;

这篇关于帮助计算层次数据集中的复杂和的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！