Проще говоря, яЯ пытаюсь вычислить процент корня дерева, принадлежащего его родителям, далее вверх по дереву. Как я могу сделать это в одном только SQL?

Вот'Моя (примерная) схема. Обратите внимание, что хотя сама иерархия довольно проста, естьholding_id, что означает, что один родитель можетсвоя" разные части своего ребенка.

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

И некоторые примеры данных:

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;

SQL Fiddle

Следующий запрос возвращает расчет, который я хотел бы сделать. Из-за характера SYS_CONNECT_BY_PATH он может 'т, насколько мне известно, выполнить сам расчет.

 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

В данных есть циклические отношения, но не в этом примере.

На данный момент яЯ собираюсь использовать довольно простую функцию, чтобы превратить строку вcalc столбец в число

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

Это кажется нелепым способом решения этой проблемы, и я бы предпочел избежать дополнительного времени, которое потребуется для анализа динамического SQL1.

Теоретически, я думаю, я должен быть в состоянии использовать предложение MODEL для вычисления этого. Моя проблема вызвана неединственностью дерева. Одна из моих попыток использовать предложение MODEL для этого:

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

Это, по понятным причинам, терпит неудачу со следующим:

ORA-32638: неуникальная адресация в размерах модели

С помощьюУНИКАЛЬНАЯ ЕДИНАЯ ССЫЛКА терпит неудачу по той же причине, а именно, что предложение ORDER BY не является уникальным.

ТЛ; др

Есть ли простой способ вычислить процент корня дерева, принадлежащего его родителям, используя только SQL? Если я'м на правильном пути с МОДЕЛЬ, где я иду не так?

1. ЯТакже хотелось бы избежать переключения контекста PL / SQL SQL. Я понимаю, что это крошечный промежуток времени, но это будет достаточно сложно сделать быстро, не добавляя несколько минут в день.