对BOM展开并计算最终使用量的解答

zhangweicai74

原帖由 jvkojvko 于 2008-7-13 20:57 发表


不怕，我是男的

如此更可怕

jvkojvko

呵呵，等着呢

jvkojvko

高手呢？

zhangweicai74

原帖由 jvkojvko 于 2008-7-14 21:41 发表
高手呢？

不是完了么,还要什么高手,
我的问题是OK了

newkid

嗯，必须承认小马哥的SQL有点难懂，我斗胆来给你的最新成果挑点毛病：

Select p,d,Sum(qty) From (
Select p,c,d,power(10,Sum(Log(10,qty))) As qty From (
Select Distinct   P,c,SUBSTR(C,-1,1) D,regexp_substr(c,'[^,]+',1,Level), to_number(regexp_substr(Q,'[^*]+',1,Level)) As qty From (
Select CONNECT_BY_ROOT paret As P ,substr(SYS_CONNECT_BY_PATH(CHID,','),2) As C,1||SYS_CONNECT_BY_PATH(CQTY/PQTY,'*') As Q From tmp
Where CONNECT_BY_ISLEAF =1
Start With paret ='A'
Connect By  paret =  Prior CHID
) C
connect by  Level<=length(regexp_replace(Q,'[^*]',''))+1
Order By 1,2
)tt
Group  By p,c,d)ff
Group By p,d


这个SQL里面是把CQTY/PQTY逐层拼接然后又解析出来。碰巧例子中都是可以除尽的，如果碰上一个像 2/3 的你就有误差了。不如把CQTY和PQTY分开，最后算好总乘积再相除。
你在第三行有个SUBSTR(C,-1,1) D, 其实这个就是CHID, 你最好在最里层的SELECT多写一个CHID, 然后在有用到D的地方直接引用CHID。
你在第九行有个Level<=length(regexp_replace(Q,'[^*]',''))+1, 其实右边就是最里层的LEVEL, 你最好在最里层的SELECT多写一个LEVEL AS L, 这里改为Level<= L+1.
第十行来了个排序，完全无用。
最后，我不喜欢Level<=length(regexp_replace(Q,'[^*]',''))+1这样的写法，它的连接没有用到任何PRIOR条件，前后层完全无关，只要集合里行数稍多一点，右边的表达式稍微大一点，那么这个SELECT返回的行数就呈几何级数的上涨。虽然你后来用DISTINCT过滤掉了，但中间结果的开销可不小。

jvkojvko

原帖由 newkid 于 2008-7-14 23:45 发表
嗯，必须承认小马哥的SQL有点难懂，我斗胆来给你的最新成果挑点毛病：

Select p,d,Sum(qty) From (
Select p,c,d,power(10,Sum(Log(10,qty))) As qty From (
Select Distinct   P,c,SUBSTR(C,-1,1) D,regexp_substr(c,'[^,]+',1,Level), to_number(regexp_substr(Q,'[^*]+',1,Level)) As qty From (
Select CONNECT_BY_ROOT paret As P ,substr(SYS_CONNECT_BY_PATH(CHID,','),2) As C,1||SYS_CONNECT_BY_PATH(CQTY/PQTY,'*') As Q From tmp
Where CONNECT_BY_ISLEAF =1
Start With paret ='A'
Connect By  paret =  Prior CHID
) C
connect by  Level

恩，学习了，先看看

zhangweicai74

原帖由 newkid 于 2008-7-14 23:45 发表
嗯，必须承认小马哥的SQL有点难懂，我斗胆来给你的最新成果挑点毛病：

Select p,d,Sum(qty) From (
Select p,c,d,power(10,Sum(Log(10,qty))) As qty From (
Select Distinct   P,c,SUBSTR(C,-1,1) D,regexp_substr(c,'[^,]+',1,Level), to_number(regexp_substr(Q,'[^*]+',1,Level)) As qty From (
Select CONNECT_BY_ROOT paret As P ,substr(SYS_CONNECT_BY_PATH(CHID,','),2) As C,1||SYS_CONNECT_BY_PATH(CQTY/PQTY,'*') As Q From tmp
Where CONNECT_BY_ISLEAF =1
Start With paret ='A'
Connect By  paret =  Prior CHID
) C
connect by  Level

有道理.
两位有看过杨大侠的语句没?

newkid

学习一下杨大侠的SQL: 最后的9i版本

SELECT SUBSTR(MAX(D), LENGTH(MAX(D))) C,
SUBSTR(P, 2, 1),
POWER(10, SUM(LOG(10, Q))) Q
FROM
(
SELECT SYS_CONNECT_BY_PATH(ID, '/') ID,
  SYS_CONNECT_BY_PATH(CHILD, '/') P,
  SYS_CONNECT_BY_PATH(PARENT, '/') D,
  CHILD_QTY/PARENT_QTY Q
FROM T_LEVEL1
START WITH ID IN
(
  SELECT ID FROM
  (
   SELECT ID,
    CASE WHEN LEAD(LEVELS) OVER(ORDER BY RN) > LEVELS THEN 0 ELSE 1 END LEAF
   FROM
   (
    SELECT ROWNUM RN, ID, LEVELS, PARENT, CHILD
    FROM T_LEVEL1
    START WITH LEVELS = 1
    CONNECT BY PRIOR CHILD = PARENT
    ORDER SIBLINGS BY LEVELS
   )
  )
  WHERE LEAF = 1
)
CONNECT BY PRIOR PARENT = CHILD
)
GROUP BY SUBSTR(ID, 2, 1), SUBSTR(P, 2, 1) ;

他这里用了connect by后的ROWNUM, 利用下一个纪录层数是否增加来判断是否叶子。
这必须有一个前提，即CONNECT BY一定是深度优先遍历的，我不知道有没有文档支持。哪天ORACLE改用广度优先，这个逻辑就不成立了。
最后，GROUP BY SUBSTR(ID, 2, 1), SUBSTR(P, 2, 1) 有点小瑕疵，他假定ID和节点符号都只有一位字符。
如果按原料汇总，应该GROUP BY SUBSTR(P, 2, 1)才对，否则当一种原料有多个父亲的话就会有多行出现。

他的反向遍历树最后按叶子GROUP BY还是很巧妙的。

zhangweicai74

原帖由 newkid 于 2008-7-15 10:19 发表
学习一下杨大侠的SQL: 最后的9i版本

SELECT SUBSTR(MAX(D), LENGTH(MAX(D))) C,
SUBSTR(P, 2, 1),
POWER(10, SUM(LOG(10, Q))) Q
FROM
(
SELECT SYS_CONNECT_BY_PATH(ID, '/') ID,
  SYS_CONNECT_BY_PATH(CHILD, '/') P,
  SYS_CONNECT_BY_PATH(PARENT, '/') D,
  CHILD_QTY/PARENT_QTY Q
FROM T_LEVEL1
START WITH ID IN
(
  SELECT ID FROM
  (
   SELECT ID,
    CASE WHEN LEAD(LEVELS) OVER(ORDER BY RN) > LEVELS THEN 0 ELSE 1 END LEAF
   FROM
   (
    SELECT ROWNUM RN, ID, LEVELS, PARENT, CHILD
    FROM T_LEVEL1
    START WITH LEVELS = 1
    CONNECT BY PRIOR CHILD = PARENT
    ORDER SIBLINGS BY LEVELS
   )
  )
  WHERE LEAF = 1
)
CONNECT BY PRIOR PARENT = CHILD
)
GROUP BY SUBSTR(ID, 2, 1), SUBSTR(P, 2, 1) ;

他这里用了connect by后的ROWNUM, 利用下一个纪录层数是否增加来判断是否叶子。
这必须有一个前提，即CONNECT BY一定是深度优先遍历的，我不知道有没有文档支持。哪天ORACLE改用广度优先，这个逻辑就不成立了。
最后，GROUP BY SUBSTR(ID, 2, 1), SUBSTR(P, 2, 1) 有点小瑕疵，他假定ID和节点符号都只有一位字符。
如果按原料汇总，应该GROUP BY SUBSTR(P, 2, 1)才对，否则当一种原料有多个父亲的话就会有多行出现。

他的反向遍历树最后按叶子GROUP BY还是很巧妙的。

<最后，GROUP BY SUBSTR(ID, 2, 1), SUBSTR(P, 2, 1) 有点小瑕疵，他假定ID和节点符号都只有一位字符。
如果按原料汇总，应该GROUP BY SUBSTR(P, 2, 1)才对，否则当一种原料有多个父亲的话就会有多行出现。>
这点还没有测试过,有时间再测试下.

lypch

我测试了一下，发现了问题。如果遇到公用品的话，就会出现问题：

例如下面的案例：

insert into bom values('D','E',1,4)
insert into bom values('A','D',1,4)
insert into bom values('B','D',1,4)

在计算一个A用多少个D的时候，计算结果就不正确

我修改了一下，红色部分是我追加的：
SELECT CHILD ,(SELECT POWER(10,SUM(LOG(10,C_QTY)))/POWER(10,SUM(LOG(10,P_QTY)))
                  FROM BOM where (parent,child) in (
                    select parent,child from bom start with parent = 'A' connect by prior child = parent
                  )               
                 CONNECT BY PRIOR PARENT=CHILD
                 START WITH CHILD = inner.CHILD
                 --parent = inner.parent  --如果同一个母品目下挂相同的字品目时会出现问题 比较极端的情况，但是也不能排除用户这样干。
                 and rowidtochar(rowid) = inner.frowid --这个地方可以解决在同一个母品目下挂下同的字品目的问题。   
                ) AS C_QTY
        FROM (SELECT b.*,rowidtochar(rowid) frowid
                FROM BOM b
               WHERE NOT EXISTS (SELECT 1 FROM BOM WHERE parent = b.child)
               START WITH PARENT in ('A')
                CONNECT BY PRIOR CHILD = PARENT
             ) INNER
            
接上面的例子，如果用一个语句同时计算A和B各需要多少个E的话，我还是没有想到方法。NewKid，有什么办法吗？

原帖由 newkid 于 2008-7-14 10:41 发表
稍微改了一下，你帮我验证吧：

SELECT CHILD
      ,SUM(C_QTY) AS C_QTY       -- 解决叶子（原料）不唯一
  FROM (SELECT CHILD
              ,(SELECT POWER(10,SUM(LOG(10,C_QTY)))/POWER(10,SUM(LOG(10,P_QTY)))
                  FROM BOM
               CONNECT BY PRIOR PARENT=CHILD
                 START WITH CHILD = inner.CHILD     -- 从每个叶子开始
                            AND PARENT = inner.PARENT    -- 解决叶子（原料）不唯一
                ) AS C_QTY
        FROM (SELECT b.*
                FROM BOM b
               WHERE NOT EXISTS (SELECT 1 FROM BOM WHERE parent = b.child) --解决 9i 没有CONNECT_BY_ISLEAF
                CONNECT BY PRIOR CHILD = PARENT
               START WITH PARENT='A'
             ) inner
      )
GROUP BY CHILD;

[ 本帖最后由 lypch 于 2008-7-15 12:22 编辑 ]