Oracle-12c
遞歸乘法
如何讓 ASSY_EXT_QTY 成為所有路徑的數量?IE:
LVL QTY ASSY_EXT_QTY 1 1 1 2 3 3 1*3 3 2 6 1*3*2 ...
我想為數量開闢一條道路,然後以某種方式將它們相乘,但這似乎遙不可及。這是程式碼:
select LEVEL, SYS_CONNECT_BY_PATH(CHILD_ITEM_NUMBER, '>') ROUTE, CASE LEVEL WHEN 1 THEN COMPONENT_QTY ELSE (PRIOR COMPONENT_QTY) * COMPONENT_QTY <--- RIGHT NOW I'M DOING THIS BUT NOT ENOUGH AS WORKS IN THE CURRENT SUB-LEVEL ONLY. END ASSY_EXT_QTY from ( select ... FROM ... WHERE ... ) START WITH FATHER_ITEM_NUMBER = :ASSY CONNECT BY PRIOR CHILD_ITEM_ID = STR_ITEM_ID ) bom
我只是 SQL,沒有函式或類似的東西,因為環境不允許。
謝謝!
ODCI
就個人而言,我可能會編寫自己的聚合函式,以便 SQL 看起來像
select SYS_CONNECT_BY_PATH(CHILD_ITEM_NUMBER, '>') ROUTE, my_mult_agg( ) over (order by level) ASSY_EXT_QTY from ( .. ) bom
(可能還有純 SQL 方式)
例子:
從此處找到的文件中的範例修改
create or replace type my_mult_agg_impl as object ( mult_buffer NUMBER, -- highest value seen so far static function ODCIAggregateInitialize(sctx IN OUT my_mult_agg_impl) return number, member function ODCIAggregateIterate(self IN OUT my_mult_agg_impl, value IN number) return number, member function ODCIAggregateDelete(self IN OUT my_mult_agg_impl, value IN number) return number, member function ODCIAggregateTerminate(self IN my_mult_agg_impl, returnValue OUT number, flags IN number) return number, member function ODCIAggregateMerge(self IN OUT my_mult_agg_impl, ctx2 IN my_mult_agg_impl) return number ); / create or replace type body my_mult_agg_impl is static function ODCIAggregateInitialize(sctx IN OUT my_mult_agg_impl) return number is begin sctx := my_mult_agg_impl(1); return ODCIConst.Success; end; member function ODCIAggregateIterate(self IN OUT my_mult_agg_impl, value IN number) return number is begin mult_buffer := mult_buffer * value; return ODCIConst.Success; end; member function ODCIAggregateDelete(self IN OUT my_mult_agg_impl, value IN number) return number is begin mult_buffer := mult_buffer / value; return ODCIConst.Success; end; member function ODCIAggregateTerminate(self IN my_mult_agg_impl, returnValue OUT number, flags IN number) return number is begin returnValue := mult_buffer; return ODCIConst.Success; end; member function ODCIAggregateMerge(self IN OUT my_mult_agg_impl, ctx2 IN my_mult_agg_impl) return number is begin self.mult_buffer := self.mult_buffer * ctx2.mult_buffer; return ODCIConst.Success; end; end; / CREATE or replace FUNCTION my_mult_agg (input NUMBER) RETURN NUMBER AGGREGATE USING my_mult_agg_impl; / select level, my_mult_agg( level ) over (order by level) as MM from dual connect by level <= 10 order by level;
結果:
這是將行相乘而不是求和的老技巧:
drop table t1 purge; create table t1 (lvl number, qty number); insert into t1 values (1, 1); insert into t1 values (2, 3); insert into t1 values (3, 4); insert into t1 values (4, 2); insert into t1 values (5, 3); commit; select * from t1; LVL QTY ---------- ---------- 1 1 2 3 3 4 4 2 5 3 select lvl, qty, round(exp(sum(ln(qty)) over (order by lvl))) as assy_ext_qty from t1; LVL QTY ASSY_EXT_QTY ---------- ---------- ------------ 1 1 1 2 3 3 3 4 12 4 2 24 5 3 72
當心分數和四捨五入。