求BOM树型展开的总用量

newkid

上面的方法都不新鲜了，这个10G的MODEL方法好像没见过：

WITH t AS (
SELECT SYS_CONNECT_BY_PATH(a.part_no,'\')||'\'||a.component_part as path
      ,qty_per_assembly
      ,LEVEL lvl
  FROM manuf_structure_tab a JOIN inventory_part_tab b
       ON a.part_no = b.part_no
START WITH b.sk_part_category_db = 'Product'
CONNECT BY a.part_no = PRIOR a.component_part
)
SELECT root_part,part,SUM(qty) AS qty
  FROM (
SELECT SUBSTR(path,2,INSTR(path,'\',1,2)-INSTR(path,'\',1,1)-1) as ROOT_PART
      ,SUBSTR(path,INSTR(path,'\',-1,1)+1) as PART
      ,qty
  FROM t
MODEL IGNORE NAV RETURN UPDATED ROWS
    DIMENSION BY (path)
    MEASURES (qty_per_assembly, 1 qty,lvl)
    RULES AUTOMATIC ORDER (
      qty[any] order by path=(CASE WHEN lvl[cv()]=1 THEN qty_per_assembly[cv()]
                                   ELSE qty_per_assembly[cv()] *
                                        qty[SUBSTR(cv(),1,INSTR(cv(),'\',-1,1)-1)]
                              END)
    )
)
GROUP BY root_part,part;

jiqing1004

记录学习下

newkid

原帖由 nyfor 于 2009-11-5 10:31 发表

就等你了
我想用sys_connect_by_path 把QTY_PER_ASSEMBLY用乘號串起來, 然後調用一個能執行動態SQL的function以得到結果.

怪不得昨天没人回帖，都是你害的。反正我是不怕出洋相。
这才想起11G的DBMS_XMLGEN.getxmltype可以对表达式求值，以后等我有了环境得玩一下。
你最近跳槽到台资企业了？怎么老用繁体字？

zhangweicai74

原帖由 newkid 于 2009-11-6 01:00 发表
我简化一下你的数据：

create table MANUF_STRUCTURE_TAB
(PART_NO                   VARCHAR2(25) not null,
COMPONENT_PART            VARCHAR2(25) not null,
QTY_PER_ASSEMBLY          NUMBER not null
);



create table INVENTORY_PART_TAB
(PART_NO                       VARCHAR2(25) not null,   
  SK_PART_CATEGORY_DB                      VARCHAR2(15) not null
  );


INSERT INTO MANUF_STRUCTURE_TAB VALUES ('A1','B',2);
INSERT INTO MANUF_STRUCTURE_TAB VALUES ('A2','B',3);
INSERT INTO MANUF_STRUCTURE_TAB VALUES ('B','C',4);
INSERT INTO MANUF_STRUCTURE_TAB VALUES ('C','D',7);
INSERT INTO MANUF_STRUCTURE_TAB VALUES ('A1','C',6);


INSERT INTO INVENTORY_PART_TAB VALUES ('A1','Product');
INSERT INTO INVENTORY_PART_TAB VALUES ('A2','Product');
INSERT INTO INVENTORY_PART_TAB VALUES ('B','SemiProdudct');
INSERT INTO INVENTORY_PART_TAB VALUES ('C','Component');
INSERT INTO INVENTORY_PART_TAB VALUES ('D','Component');

三种方法（既然你不喜欢自定义函数就没写了）：

WITH t AS (
SELECT SYS_CONNECT_BY_PATH(a.part_no,'\')||'\'||a.component_part||'\' as path
      ,qty_per_assembly
  FROM manuf_structure_tab a JOIN inventory_part_tab b
       ON a.part_no = b.part_no
START WITH b.sk_part_category_db = 'Product'
CONNECT BY a.part_no = PRIOR a.component_part
)
SELECT root_part,part,SUM(qty) AS qty
  FROM (
SELECT SUBSTR(t1.path,2,INSTR(t1.path,'\',1,2)-INSTR(t1.path,'\',1,1)-1) as ROOT_PART
      ,SUBSTR(t1.path,INSTR(t1.path,'\',-1,2)+1,INSTR(t1.path,'\',-1,1)-INSTR(t1.path,'\',-1,2)-1) as PART
      ,EXP(SUM(LN(t2.qty_per_assembly))) AS qty
  FROM t t1,t t2
WHERE t1.path LIKE t2.path||'%'
GROUP BY t1.path
)
GROUP BY root_part,part;



WITH t AS (
SELECT SYS_CONNECT_BY_PATH(a.part_no,'\')||'\'||a.component_part||'\' as path
      ,qty_per_assembly
  FROM manuf_structure_tab a JOIN inventory_part_tab b
       ON a.part_no = b.part_no
START WITH b.sk_part_category_db = 'Product'
CONNECT BY a.part_no = PRIOR a.component_part
)
SELECT root_part,part,SUM(qty) AS qty
  FROM (
SELECT SUBSTR(t1.path,2,INSTR(t1.path,'\',1,2)-INSTR(t1.path,'\',1,1)-1) as ROOT_PART
      ,SUBSTR(t1.path,INSTR(t1.path,'\',-1,2)+1,INSTR(t1.path,'\',-1,1)-INSTR(t1.path,'\',-1,2)-1) as PART
      ,(SELECT EXP(SUM(LN(t2.qty_per_assembly))) FROM t t2 WHERE t1.path LIKE t2.path||'%') AS qty
  FROM t t1
GROUP BY t1.path
)
GROUP BY root_part,part;



WITH t AS (
SELECT SYS_CONNECT_BY_PATH(a.part_no,'\')||'\'||a.component_part||'\' as path
      ,SYS_CONNECT_BY_PATH(a.qty_per_assembly,'\')||'\' as qty_path
      ,qty_per_assembly
  FROM manuf_structure_tab a JOIN inventory_part_tab b
       ON a.part_no = b.part_no
START WITH b.sk_part_category_db = 'Product'
CONNECT BY a.part_no = PRIOR a.component_part
)
SELECT root_part,part,SUM(qty) AS qty
  FROM (
SELECT SUBSTR(path,2,INSTR(path,'\',1,2)-INSTR(path,'\',1,1)-1) as ROOT_PART
      ,SUBSTR(path,INSTR(path,'\',-1,2)+1,INSTR(path,'\',-1,1)-INSTR(path,'\',-1,2)-1) as PART
      ,(SELECT EXP(SUM(LN(SUBSTR(qty_path,INSTR(qty_path,'\',1,rn)+1,INSTR(qty_path,'\',1,rn+1)-INSTR(qty_path,'\',1,rn)-1))))
          FROM (SELECT ROWNUM RN FROM DUAL CONNECT BY ROWNUM

多谢NWEKID!
真的大家都在等你出手啊,昨天白天没有一个人下刀.

现在测试中,貌似性能不怎么样.
测试数据没问题后再考虑优化.

newkid

你去除重复的父子键了吗？我用你的数据去除重复后，差不多2秒。

zhangweicai74

我的manuf_structure_tab有15万记录,INVENORY_PART_TAB有8W.
目前执行了5MIN还没反应.
WITH t AS (
SELECT SYS_CONNECT_BY_PATH(a.part_no,'\')||'\'||a.component_part||'\' as path
      ,qty_per_assembly
  FROM manuf_structure_tab a,inventory_part_tab  b
  where a.part_no = b.part_no
  and a.contract=b.contract
  and b.contract='SKY'
START WITH b.sk_part_category_db = 'Product'
CONNECT BY a.part_no = PRIOR a.component_part
)
SELECT root_part,part,SUM(qty) AS qty
  FROM (
SELECT SUBSTR(t1.path,2,INSTR(t1.path,'\',1,2)-INSTR(t1.path,'\',1,1)-1) as ROOT_PART
      ,SUBSTR(t1.path,INSTR(t1.path,'\',-1,2)+1,INSTR(t1.path,'\',-1,1)-INSTR(t1.path,'\',-1,2)-1) as PART
      ,(SELECT EXP(SUM(LN(t2.qty_per_assembly))) FROM t t2 WHERE t1.path LIKE t2.path||'%') AS qty
  FROM t t1
GROUP BY t1.path
)
GROUP BY root_part,part

nyfor

原帖由 newkid 于 2009-11-6 09:18 发表

怪不得昨天没人回帖，都是你害的。反正我是不怕出洋相。
这才想起11G的DBMS_XMLGEN.getxmltype可以对表达式求值，以后等我有了环境得玩一下。
你最近跳槽到台资企业了？怎么老用繁体字？

唉, 俺英文不好,只能在台资企业混混.
10G也有 DBMS_XMLGEN.getxmltype

zhangweicai74

原帖由 newkid 于 2009-11-6 09:58 发表
你去除重复的父子键了吗？我用你的数据去除重复后，差不多2秒。

我的表里没有重复的父子键,给的数据是递归导出的部分PRODUCT的BOM表,可能存在重复.

......现在还未有结果,看来要停掉它了.

newkid

这样呢？
WITH t AS (
SELECT SYS_CONNECT_BY_PATH(a.part_no,'\')||'\' as path
      ,component_part as part
      ,LEVEL lvl
      ,SYS_CONNECT_BY_PATH(a.qty_per_assembly,'\')||'\' as qty_path
  FROM manuf_structure_tab a JOIN inventory_part_tab b
       ON a.part_no = b.part_no
START WITH b.sk_part_category_db = 'Product'
CONNECT BY a.part_no = PRIOR a.component_part
)
SELECT root_part,part,SUM(qty) AS qty
  FROM (
SELECT SUBSTR(path,2,INSTR(path,'\',1,2)-2) as ROOT_PART
      ,PART
      ,(SELECT EXP(SUM(LN(SUBSTR(qty_path,INSTR(qty_path,'\',1,rn)+1,INSTR(qty_path,'\',1,rn+1)-INSTR(qty_path,'\',1,rn)-1))))
          FROM (SELECT ROWNUM RN FROM DUAL CONNECT BY ROWNUM<=100)
         WHERE rn<=lvl
       ) AS qty
  FROM t
)
GROUP BY root_part,part;

---------------------------------------------------------------------------------------------------------------
| Id  | Operation                       | Name                | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                |                     |   804 |  3163K|       |  1216   (1)| 00:00:15 |
|   1 |  SORT AGGREGATE                 |                     |     1 |    13 |       |            |       |
|*  2 |   VIEW                          |                     |     1 |    13 |       |     2   (0)| 00:00:01 |
|   3 |    COUNT                        |                     |       |       |       |            |       |
|   4 |     CONNECT BY WITHOUT FILTERING|                     |       |       |       |            |       |
|   5 |      FAST DUAL                  |                     |     1 |       |       |     2   (0)| 00:00:01 |
|   6 |  HASH GROUP BY                  |                     |   804 |  3163K|    13M|  1216   (1)| 00:00:15 |
|   7 |   VIEW                          |                     |  2587 |     9M|       |     9  (12)| 00:00:01 |
|*  8 |    CONNECT BY WITH FILTERING    |                     |       |       |       |            |       |
|*  9 |     FILTER                      |                     |       |       |       |            |       |
|  10 |      COUNT                      |                     |       |       |       |            |       |
|* 11 |       HASH JOIN                 |                     |  2587 |   126K|       |     9  (12)| 00:00:01 |
|  12 |        TABLE ACCESS FULL        | INVENTORY_PART_TAB  |   887 | 20401 |       |     3   (0)| 00:00:01 |
|  13 |        TABLE ACCESS FULL        | MANUF_STRUCTURE_TAB |  2587 | 69849 |       |     5   (0)| 00:00:01 |
|* 14 |     HASH JOIN                   |                     |       |       |       |            |       |
|  15 |      CONNECT BY PUMP            |                     |       |       |       |            |       |
|  16 |      COUNT                      |                     |       |       |       |            |       |
|* 17 |       HASH JOIN                 |                     |  2587 |   126K|       |     9  (12)| 00:00:01 |
|  18 |        TABLE ACCESS FULL        | INVENTORY_PART_TAB  |   887 | 20401 |       |     3   (0)| 00:00:01 |
|  19 |        TABLE ACCESS FULL        | MANUF_STRUCTURE_TAB |  2587 | 69849 |       |     5   (0)| 00:00:01 |
|  20 |     COUNT                       |                     |       |       |       |            |       |
|* 21 |      HASH JOIN                  |                     |  2587 |   126K|       |     9  (12)| 00:00:01 |
|  22 |       TABLE ACCESS FULL         | INVENTORY_PART_TAB  |   887 | 20401 |       |     3   (0)| 00:00:01 |
|  23 |       TABLE ACCESS FULL         | MANUF_STRUCTURE_TAB |  2587 | 69849 |       |     5   (0)| 00:00:01 |
---------------------------------------------------------------------------------------------------------------

从你的数据量来看，遍历深度应该很浅啊？如果单独挑几种产品(在START WITH中指定)，速度怎么样？结果对吗？
单独把WITH里面的查询CREATE TABLE AS...要花多少时间？生成的结果多大？这一步完成后尽量过滤（比如只保留你要的原料），让后面的乘法计算更少。


[ 本帖最后由 newkid 于 2009-11-6 23:44 编辑 ]

newkid

原帖由 nyfor 于 2009-11-6 10:17 发表

唉, 俺英文不好,只能在台资企业混混.
10G也有 DBMS_XMLGEN.getxmltype

我记错了，应该是这个：

SELECT xmlquery('1*2*3*4' returning content).getnumberval() FROM DUAL;

但是10G不支持变量，只能常量：
SELECT xmlquery(str returning content).getnumberval()
FROM (SELECT '1*2*3*4' as str FROM DUAL);

ERROR at line 1:
ORA-19102: XQuery string literal expected