In simplified terms, I'm trying to calculate the percentage of the root of a tree owned by its parents, further up the tree. How can I do this in SQL alone?
Here's my (sample) schema. Please note that though the hierarchy itself is quite simple there is an additional holding_id
, which means that a single parent can "own" different parts of their child.
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)
);
And some sample data:
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;
The following query returns the calculation I would like to make. Due to the nature of SYS_CONNECT_BY_PATH it can't, to my knowledge, perform the calculation itself.
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
There are cyclical relationships in the data, just not in this example.
At the moment I'm going to use a pretty simple function to turn the string in the calc
column into a number
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;
/
This seems to be a ridiculous way of going about it and I would rather avoid the additional time that will be taken parsing the dynamic SQL1.
Theoretically, I think, I should be able to use the MODEL clause to calculate this. My problem is caused by the non-uniqueness of the tree. One of my attempts at using the MODEL clause to do this is:
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)
)
This, understandably, fails with the following:
ORA-32638: Non unique addressing in MODEL dimensions
Using UNIQUE SINGLE REFERENCE fails for a similar reason, namely that the ORDER BY clause is not unique.
tl;dr
Is there a simple way to calculate the percentage of the root of a tree owned by its parents using only SQL? If I'm on the right track with MODEL where am I going wrong?
1. I'd also like to avoid the PL/SQL SQL context-switch. I realise that this is a tiny amount of time but this is going to be difficult enough to do quickly without adding an additional few minutes a day.
EVAL()
function. – Jimmyt.id = a.parent_id
tot.parent_ID = a.id
. – Sibella