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

yangtingkun

原帖由 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还是很巧妙的。

这位兄弟的水平很高

对于遍历顺序，是通过ORDER SIBLINGS BY进行保证的
SQL中的三个SUBSTR确实有问题，只是针对当前测试环境的，其实这里应该去第一个/和第二个/之间的部分。

lypch

小马哥，ORDER SIBLINGS 这个是什么意思啊？

原帖由 yangtingkun 于 2008-7-15 15:15 发表


这位兄弟的水平很高

对于遍历顺序，是通过ORDER SIBLINGS BY进行保证的
SQL中的三个SUBSTR确实有问题，只是针对当前测试环境的，其实这里应该去第一个/和第二个/之间的部分。

newkid

如果你同时要求A,B的原料数，那么起点就必须有两个，而且所有数字都要按它们来分组。修改如下：

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


CONNECT_BY_ROOT 在9i中可以从connect_by_path解析出来:
WITH vw_bom AS
    (SELECT DISTINCT PARENT,CHILD,P_QTY,C_QTY,
            regexp_substr(path,'[^,]+',1,1) AS ROOT   -- 规则表达式偷师小马哥，谢谢！不知为什么这个不能和sys_connect_by_path写在同一层, ORACLE会报错
      FROM (SELECT  b.*,sys_connect_by_path(parent,',') as path
             FROM BOM b
            CONNECT BY PRIOR CHILD = PARENT
           START WITH PARENT IN ('A','B')
           )               
    )
.......... 下面的一样

[ 本帖最后由 newkid 于 2008-7-15 23:17 编辑 ]

newkid

原帖由 yangtingkun 于 2008-7-15 15:15 发表


这位兄弟的水平很高

对于遍历顺序，是通过ORDER SIBLINGS BY进行保证的
SQL中的三个SUBSTR确实有问题，只是针对当前测试环境的，其实这里应该去第一个/和第二个/之间的部分。

杨兄过奖，这里的弟兄们都是我的老师。
关于ORDER SIBLINGS BY和ROWNUM, 我发现没有什么关系。用你的测试例子：

SELECT ID, rn
   FROM
   (
    SELECT ROWNUM RN, ID, LEVELS, PARENT, CHILD
    FROM T_LEVEL1
    START WITH LEVELS = 1
    CONNECT BY PRIOR CHILD = PARENT
    ORDER SIBLINGS BY LEVELS
   )

最后我拿掉 ORDER SIBLINGS BY LEVELS, 或者反序 ORDER SIBLINGS BY LEVELS DESC, 出来的顺序都是一样的。
但如果 ORDER SIBLINGS BY ID DESC, 可以看到在取同一层节点时，是从大的ID开始，仍然是深度优先（也就是说你的判断办法仍然成立）。

所以说， ORDER SIBLINGS BY LEVELS在这个例子中并不如你说的能保证深度优先，实际上是ORACLE自己的内部算法给了你保证。ORDER SIBLINGS 写不写，按什么排序都不会影响你的结果。

lypch

谢谢，你真的是太厉害了。有很多东西我都不知道。

jvkojvko

我也学习了，呵呵，谢谢newkid
我是新手啊，多多关照啊
呵呵，还有好多东西都不知道呢

yangtingkun

原帖由 newkid 于 2008-7-15 22:31 发表


杨兄过奖，这里的弟兄们都是我的老师。
关于ORDER SIBLINGS BY和ROWNUM, 我发现没有什么关系。用你的测试例子：

SELECT ID, rn
   FROM
   (
    SELECT ROWNUM RN, ID, LEVELS, PARENT, CHILD
    FROM T_LEVEL1
    START WITH LEVELS = 1
    CONNECT BY PRIOR CHILD = PARENT
    ORDER SIBLINGS BY LEVELS
   )

最后我拿掉 ORDER SIBLINGS BY LEVELS, 或者反序 ORDER SIBLINGS BY LEVELS DESC, 出来的顺序都是一样的。
但如果 ORDER SIBLINGS BY ID DESC, 可以看到在取同一层节点时，是从大的ID开始，仍然是深度优先（也就是说你的判断办法仍然成立）。

所以说， ORDER SIBLINGS BY LEVELS在这个例子中并不如你说的能保证深度优先，实际上是ORACLE自己的内部算法给了你保证。ORDER SIBLINGS 写不写，按什么排序都不会影响你的结果。

Oracle很可能修改START WITH的读取算法，比如对START WITH做了优化等等，这时不能保证START WITH的读取顺序仍然和当前一致，但是根据SIBLINGS 的定义和后向兼容性的考虑，Oracle不会修改ORDER SIBLINGS BY的排序规则。
这里添加SIBLINGS就是为了避免算法的改变而导致排序的改变，因为这个SQL依赖于这个排序的结果。

yangtingkun

另外，根据newkid指出的字段长度的问题修改了一下SQL：

[PHP]

SQL> SELECT SUBSTR(MAX(D), INSTR(MAX(D), '/', -1) + 1) C,
  2   SUBSTR(P, 2, INSTR(P, '/', 1, 2) - 2) P,
  3   POWER(10, SUM(LOG(10, Q))) Q
  4  FROM
  5  (
  6   SELECT SYS_CONNECT_BY_PATH(ID, '/') || '/' ID,
  7    SYS_CONNECT_BY_PATH(CHILD, '/') || '/' P,
  8    SYS_CONNECT_BY_PATH(PARENT, '/') D,
  9    CHILD_QTY/PARENT_QTY Q
10   FROM T_LEVEL1
11   START WITH ID IN
12   (
13    SELECT ID FROM
14    (
15     SELECT ID,
16      CASE WHEN LEAD(LEVELS) OVER(ORDER BY RN) > LEVELS THEN 0 ELSE 1 END LEAF
17     FROM
18     (
19      SELECT ROWNUM RN, ID, LEVELS, PARENT, CHILD
20      FROM T_LEVEL1
21      START WITH LEVELS = 1
22      CONNECT BY PRIOR CHILD = PARENT
23      ORDER SIBLINGS BY LEVELS
24     )
25    )
26    WHERE LEAF = 1
27   )
28   CONNECT BY PRIOR PARENT = CHILD
29  )
30  GROUP BY SUBSTR(ID, 2, INSTR(ID, '/', 1, 2) - 2), SUBSTR(P, 2, INSTR(P, '/', 1, 2) - 2) ;

C                              P                                       Q
------------------------------ ------------------------------ ----------
A                              Z                                    21.6
A                              Z                                       3
A                              G                                    32.4
A                              E                                    10.8
.
[/PHP]

当然这里还要假设字段中不包含'/'

jvkojvko

我这块砖抛得还是不错的

lypch

TO NewKID:

我现在有点糊涂了，
我看到你的Connect By和Start With中都追加了 ROOT = inner.ROOT 条件，为什么呢？
怎么来正确的理解Connect By和Start With中的ROOT = inner.ROOT ？

原帖由 newkid 于 2008-7-15 22:22 发表
如果你同时要求A,B的原料数，那么起点就必须有两个，而且所有数字都要按它们来分组。修改如下：

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


CONNECT_BY_ROOT 在9i中可以从connect_by_path解析出来:
WITH vw_bom AS
    (SELECT DISTINCT PARENT,CHILD,P_QTY,C_QTY,
            regexp_substr(path,'[^,]+',1,1) AS ROOT   -- 规则表达式偷师小马哥，谢谢！不知为什么这个不能和sys_connect_by_path写在同一层, ORACLE会报错
      FROM (SELECT  b.*,sys_connect_by_path(parent,',') as path
             FROM BOM b
            CONNECT BY PRIOR CHILD = PARENT
           START WITH PARENT IN ('A','B')
           )               
    )
.......... 下面的一样