Eu tenho um problema interessante de SQL. Eu tenho uma tabela hierárquica de peças que produz uma lista de materiais. semelhante a este:

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

Eu recebo a hierarquia dessa estrutura com uma consulta como esta:

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

a saída pode ficar assim:

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

Por enquanto, tudo bem.

a pergunta é a seguinte: como faço para calcular o número total de cada peça necessária para fazer a montagem de nível superior (parte 1)?

O agrupamento desse resultado definido por peça e a soma da quantidade não está correto, pois a quantidade deve ser multiplicada pela quantidade da peça imediatamente acima da peça atual na hierarquia, subindo a árvore recursivamente.

Eu estou pensando que esta é uma função LAG, mas com problemas para visualizá-la.

edit: resultados esperados:

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

mais editar: eu recebo resultados interessantes com esta consulta

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

a coluna matemática retorna uma representação em string do cálculo que eu quero executar :) por exemplo, pode-se dizer:

1*1*2*10

ou algo semelhante e apropriado ... talvez fazer uma função para analisar isso e retornar o resultado resolva o problema .. alguém acha isso ultrajante?