В упрощенных выражениях я пытаюсь рассчитать процент корня дерева, принадлежащего его родителям, далее по дереву. Как я могу сделать это только в 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;
Следующий запрос возвращает вычисление, которое я хотел бы сделать. Из-за характера 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;
/
Кажется, это смешной способ обойти это, и я предпочел бы избежать дополнительного времени, которое будет принято при анализе динамического SQL 1.
Теоретически, я думаю, я должен был бы использовать предложение 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: уникальная адресация в размерах модели
Использование UNIQUE SINGLE REFERENCE происходит по той же причине, что предложение ORDER BY не уникально.
TL;DR
Есть ли простой способ рассчитать процент корня дерева, принадлежащего его родителям, используя только SQL? Если я нахожусь на правильном пути с MODEL, где я ошибаюсь?
<суб > 1. Я также хотел бы избежать контекстного переключателя PL/SQL SQL. Я понимаю, что это крошечное количество времени, но это будет достаточно сложно сделать быстро, не добавляя еще несколько минут в день.
