EN VI

How to implement recursion using SQL Oracle?

I have a table with the following columns:

ID, date, amount, amount2

Column amount2 has values only in the first row. I need to recursively calculate each row of column amount2 using values from previous row of this column and also column amount

second row for amount2 should be: first row value of amount2 - second row value of amount
third row for amount2 should be: second row value of amount2 - third row value of amount ...

I tried to use following code, but it does not work:

SELECT   
    first_column,  
    CASE   
        WHEN second_column IS NULL THEN LAG(first_column, 1, first_column) OVER (ORDER BY row_id) - LAG(first_column, 1, first_column) OVER (ORDER BY row_id DESC)  
        ELSE second_column  
    END AS calculated_second_column
from table;

Reproducible example:

CREATE TABLE your_table_name (  
    id INT,  
    date DATE,  
    amount DECIMAL(10,2),  
    amount1 DECIMAL(10,2)  
); 




INSERT INTO your_table_name (id, date, amount, amount1)  
VALUES  
    (234, '2020-01-01', 4, 10),  
    (234, '2020-01-02', 7, NULL),  
    (234, '2020-01-03', 10, NULL),  
    (234, '2020-01-04', 15, NULL),  
    (234, '2020-01-05', 6, NULL);

I expect to have in column amount1; 10, 10-7=3, 3-10=-7, -7-15=-22, -22-6=-28

Solution:

Find first row using the FIRST_VALUE analytic function and then use the SUM analytic function to find a cumulative total and subtract:

SELECT  id,
        dt,
        amount,
        -- Find the first amount + amount1 value
        FIRST_VALUE(amount + amount1) OVER (PARTITION BY id ORDER BY dt)
        -- Subtract the cumulative sum of the amount values
        - SUM(amount) OVER (PARTITION BY id ORDER BY dt)
        AS amount1
FROM    your_table_name;

Or, using COALESCE in the cumulative SUM:

SELECT  id,
        dt,
        amount,
        SUM(COALESCE(amount1, -amount)) OVER (PARTITION BY id ORDER BY dt)
          AS amount1
FROM    your_table_name;

Or, using a MODEL clause:

SELECT id, dt, amount, amount1
FROM (
  SELECT t.*, ROW_NUMBER() OVER (PARTITION BY id ORDER BY dt) AS rn
  FROM   your_table_name t
)
MODEL
  PARTITION BY (id)
  DIMENSION BY (rn)
  MEASURES (dt, amount, amount1)
  RULES (
    amount1[rn>1] = amount1[cv(rn)-1] - amount[cv(rn)]
  );

Which, for the sample data:

CREATE TABLE your_table_name (
  id      INT,
  dt      DATE,
  amount  DECIMAL(10,2),
  amount1 DECIMAL(10,2)
);

INSERT INTO your_table_name (id, dt, amount, amount1)
VALUES
(234, DATE '2020-01-01',  4,   10),
(234, DATE '2020-01-02',  7, NULL),
(234, DATE '2020-01-03', 10, NULL),
(234, DATE '2020-01-04', 15, NULL),
(234, DATE '2020-01-05',  6, NULL);

All output:

ID	DT	AMOUNT	AMOUNT1
234	2020-01-01 00:00:00	4	10
234	2020-01-02 00:00:00	7	3
234	2020-01-03 00:00:00	10	-7
234	2020-01-04 00:00:00	15	-22
234	2020-01-05 00:00:00	6	-28

fiddle