I have a table which looks like this:

I want that rows, without a value in money_earned (which means that the money_earned column was empty that day) - will and fill the money_earned with last known value, so it to look like this:

I have tried to look up for something like that, but postgresql doesn't support ignore nulls in lag window function :(

thank you!

CodePudding user response：

The LOG() function would be suitable for this purpose, but in your case you need to get first not null previous element. What the LOG() function can't handle.

I suggest creating your own function that would return the first non-null value of the previous element.

CREATE OR REPLACE FUNCTION log_not_null(row_num int, dafult text = '1001') RETURNS text LANGUAGE plpgsql AS $$
DECLARE
  result text; 
  index int;
BEGIN 
  if row_num > 0  then 
      index := row_num - 1;
   else
      return dafult;
   end if;
   result := (select money_earned from my_table  offset index limit 1);
   if result is null then 
     return log_not_null(index); 
   end if;  
   return result;
END
$$;


select id,  log_not_null((row_number() over ())::int) as money_earned, days_since_start from my_table;

Note:

If the previous value is null, then the function is called recursively until it reaches the top element, and if the topmost element is also null, then the function will return the value from the dafult variable.

Demo in DBfiddle

CodePudding user response：

You can do this (warning, untested!