Calculate current based on previous formula-CodePudding

I have a scenario where the current column value is calculated based on the previous value calculated by the formula The initial row of the group has no previous value so it will not consider.

Formula for loss= relase-withdraw-least(previous_row_loss,reverse)

Here below loss is the column I need to calculate.

I tried with the following query but not getting expected output. Can you please guide me here.

SELECT
pid,release,withdraw,reverse,
SUM(release - withdraw - LEAST( LAG(loss,1,0) OVER (ORDER BY pid)),reverse))  as loss 
FROM transactions

CodePudding user response：

You can use a MODEL clause:

SELECT *  
FROM   (
  SELECT t.*,
         ROW_NUMBER() OVER (ORDER BY pid, fiscalperiod) AS rn
  FROM   table_name t
)
MODEL
  DIMENSION BY (rn)
  MEASURES (pid, fiscalperiod, release, withdraw, reverse, 0 AS loss)
  RULES (
    loss[1]    = release[1] - withdraw[1] - reverse[1],
    loss[rn>1] = release[cv()] - withdraw[cv()] - LEAST(reverse[cv()], loss[cv()-1])
                   loss[cv()-1]
);

Or, probably, much less efficiently a recursive query:

WITH numbered_rows AS (
  SELECT t.*,
         ROW_NUMBER() OVER (ORDER BY pid, fiscalperiod) AS rn
  FROM   table_name t
),
recursive_query (rn, pid, fiscalperiod, release, withdraw, reverse, loss) AS (
  SELECT rn,
         pid,
         fiscalperiod,
         release,
         withdraw,
         reverse,
         release - withdraw - reverse
  FROM   numbered_rows
  WHERE  rn = 1
UNION ALL
  SELECT n.rn,
         n.pid,
         n.fiscalperiod,
         n.release,
         n.withdraw,
         n.reverse,
         n.release - n.withdraw   GREATEST(r.loss - n.reverse, 0)
  FROM   numbered_rows n
         INNER JOIN recursive_query r
         ON (n.rn = r.rn   1)
)
SELECT *
FROM   recursive_query;

Which, for your sample data:

CREATE TABLE table_name (pid, fiscalperiod, release, withdraw, reverse) AS
SELECT 'A1', 2022001, 10, 10, 10 FROM DUAL UNION ALL
SELECT 'A1', 2022002, 20, 13,  2 FROM DUAL UNION ALL
SELECT 'A1', 2022003, 20, 20, 10 FROM DUAL UNION ALL
SELECT 'A2', 2022002, 15, 10, 13 FROM DUAL;

Both output:

RN PID FISCALPERIOD RELEASE WITHDRAW REVERSE LOSS

1 A1 2022001 10 10 10 -10

2 A1 2022002 20 13 2 7

3 A1 2022003 20 20 10 0

4 A2 2022002 15 10 13 5

RN	PID	FISCALPERIOD	RELEASE	WITHDRAW	REVERSE	LOSS
1	A1	2022001	10	10	10	-10
2	A1	2022002	20	13	2	7
3	A1	2022003	20	20	10	0
4	A2	2022002	15	10	13	5

db<>fiddle here