sql - 计算其 parent 拥有的根的百分比

Question

简而言之，我试图计算其 parent 拥有的树根的百分比，在树的更远的地方。我怎么能单独在 SQL 中做到这一点？
这是我的(示例)架构。请注意，虽然层次结构本身非常简单，但还有一个额外的 holding_id ，这意味着单亲 parent 可以“拥有”他们 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) 
        );

以及一些示例数据:

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;

SQL Fiddle
以下查询返回我想要进行的计算。由于 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.*, 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: Non unique addressing in MODEL dimensions

使用 UNIQUE SINGLE REFERENCE由于类似的原因失败，即 ORDER BY 子句不是唯一的。
tl;博士
是否有一种简单的方法可以仅使用 SQL 计算其父级拥有的树的根的百分比？如果我在 MODEL 的正确轨道上，我哪里出错了？
1. 我还想避免 PL/SQL SQL 上下文切换。我意识到这是一小段时间，但是如果每天不增加几分钟，这将很难快速完成。

Answer 1

在 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;

SQL Fiddle .

或者，根据您的 '1'||把戏——

SELECT a.*, LEVEL AS lvl
      , 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 string literal expected .

随着树上层数的增加以及内部树创建的额外开销 XMLQuery，查询可能会变慢。本身。实现结果的最佳方法仍然是 PL/SQL 函数，顺便说一下，它可以在 10g 和 11g 中工作。