计算根的父母拥有的百分比

简而言之，我试图计算树的父级所拥有的树根的百分比，即树的更上层。我怎样才能单独用 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;

SQL小提琴 http://www.sqlfiddle.com/#!4/c3d5d/2

以下查询返回我想要进行的计算。据我所知，由于 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;
/

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 SQL¹.

从理论上讲，我认为，我应该能够使用 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: 模型维度中的非唯一寻址

Using 独特的单一参考 http://docs.oracle.com/cd/E11882_01/server.112/e25554/sqlmodel.htm#DWHSG8797由于类似的原因而失败，即 ORDER BY 子句不唯一。

tl;dr

有没有一种简单的方法可以仅使用 SQL 来计算其父级所拥有的树的根的百分比？如果我的 MODEL 方向是正确的，那么我哪里出错了？

_{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.}

在 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小提琴 http://sqlfiddle.com/#!4/c3d5d/68.

或者，根据您的'1'|| trick-

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, but

select XMLQuery(substr('*1*0.25',2) RETURNING CONTENT).getnumberval() as val
   from dual;

gives ORA-19102: XQuery string literal expected.

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