Em termos simplificados, estou tentando calcular a porcentagem da raiz de uma árvore pertencente a seus pais, mais acima na árvore. Como posso fazer isso apenas no SQL?

Aqui está o meu esquema (amostra). Por favor, note que, embora a hierarquia em si seja bastante simples, há um adicionalholding_id, o que significa que um pai / mãe solteiro pode "possuir" diferentes partes do filho.

create table hierarchy_test ( 
       id number -- "root" ID
     , parent_id number -- Parent of ID
     , holding_id number -- The ID can be split into multiple parts
     , percent_owned number (3, 2)
     , primary key (id, parent_id, holding_id) 
        );

E alguns dados de amostra:

insert all 
 into hierarchy_test values (1, 2, 1, 1) 
 into hierarchy_test values (2, 3, 1, 0.25)
 into hierarchy_test values (2, 4, 1, 0.25)
 into hierarchy_test values (2, 5, 1, 0.1)
 into hierarchy_test values (2, 4, 2, 0.4)
 into hierarchy_test values (4, 5, 1, 1)
 into hierarchy_test values (5, 6, 1, 0.3)
 into hierarchy_test values (5, 7, 1, 0.2)
 into hierarchy_test values (5, 8, 1, 0.5)
select * from dual;

Violino SQL

A consulta a seguir retorna o cálculo que gostaria de fazer. Devido à natureza de SYS_CONNECT_BY_PATH, não posso, no meu conhecimento, executar o cálculo em si.

 select a.*, level as lvl
      , '1' || sys_connect_by_path(percent_owned, ' * ') as calc
   from hierarchy_test a
  start with id = 1
connect by nocycle prior parent_id = id

Existem relações cíclicas nos dados, mas não neste exemplo.

No momento eu vou usar uma função bem simples para transformar a string nocalc coluna em um número

create or replace function some_sum ( P_Sum in varchar2 ) return number is
   l_result number;
begin  
   execute immediate 'select ' || P_Sum || ' from dual'
     into l_result;

   return l_result;   
end;
/

Esta parece ser uma maneira ridícula de fazer isso e eu preferiria evitar o tempo adicional que será usado para analisar o SQL dinâmico1.

Teoricamente, acho que deveria poder usar a cláusula MODEL para calcular isso. Meu problema é causado pela não-exclusividade da árvore. Uma das minhas tentativas de usar a cláusula MODEL para fazer isso é:

select *
  from ( select a.*, level as lvl
              , '1' || sys_connect_by_path(percent_owned, ' * ') as calc
           from hierarchy_test a
          start with id = 1
        connect by nocycle prior parent_id = id
                 )
 model
 dimension by (lvl ll, id ii)
 measures (percent_owned, parent_id )
 rules upsert all ( 
   percent_owned[any, any]
   order by ll, ii  = percent_owned[cv(ll), cv(ii)] * nvl( percent_owned[cv(ll) - 1, parent_id[cv(ll), cv(ii)]], 1)
               )

Isso, compreensivelmente, falha com o seguinte:

ORA-32638: Endereçamento não exclusivo em dimensões MODEL

UsandoÚNICA REFERÊNCIA ÚNICA falha por um motivo semelhante, ou seja, que a cláusula ORDER BY não é exclusiva.

tl; dr

Existe uma maneira simples de calcular a porcentagem da raiz de uma árvore pertencente a seus pais usando apenas SQL? Se estou no caminho certo com MODEL, onde estou indo errado?

1. Também gostaria de evitar o contexto do SQL do PL / SQL. Eu percebo que esta é uma pequena quantidade de tempo, mas isso vai ser difícil o suficiente para fazer rapidamente, sem acrescentar mais alguns minutos por dia.