I have a table which looks like this:

I want that rows, without a days_since_start (which means that the money_earned column was empty that day) - will include all the days PER ID, with "money" being null to indicate there was no earnings, so it to look like this:

I have tried to look up for something like that, but I don't even know what function does that...

thank you!

CodePudding user response：

You can first generate table for your ids and days by query

SELECT d1.id, generate_series(1, max(d1.days_since_start)) AS days_since_start
FROM days d1 JOIN days d2 ON d1.id = d2.id GROUP BY d1.id

(if you need all numbers from 1 to 100, you can replase exspression max(d1.days_since_start) with the number 100)

and then join it with your table. the final query might look like this

WITH genDays AS
     (SELECT d1.id, generate_series(1, max(d1.days_since_start)) AS days_since_start
      FROM days d1 JOIN days d2 ON d1.id = d2.id GROUP BY d1.id)

SELECT coalesce(genDays.id, d3.id) AS id,
       d3.money_earned,
       coalesce(d3.days_since_start, genDays.days_since_start) AS days_since_start
FROM days d3 FULL JOIN genDays ON genDays.id = d3.id
                     AND genDays.days_since_start = d3.days_since_start

the output will be as you need

if you need to fill the nulls with last nonnull value per id then you can modify the query like here

WITH genDays AS
     (SELECT d1.id as id, generate_series(1,  100) AS days_since_start
      FROM days d1 JOIN days d2 ON d1.id = d2.id GROUP BY d1.id)

SELECT coalesce(genDays.id, d3.id) AS id,
       coalesce(d3.money_earned, 
            (
                select d4.money_earned
                from days d4
                where d4.id = genDays.id
                  and d4.days_since_start < genDays.days_since_start
                order by d4.days_since_start desc
                limit 1
            )) as money_earned,
       coalesce(d3.days_since_start, genDays.days_since_start) AS days_since_start
FROM days d3 FULL JOIN genDays ON genDays.id = d3.id
                  AND genDays.days_since_start = d3.days_since_start