PostgreSQL fill out nulls with previous value by category-CodePudding

I am trying fill out some nulls where I just need them to be the previous available value for a name (sorted by date).

So, from this table:

I need the query to output this:

Now, the idea is that for Jane, on the second and third there was no score, so it should be equal to the previous date on which an score was available, for Jane. And the same for Jon. I am trying coalesce and range, but range is not implemented yet in Redshift. I also looked into other questions and they don't fully apply to different categories. Any alternatives?

Thanks!

CodePudding user response：

You can just use COALESCE with self LEFT JOINed table by name, less day value and not NULL score like this

SELECT
   data.day,
   data.name,
   COALESCE(data.score, prev.score) AS score
FROM data   
LEFT JOIN data prev ON prev.name = data.name AND prev.day < data.day AND prev.score IS NOT NULL
ORDER BY name DESC, day

You can check a working demo here

CodePudding user response：

select day, name, 
coalesce(score, (select score 
                 from [your table] as t 
                 where t.name = [your table].name and t.date < [your table].date 
                 order by date desc limit 1)) as score
from [your table]

The query straightforwardly implements the logic you described:

if score is not null, coalesce will return its value without executing the subquery
if score is null, the subquery will return the last available score for that name before the given date