简单来说,我试图计算其父母所拥有的树的根百分比,进一步增加树.如何在sql中单独执行此操作？
这是我的(示例)模式.请注意,虽然层次结构本身非常简单,但是还有一个additional_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,0.25) into hierarchy_test values (2,4,5,0.1) into hierarchy_test values (2,0.4) into hierarchy_test values (4,1) into hierarchy_test values (5,6,0.3) into hierarchy_test values (5,7,0.2) into hierarchy_test values (5,8,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;
/
这似乎是一个可笑的方法,我宁愿避免额外的时间将解析动态sql1.
理论上,我想,我应该可以使用MODEL子句来计算.我的问题是由树的非唯一性造成的.我尝试使用MODEL子句来做到这一点：
select *
from ( select a.*,' * ') 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: Non unique addressing in MODEL dimensions
由于类似的原因,使用UNIQUE SINGLE REFERENCE失败,即ORDER BY子句不是唯一的.
TL;博士
解决方法
在11g,可能是像 –
SELECT a.*,LEVEL AS lvl,XMLQuery( substr( sys_connect_by_path( percent_owned,'*' ),2 ) RETURNING CONTENT).getnumberval() AS calc FROM hierarchy_test a START WITH id = 1 CONNECT BY nocycle PRIOR parent_id = id;
或者,根据你的’1’||招-
SELECT a.*,XMLQuery( ('1'|| sys_connect_by_path( percent_owned,'*' )) RETURNING CONTENT).getnumberval() AS calc
FROM hierarchy_test a
START WITH id = 1
CONNECT BY nocycle PRIOR parent_id = id;
不幸的是在10g中,XMLQuery不能接受函数,并且总是期望一个字符串字面值用于评估,例如 –
select XMLQuery('1*0.25' RETURNING CONTENT).getnumberval() as val
from dual;
工作和回报0.25,但
select XMLQuery(substr('*1*0.25',2) RETURNING CONTENT).getnumberval() as val
from dual;
给出了ORA-19102：预期的XQuery字符串字面值.
随着树的级数增加,XMLQuery本身的内部树创建的额外开销也会增加查询速度.实现结果的最佳方法仍然是PL / sql函数,它在10g和11g中都可以使用.
