Demo for Window Functions

Demo_Window_Functions.sql

drop table stock_qty;
drop table stock_delta;

CREATE TABLE STOCK_QTY (
  PLANT varchar(4),
  MATERIAL varchar(10),
  CALDAY date,
  QTY int,
  primary key (PLANT, MATERIAL, CALDAY)
);

CREATE TABLE STOCK_DELTA(
  PLANT varchar(4),
  MATERIAL varchar(10),
  CALDAY date,
  DELTA int,
  primary key (PLANT, MATERIAL, CALDAY)
);

do begin
  declare la_plant nvarchar(4) array = array( 'DE01', 'DE02');
  DECLARE la_material nvarchar(10) array = array ('TP012', 'TP001');
  
  lt_time = SELECT GENERATED_PERIOD_START AS calday
              FROM SERIES_GENERATE_DATE('INTERVAL 1 DAY', '2020-01-01', '2020-01-10');

  lt_plant = unnest( :la_plant  ) as (plant) ;
  lt_material = unnest(:la_material) as (material);

  lt_tmp = 
select plant,
       material,
       calday, 
      ceil(rand()*100)-50 as delta
  from :lt_time 
  cross join :lt_plant
  cross join :lt_material;
  
  
  insert into stock_qty(
  select plant,
       material,
       calday, 
       abs(sum(delta) over (partition by plant, material order by calday))  as qty
       from :lt_tmp);
       
 insert into stock_delta(
     select plant, 
     material,
     calday,
     qty - ifnull(lag(qty) over (partition by plant, material order by calday) , 0) as qty
     from stock_qty
 );
end;

select * from stock_delta ;
select * from stock_qty;