I want get STOCK and weighted average unit cost WAUC.

TABLE T1:

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 :

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

CodePudding user response：

Your problem has nothing to do with "lag".

Using MT0's sample data:

select "ROW", item, "IN", "OUT", price,
       sum(nvl("IN", 0) - nvl("OUT", 0)) 
           over (partition by item order by "ROW") as stock,
       round(sum((nvl("IN", 0) - nvl("OUT", 0)) * price)
           over (partition by item order by "ROW") 
       / sum(nvl("IN", 0) - nvl("OUT", 0))
           over (partition by item order by "ROW"), 2 ) as wauc
from   t1
;

   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.56
     4 A       500            20   5000   20.5

CodePudding user response：

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