Oracle SQL to Compute values based on previous rows

 

 Excepted Output:

 

 

 

create table prev_rows(id varchar2(10),dt varchar2(10),val number);

insert into prev_rows values('A','JAN-23',100);

insert into prev_rows values('A','FEB-23',60);

insert into prev_rows values('A','MAR-23',40);

insert into prev_rows values('B','JAN-23',30);

insert into prev_rows values('B','FEB-23',20);

commit;

 

select ID, to_date(DT,'MON-YY') val_dt,VAL,

row_number() over(partition by id order by DT)

 from prev_rows;

 

select ID,dt,VAL,to_date(DT,'MON-YY') val_dt from prev_rows;

 

 select ID,dt,VAL,to_date(DT,'MON-YY') val_dt,

 row_number() over(partition by id order by to_date(DT,'MON-YY')) rn from prev_rows;

 

 select ID,dt,VAL,to_date(DT,'MON-YY') val_dt,

 row_number() over(partition by id order by to_date(DT,'MON-YY')) rn from prev_rows;

 

  select ID,dt,VAL,to_date(DT,'MON-YY') val_dt,

 row_number() over(partition by id order by to_date(DT,'MON-YY')) rn,

 row_number() over(partition by id order by dt) rn from prev_rows;

 

  select ID,dt,VAL,to_date(DT,'MON-YY') val_dt,

 row_number() over(partition by id order by to_date(DT,'MON-YY')) rn,

 sum(val) over(partition by id order by to_date(DT,'MON-YY')) sum_sal from prev_rows;

 

 select ID, to_date(DT,'MON-YY') val_dt,VAL,

row_number() over(partition by id order by to_date(DT,'MON-YY')) rn,

sum(val) over(partition by id order by to_date(DT,'MON-YY')) sum_sal,

round(sum(val) over(partition by id order by to_date(DT,'MON-YY'))/row_number() over(partition by id order by to_date(DT,'MON-YY'))) res

 from prev_rows;

 

  select ID, dt,

round(sum(val) over(partition by id order by to_date(DT,'MON-YY'))/row_number() over(partition by id order by to_date(DT,'MON-YY'))) res

 from prev_rows;