I have a table where I have to insert values in Final_value column

date      column1  column2   Final_value   
01-01-10    5.5       Null        Null
02-01-10    4.2       Null        NULL
03-01-10    5.3       0.4         NULL
04-01-10    6.4       Null        NULL
05-01-10    7.0       0.3         NULL
06-01-10    7.7       Null        NULL

Explanation of the task

1. First value of the Final_value column will be = 1st value of column1 i.e 5.5

2. From the 2nd row we will check if column2 is Not Null
   a) if TRUE , then formula would be
   previous Final_value column* ( current column1 value/previous Column1 value ) column2 value * (previous column1 value/ previous Final_value )

   b) if FALSE, then formula would be the Previous value of Final_value column * ( current value of column1/ previous value of column 1)

Required Output

date      column1  column2   Final_value   
01-01-10    5.5       Null        5.5
02-01-10    4.2       Null        4.2
03-01-10    5.3       0.4         5.7
04-01-10    6.4       Null        6.883019
05-01-10    7.0       0.3         7.850943
06-01-10    7.7       Null        8.636038

CodePudding user response：

declare
v_num integer := 1;
v_column1 number(8,2);
v_column2 number(8,2);
v_Final_value number(8,2);
begin
for rec in (select * from tab order by mydate)
loop
   if(v_num = 1) then
       
       update tab set Final_value = column1 where mydate = rec.mydate;     
   else
       if(rec.column2 is not null) then
          update tab set Final_value =
           v_Final_value * (v_column1/rec.column1)  
           rec.column2 * (v_column1/v_Final_value) where mydate = rec.mydate;
       else
          update tab set Final_value =
          v_Final_value * (rec.column1 / v_column1) where mydate = rec.mydate;
       end if;
   end if;
   
   
   v_num:= v_num  1;
   
   v_column1 := rec.column1;
   v_column2 := rec.column2;
   
   select final_value 
   into v_Final_value
   from tab
   where mydate = rec.mydate;
     
end loop;
end;

CodePudding user response：

Assuming that your table contains consecutive days and you start with the smalest one, you can use a recursive subquery factoring to get your expected data.

with t1(date_d, column1, column2, Final_value) as (
 select date_d, column1, column2, column1 Final_value 
 from tab 
 where date_d = (select min(date_d) from tab)
union all
 select t2.date_d, t2.column1, t2.column2, 
 case when t2.column2 is not null then 
     t1.Final_value * (t2.column1 / t1.column1)   t2.column2 * (t1.column1 / t1.Final_value)
 else
     t1.Final_value * (t2.column1 / t1.column1)
 end as  Final_value 
 from t1
 join tab t2 on t1.date_d   1 = t2.date_d
)
select * from t1

Note that the anchor subquery selects the first row with the minimal date and the initial Final_value

The recursive subquery (the second query in the union all) joins the row with the next day, so you can clearly describe your formula where t1 reference the previous day and the t2 the current one.

Result is as expected

DATE_D                 COLUMN1    COLUMN2 FINAL_VALUE
------------------- ---------- ---------- -----------
01.01.2010 00:00:00        5,5                    5,5
02.01.2010 00:00:00        4,2                    4,2
03.01.2010 00:00:00        5,3 ,4                 5,7
04.01.2010 00:00:00        6,4             6,88301887
05.01.2010 00:00:00          7 ,3          7,80724926
06.01.2010 00:00:00        7,7             8,58797418

Use it in UPDATE/INSERT as you like, but you may find that defining the final_value in a view only is the best solution...

CodePudding user response：

You may consider to use model clause that allows straightforward reference to the previous calculation of the same measure without explicit recursion (and sometimes performs faster than recursive query). In the query belowcv() is the current value of a dimension (i.e. some_measure[cv() - 1] is the value of column measure of the previous row). I've also added nvl to handle division by zero, which can be removed if you wish.

with  b as (
  select
    a.*,
    /*Add result column and auxiliarry numbering column to reference prevoius row*/
    cast(null as number) as final_value,
    row_number() over(order by dt asc) as rn
  from a
)
select *
from b
model
  /*Our generated ordering column*/
  dimension by (rn)
  /*Output columns apart from dimensions*/
  measures (dt, column1, column2, final_value)
  /*Ordered execution of the rules*/
  rules update sequential order (
    /*First row should have column1 value*/
    final_value[1]= column1[cv()],
    /*Other rows should have values according to formula*/
    final_value[rn > 1] order by rn asc =
      case
        when column2[cv()] is not null
        then final_value[cv() - 1]*column1[cv()]/nullif(column1[cv() - 1], 0)
            column2[cv()]*column1[cv() - 1]/nullif(final_value[cv() - 1], 0)
        else
          final_value[cv() - 1]*column1[cv()]/nullif(column1[cv() - 1], 0)
      end
  )

RN | DT        | COLUMN1 | COLUMN2 |                              FINAL_VALUE
-: | :-------- | ------: | ------: | ---------------------------------------:
 1 | 01-JAN-10 |     5.5 |    null |                                      5.5
 2 | 02-JAN-10 |     4.2 |    null |                                      4.2
 3 | 03-JAN-10 |     5.3 |      .4 |                                      5.7
 4 | 04-JAN-10 |     6.4 |    null | 6.88301886792452830188679245283018867925
 5 | 05-JAN-10 |       7 |      .3 | 7.80724925521350546176762661370407149951
 6 | 06-JAN-10 |     7.7 |    null | 8.58797418073485600794438927507447864946

db<>fiddle here