Oraclesql在計算中使用前一行資料

我有一個包含 06 列的表 T1，并希望使用選擇查詢獲取新的兩列。這是我想要獲得的帶有兩個額外列（STOCK，WAUC）的 T1：

CREATE TABLE T1 (MOUVEMENT NUMBER(2), OPERATION VARCHAR2(5), ITEM VARCHAR2(5), INPUT_QTY NUMBER(6, 2), OUTPUT_QTY NUMBER(6, 2), INPUT_PRICE NUMBER(6, 2), STOCK NUMBER(6, 2), WAUC NUMBER(6, 2));
INSERT ALL
INTO T1 VALUES(1, 'I', 'A', 1500,  0,      5,      1500,       5)
INTO T1 VALUES(2, 'I', 'A', 700,   0,      6,      2200,       5.31)
INTO T1 VALUES(3, 'O', 'A', 0,     800,    0,      1400,       5.31)
INTO T1 VALUES(4, 'I', 'A', 1000,  0,      5,      2400,       5.18)
INTO T1 VALUES(5, 'O', 'A', 0,     500,    0,      1900,       5.18)
INTO T1 VALUES(6, 'I', 'A', 1000,  0,      7,      2900,       5.8 )
INTO T1 VALUES(7, 'I', 'A', 2000,  0,      7,      4900,       6.28)
INTO T1 VALUES(8, 'I', 'A', 5000,  0,      7,      5400,       6.34)
INTO T1 VALUES(9, 'O', 'A', 0,     1000,   0,      4400,       6.34)
INTO T1 VALUES(10, 'I','A', 1000,  0,      5,      5400,       6.09)
SELECT 1 FROM DUAL;

WAUC 就像加權平均單位成本來評估我們的庫存。

• 如果第一條記錄：STOCK = INPUT 和 WAUC = INPUT_PRICE;
• 如果新的 INPUT 操作：新的 WAUC 應該是：（最后生成的 WAUC * 最后生成的庫存） （當前輸入 * 當前 INPUT_PRICE））/當前生成的 STOCK。

例如第二行：WAUC = ((5 * 1500) (700 * 6)) / 2200 = 5.31

• 如果新的 OUTPUT 操作：WAUC 應該是最后生成的 WAUC。

第 3 行的Ex：WAUC = 同一專案 A 的最后生成的 WAUC (5.31)。

• 意思是，每次新的 INPUT 操作都應該更改 WAUC。
• 在我看來，STOCK 和 WAUC 應該即時生成，而不是作為記錄生成，因為否則，只有一個不小心錯誤的 INPUT_PRICE，會導致下一個 WAUC(s) -> 錯誤的下一個計算 -> (錯誤的作業)。

我怎樣才能做到這一點？提前致謝。

uj5u.com熱心網友回復：

您的邏輯是需要model條款的教科書示例，幾乎可以按照您詳細指定的方式重寫為該條款（注意該model條款是野獸，要了解更多資訊，請參見此處或此處或此處）：

with t1 (mouvement, operation, item, input_qty, output_qty, input_price, stock_expected, wauc_expected) as (
 select 1, 'I', 'A', 1500,  0,      5,      1500,       5    from dual union all
 select 2, 'I', 'A', 700,   0,      6,      2200,       5.31 from dual union all
 select 3, 'O', 'A', 0,     800,    0,      1400,       5.31 from dual union all
 select 4, 'I', 'A', 1000,  0,      5,      2400,       5.18 from dual union all
 select 5, 'O', 'A', 0,     500,    0,      1900,       5.18 from dual union all
 select 6, 'I', 'A', 1000,  0,      7,      2900,       5.8  from dual union all
 select 7, 'I', 'A', 2000,  0,      7,      4900,       6.28 from dual union all
 select 8, 'I', 'A', 500,  0,      7,      5400,       6.34 from dual union all
 select 9, 'O', 'A', 0,     1000,   0,      4400,       6.34 from dual union all
 select 10, 'I','A', 1000,  0,      5,      5400,       6.09 from dual
)
select * from (
  select t1.*, 0 as stock_actual, 0 as wauc_actual from t1
)
model
  dimension by (row_number() over (order by mouvement) as rn)
  measures (mouvement, operation, item, input_qty, output_qty, input_price, stock_expected, wauc_expected, stock_actual, wauc_actual)
  rules (
    stock_actual[any] = coalesce(stock_actual[cv(rn) - 1], 0)   case operation[cv(rn)]
      when 'I' then input_qty[cv(rn)]
      when 'O' then -output_qty[cv(rn)]
    end,
    wauc_actual[any] = case
      when cv(rn) = 1
        then input_price[cv(rn)]
      when operation[cv(rn)] = 'I'
        then trunc((wauc_actual[cv(rn) - 1] * stock_actual[cv(rn) - 1]   input_qty[cv(rn)] * input_price[cv(rn)]) / stock_actual[cv(rn)], 2)
      when operation[cv(rn)] = 'O'
        then wauc_actual[cv(rn) - 1]
    end
  )
order by mouvement

（我更改了operation=5000->500for 的拼寫錯誤mouvement=8并將截斷添加到 2 位數 - 我從您的預期結果中猜到了。）

資料庫小提琴在這里。

請注意，簡單的分析函式不足以計算 ，wauc因為它們只能訪問輸入資料集列的先前值，而不能訪問由函式本身計算的列的值。因為stock可以使用運行總計，sum(input_qty) over (order by mouvement) - sum(output_qty) over (order by mouvement)但是wauc幾乎沒有任何明確的公式。

標籤：sql 递归 落后

上一篇：c -如何在C 中的k個節點之后遞回拆分鏈表？

下一篇：如何遞回遍歷潛在的無限樹