Use oracle LAG function data in calculations

Question

I want get STOCK and weighted average unit cost WAUC.

TABLE T1:

ROW	ITEM	IN	OUT	PRICE	STOCK	WAUC
1	A	1000	-	20	1000	20
2	A	2000	-	25	-	-
3	A	1500	-	15	-	-
4	A	500	-	20	-	-

I have the first 5 columns and the first two records of last two columns, and want to get the rest of last two columns STOCK and WAUC.

WAUC = ((PREVIOUS_PRICE * PREVIOUS_STOCK) + (CURRENT_IN * CURRENT_PRICE)) / CURRENT_STOCK

So, I write the query:

SELECT ROW,
       SUM(IN - OUT) OVER(ORDER BY ROW) STOCK,
       ((LAG(STOCK * WAUC) OVER (ORDER BY ROW)) + (IN * PRICE)) / STOCK AS WAUC
FROM T1

What I want is :

ROW	ITEM	IN	OUT	PRICE	STOCK	WAUC
1	A	1000	-	20	1000	20
2	A	2000	-	25	3000	23.33
3	A	1500	-	15	4500	20.55
4	A	500	-	20	5000	20.49

In other words, I want to use LAG results in calculation data.

Answer 1

Your formula should be:

WAUC = (
         PREVIOUS_WAUC * PREVIOUS_STOCK
       + (CURRENT_IN - CURRENT_OUT) * CURRENT_PRICE
       )
       / CURRENT_STOCK

---

You can use a MODEL clause (with some extra measurements to make the calculation simpler):

SELECT "ROW", item, "IN", "OUT", price, stock, wauc
FROM   t1
MODEL
  DIMENSION BY ("ROW")
  MEASURES (item, "IN", "OUT", price, 0 AS change, 0 AS stock, 0 AS total, 0 AS wauc)
  RULES (
    change["ROW"] = COALESCE("IN"[cv()], 0) - COALESCE("OUT"[cv()], 0),
    stock["ROW"]  = change[cv()] + COALESCE(stock[cv()-1], 0),
    total["ROW"]  = change[cv()] * price[cv()] + COALESCE(total[cv()-1], 0),
    wauc["ROW"]   = total[cv()] / stock[cv()]
);

Or, from Oracle 12, using MATCH_RECOGNIZE:

SELECT "ROW",
       item,
       "IN",
       "OUT",
       price,
       total_stock AS stock,
       total_cost / total_stock AS wauc
FROM   t1
MATCH_RECOGNIZE(
  ORDER BY "ROW"
  MEASURES
    SUM(COALESCE("IN", 0) - COALESCE("OUT", 0)) AS total_stock,
    SUM((COALESCE("IN", 0) - COALESCE("OUT", 0))*price) AS total_cost
  ALL ROWS PER MATCH
  PATTERN (all_rows+)
  DEFINE
    all_rows AS 1 = 1
)

Or analytic functions:

SELECT "ROW",
       item,
       "IN",
       "OUT",
       price,
       SUM(COALESCE("IN",0) - COALESCE("OUT", 0)) OVER (ORDER BY "ROW")
         AS stock,
       SUM((COALESCE("IN",0) - COALESCE("OUT", 0))*price) OVER (ORDER BY "ROW")
        / SUM(COALESCE("IN",0) - COALESCE("OUT", 0)) OVER (ORDER BY "ROW")
        AS wauc
FROM   t1

Which, for the sample data:

CREATE TABLE t1 ("ROW", ITEM, "IN", "OUT", PRICE, STOCK, WAUC) AS
SELECT 1, 'A', 1000, CAST(NULL AS NUMBER), 20, CAST(NULL AS NUMBER), CAST(NULL AS NUMBER) FROM DUAL UNION ALL
SELECT 2, 'A', 2000, NULL, 25, NULL, NULL FROM DUAL UNION ALL
SELECT 3, 'A', 1500, NULL, 15, NULL, NULL FROM DUAL UNION ALL
SELECT 4, 'A',  500, NULL, 20, NULL, NULL FROM DUAL;

All output:

ROW	ITEM	IN	OUT	PRICE	STOCK	WAUC
1	A	1000		20	1000	20
2	A	2000		25	3000	23.33333333333333333333333333333333333333
3	A	1500		15	4500	20.55555555555555555555555555555555555556
4	A	500		20	5000	20.5

Note: ROW, IN and OUT are keywords and you should not use them as identifiers as you would have to use quoted identifiers everywhere they occur.

db<>fiddle here