再发一个递归应用，坐等大神

Question

问题描述：
传入一个产品号码参数HMAT,求生产这个产品(HMAT)所需要的材料(CHLVHMAT)及数量比例。
举例：
BP127-01
   71(2426K)  90
   BP127-10   10
BP127-10
  127    10
  24-1   30
  71(2426K)  60
127
  24-1   30
  71(2426K)  30 
所以，生产一个BP127-01，需要材料及数量比例为：
24-1=10/(90+10)*(30/(10+30+60))+(10/(90+10))*(10/(10+30+60))*(30/(30+30))=0.035
71(2426K)=90/(90+10) + 10/(90+10)*(60/(10+30+60))+(10/(90+10))*(10/(10+30+60))*(30/(30+30))=0.965
资料简单模拟如下，当然这是最简单的两层递归..
WITH t1 AS (
SELECT 'BP127-01'HMAT FROM dual UNION ALL 
SELECT 'BP127-10' FROM dual UNION ALL 
SELECT '127' FROM dual
),t2 AS (
SELECT 'BP127-01'HMAT,'71(2426K)'CHLVHMAT,90 QTY FROM dual UNION ALL 
SELECT 'BP127-01'HMAT,'BP127-10'CHLVHMAT,10 QTY FROM dual UNION ALL 
SELECT 'BP127-10'HMAT,'127'CHLVHMAT,10 QTY FROM dual UNION ALL
SELECT 'BP127-10'HMAT,'24-1'CHLVHMAT,30 QTY FROM dual UNION ALL
SELECT 'BP127-10'HMAT,'71(2426K)'CHLVHMAT,60 QTY FROM dual UNION ALL
SELECT '127'HMAT,'24-1'CHLVHMAT,30 QTY FROM dual UNION ALL
SELECT '127'HMAT,'71(2426K)'CHLVHMAT,30 QTY FROM dual
)
SELECT LEVEL,chlvhmat,hmat,connect_by_isleaf flag,urqunty FROM (SELECT t1.hmat,t2.chlvhmat,t2.qty urqunty FROM t1,t2 WHERE t1.hmat=t2.hmat)
START WITH hmat='BP127-01' 
CONNECT BY PRIOR chlvhmat=hmat 
    ORDER siblings BY hmat;LEVEL, CHLVHMAT, HMAT, FLAG, URQUNTY
1 71(2426K) BP127-01 1 90
1 BP127-10  BP127-01 0 10
2 127       BP127-10 0 10
3 24-1      127      1 30
3 71(2426K) 127      1 30
2 24-1      BP127-10 1 30
2 71(2426K) BP127-10 1 60
递归应用产品

Answer 1

大神，看我给你留言没，我之前的方法用connect_by_root urqunty取父亲节点在上层树的数量，但是实际上取的是顶层父亲节点的数量，这是不对的。现在纠结这么取上层的数量。

Answer 2

WITH t1 AS
 (SELECT 'BP127-01' hmat
    FROM dual
  UNION ALL
  SELECT 'BP127-10'
    FROM dual
  UNION ALL
  SELECT '127' FROM dual),
t2 AS
 (SELECT 'BP127-01' hmat, '71(2426K)' chlvhmat, 90 qty
    FROM dual
  UNION ALL
  SELECT 'BP127-01' hmat, 'BP127-10' chlvhmat, 10 qty
    FROM dual
  UNION ALL
  SELECT 'BP127-10' hmat, '127' chlvhmat, 10 qty
    FROM dual
  UNION ALL
  SELECT 'BP127-10' hmat, '24-1' chlvhmat, 30 qty
    FROM dual
  UNION ALL
  SELECT 'BP127-10' hmat, '71(2426K)' chlvhmat, 60 qty
    FROM dual
  UNION ALL
  SELECT '127' hmat, '24-1' chlvhmat, 30 qty
    FROM dual
  UNION ALL
  SELECT '127' hmat, '71(2426K)' chlvhmat, 30 qty FROM dual)
SELECT chlvhmat, SUM(urqunty) urqunty FROM (
SELECT LEVEL lv, chlvhmat, hmat, connect_by_isleaf flag,
               urqunty / SUM(urqunty) over(PARTITION BY LEVEL ORDER BY LEVEL) / power(10, LEVEL - 1) urqunty
           FROM (SELECT t1.hmat, t2.chlvhmat, t2.qty urqunty FROM t1, t2 WHERE t1.hmat = t2.hmat)
          START WITH hmat = 'BP127-01'
         CONNECT BY PRIOR chlvhmat = hmat)
WHERE flag = 1
GROUP BY chlvhmat;

Answer 3

大神，你还是习惯用power哈，可能我这个例子数据刚好误导你，你看，假如树是这样：
BP127-01
   71(2426K)  80
   BP127-10   20
BP127-10
  127    10
  24-1   30
  71(2426K)  60
127
  24-1   30
  71(2426K)  30 
所以，生产一个BP127-01，需要材料及数量比例为：
24-1=20/(80+20)*(30/(10+30+60))+(20/(90+10))*(10/(10+30+60))*(30/(30+30))=0.07
71(2426K)=80/(80+20) + 20/(80+20)*(60/(10+30+60))+(20/(80+20))*(10/(10+30+60))*(30/(30+30))=0.93
但是你的结果出来确实不同的。

Answer 4

Connected to Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 
Connected as RCOTRPR1
 
SQL> 
SQL> CREATE OR REPLACE FUNCTION cal_rate(v_level_rate IN VARCHAR2) RETURN NUMBER AS
  2    v_rate NUMBER;
  3  BEGIN
  4    EXECUTE IMMEDIATE 'select ' || v_level_rate || ' from dual'
  5      INTO v_rate;
  6    RETURN v_rate;
  7  END cal_rate;
  8  /
 
Function created
 
SQL> 
SQL> WITH t1 AS (
  2  SELECT 'BP127-01'HMAT FROM dual UNION ALL
  3  SELECT 'BP127-10' FROM dual UNION ALL
  4  SELECT '127' FROM dual
  5  ),t2 AS (
  6  SELECT 'BP127-01'HMAT,'71(2426K)'CHLVHMAT,80 QTY FROM dual UNION ALL
  7  SELECT 'BP127-01'HMAT,'BP127-10'CHLVHMAT,20 QTY FROM dual UNION ALL
  8  SELECT 'BP127-10'HMAT,'127'CHLVHMAT,10 QTY FROM dual UNION ALL
  9  SELECT 'BP127-10'HMAT,'24-1'CHLVHMAT,30 QTY FROM dual UNION ALL
 10  SELECT 'BP127-10'HMAT,'71(2426K)'CHLVHMAT,60 QTY FROM dual UNION ALL
 11  SELECT '127'HMAT,'24-1'CHLVHMAT,30 QTY FROM dual UNION ALL
 12  SELECT '127'HMAT,'71(2426K)'CHLVHMAT,30 QTY FROM dual
 13  )
 14  SELECT  chlvhmat, sum(cal_rate(substr(sys_connect_by_path(rate, '*'), 2))) rate FROM (
 15  SELECT LEVEL lv, chlvhmat, hmat,
 16                 urqunty / SUM(urqunty) over(PARTITION BY LEVEL ORDER BY LEVEL) rate
 17             FROM (SELECT t1.hmat, t2.chlvhmat, t2.qty urqunty FROM t1, t2 WHERE t1.hmat = t2.hmat)
 18            START WITH hmat = 'BP127-01'
 19           CONNECT BY PRIOR chlvhmat = hmat) WHERE connect_by_isleaf = 1
 20    start with hmat='BP127-01'
 21  connect by prior chlvhmat = hmat GROUP BY chlvhmat;
 
CHLVHMAT        RATE
--------- ----------
71(2426K)       0.93
24-1            0.07
 
SQL> 比较取巧，因为sys_connect_by_path可以连接所有的父节点，用乘号连接变成varchar2类型，再用动态sql把乘法结果算出来，需要创建一个function.

Answer 5

很有意思的想法，特别是用function计算，很有亮点啊。
最后更正了一点，应该用SUM(urqunty) over(PARTITION BY hmat ORDER BY LEVEL)计算每棵树的总量才对。
问题已解决，感谢大神。