简单来说,我想计算树根由其上层父节点所拥有的百分比。如何仅使用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:在MODEL维度中存在非唯一性寻址
使用UNIQUE SINGLE REFERENCE也会因相似原因失败,即ORDER BY子句不唯一。
tl;dr
是否有一种简单的方法只使用SQL计算树根由其父级拥有的百分比?如果我通过MODEL走在正确的轨道上,那么我错在哪里了?
1.我还想避免PL/SQL SQL上下文切换。我意识到这只是一个很小的时间量,但是要在几分钟内快速完成这项任务已经很困难了,再增加几分钟会更加困难。
t.id = a.parent_id
改为t.parent_ID = a.id
即可。 - Jon Heller