Ayuda a calcular la suma compleja en un conjunto de datos jerárquico
Tengo un interesante problema SQL. Tengo una tabla jerárquica de partes que hacen una lista de materiales. similar a ésto:
ASSEMBLY
---------
parent_part_id
part_id
quantity
Obtengo la jerarquía de esta estructura con una consulta como esta:
SELECT level, part_id, quantity
from assembly
start with parent_part_id = 1
connect by parent_part_id = prior part_id;
la salida podría verse así:
level part_id quantity
----- ------- ---------
1 2 2
2 3 10
1 4 2
2 5 1
3 3 5
hasta ahora tan bueno.
la pregunta es esta: ¿cómo calculo el número total de cada parte requerida para hacer el ensamblaje de nivel superior (parte 1)?
Agrupar este conjunto de resultados por parte y sumar la cantidad no es correcto, ya que la cantidad debe multiplicarse por la cantidad de la parte inmediatamente superior a la parte actual en la jerarquía, recursivamente hacia arriba del árbol.
Estoy pensando que esta es una función LAG, pero tengo problemas para visualizarla.
editar: resultados esperados:
part_id quantity
------- --------
2 2
3 30
4 2
5 2
más edición: obtengo resultados interesantes con 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
la columna matemática devuelve una representación de cadena del cálculo que quiero realizar :) por ejemplo, puede decir:
1*1*2*10
o algo similar y apropiado ... tal vez hacer una función para analizar esto y devolver el resultado resolverá el problema ... ¿alguien piensa que esto es indignante?