Oblicz procent korzenia należącego do rodziców
W uproszczeniu próbuję obliczyć procent korzenia drzewa należącego do jego rodziców, dalej w górę drzewa. Jak mogę to zrobić tylko w SQL?
Oto mój schemat (przykładowy). Należy pamiętać, że chociaż sama hierarchia jest dość prosta, istnieje dodatkowaholding_id
, co oznacza, że jeden rodzic może „posiadać” różne części swojego dziecka.
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)
);
I kilka przykładowych danych:
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;
Następujące zapytanie zwraca obliczenie, które chciałbym wykonać. Ze względu na naturę SYS_CONNECT_BY_PATH nie może, według mojej wiedzy, przeprowadzić samego obliczenia.
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
W danych występują cykliczne zależności, nie w tym przykładzie.
W tej chwili zamierzam użyć całkiem prostej funkcji, aby włączyć ciąg wcalc
kolumna w numer
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;
/
Wydaje się, że jest to absurdalny sposób radzenia sobie z tym i wolałbym uniknąć dodatkowego czasu, który zostanie poświęcony na analizowanie dynamicznego SQL1.
Myślę, że teoretycznie powinienem móc użyć klauzuli MODEL do obliczenia tego. Mój problem jest spowodowany brakiem wyjątkowości drzewa. Jedną z moich prób wykorzystania klauzuli MODEL do tego jest:
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)
)
To, co zrozumiałe, kończy się niepowodzeniem:
ORA-32638: Niepowtarzalne adresowanie w wymiarach MODELU
Za pomocąWYJĄTKOWA JEDNA REFERENCJA nie działa z podobnego powodu, mianowicie, że klauzula ORDER BY nie jest unikalna.
tl; drCzy istnieje prosty sposób obliczenia procentu katalogu głównego drzewa należącego do jego rodziców za pomocą tylko SQL? Jeśli jestem na dobrej drodze z MODELEM, gdzie się mylę?
1. Chciałbym również uniknąć przełączenia kontekstu PL / SQL SQL. Zdaję sobie sprawę, że jest to niewielka ilość czasu, ale będzie to wystarczająco trudne, aby zrobić to szybko, bez dodawania dodatkowych kilku minut dziennie.