Помогите рассчитать комплексную сумму в иерархическом наборе данных

У меня есть интересная проблема с SQL. У меня есть иерархическая таблица деталей, которые составляют спецификацию материалов. похоже на это:

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

Я получаю иерархию этой структуры с помощью такого запроса:

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

вывод может выглядеть так:

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

Все идет нормально.

вопрос в следующем: как рассчитать общее количество каждой детали, необходимой для сборки верхнего уровня (деталь 1)?

Группировать этот результирующий набор по деталям и суммировать количество некорректно, так как количество должно умножаться на количество детали непосредственно над текущей деталью в иерархии, рекурсивно вверх по дереву.

Я думаю, что это функция LAG, но не могу ее визуализировать.

редактировать: ожидаемые результаты:

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

больше редактирования: я получаю интересные результаты с этим запросом

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

столбец math возвращает строковое представление вычисления, которое я хочу выполнить :) например, он может сказать:

1*1*2*10

или что-то подобное и подходящее ... возможно, создание функции для анализа этого и возврата результата решит проблему ... кто-нибудь думает, что это возмутительно?


sql oracle lag hierarchical-data
person Randy    schedule 24.01.2011    source источник
comment
Извините, но я не понимаю вопроса как рассчитать общее количество каждой детали, необходимой для сборки верхнего уровня (деталь 1)? и, в связи с этим, как вы получите ожидаемый результат.   -  person René Nyffenegger    schedule 24.01.2011
comment
@Rene - часть 1 - это исходный parent_part_id, который создает первые результаты иерархии. если мы посмотрим на эти результаты, то заметим, что нам нужно 2 из part_id 2, каждому из которых нужно 10 из part_id 3 - эта ветвь вносит 20 в общее количество для части 3, позже, аналогичным образом, необходимы еще 10 частей 3, всего 30   -  person Randy    schedule 24.01.2011

Ответы (2)


arrow_upward
5
arrow_downward

В Oracle 11 R2 это возможно с помощью 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 
  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:

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 Другой возможностью было бы суммировать логарифмы количества, а затем взять показатель степени суммы:

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
;
person René Nyffenegger    schedule 24.01.2011
comment
спасибо - у меня, к сожалению, есть проблема обратной совместимости с оракулом 10. Пытаясь понять, что происходит выше, кажется, что в определение Assembly_hier встроена рекурсия, поскольку она ссылается на себя в своем собственном определении, но я не совсем кроме того, как это работает - он взрывается в моем экземпляре 10g с неподдерживаемыми ошибками псевдонимов столбцов. - person Randy; 25.01.2011
comment
спасибо за помощь - я нашел рабочее решение, опубликованное здесь, но очень ценю ваши подробные ответы. - person Randy; 25.01.2011

arrow_upward
0
arrow_downward

я оказался здесь: это работает на оракуле 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
person Randy    schedule 25.01.2011