How to replace the null value with the next non null value in sql?

Question

I have a table with the below details:

  DATE_       day_name      VALUE1
4-MAR-22      Friday        16552
3-MAR-22     Thursday       null
2-MAR-22     Wednesday      null
1-MAR-22     Tuesday        null
28-FEB-22    Monday         null
27-FEB-22    Sunday         null
26-FEB-22   Saturday        null
25-FEB-22    Friday        16658.40
24-FEB-22   Thursday       null
23-FEB-22   Wednesday      null
22-FEB-22   Tuesday        null
21-FEB-22   Monday         null
20-FEB-22   Sunday         null
19-FEB-22   Saturday       null
18-FEB-22   Friday        17276.30
17-FEB-22   Thurday        null

I tried doing:

with grouped_table as (
    select DATE_, DAY_NAME, VALUE1, count(VALUE1) over (order by DATE_) as grp
    from table1)
select DATE_, DAY_NAME, VALUE1,
       grp,
       FIRST_value(VALUE1) over (partition by grp order by date_) as VALUE_NEW
from grouped_table;

and the current result that i get is:

Current Output:

  DATE_       day_name      VALUE1
4-MAR-22      Friday        16552
3-MAR-22     Thursday       16658.40
2-MAR-22     Wednesday      16658.40
1-MAR-22     Tuesday        16658.40
28-FEB-22    Monday         16658.40
27-FEB-22    Sunday         16658.40
26-FEB-22   Saturday        16658.40
25-FEB-22    Friday         16658.40
24-FEB-22   Thursday        17276.30
23-FEB-22   Wednesday       17276.30
22-FEB-22   Tuesday         17276.30
21-FEB-22   Monday          17276.30
20-FEB-22   Sunday          17276.30
19-FEB-22   Saturday         17276.30
18-FEB-22   Friday          17276.30
17-FEB-22   Thurday         17374.75

But the output that i am trying to achieve is below:

Expected Output:

  DATE_       day_name      VALUE1
4-MAR-22      Friday        16552
3-MAR-22     Thursday       16552
2-MAR-22     Wednesday      16552
1-MAR-22     Tuesday        16552
28-FEB-22    Monday         16552
27-FEB-22    Sunday         16552
26-FEB-22   Saturday        16552
25-FEB-22    Friday        16658.40
24-FEB-22   Thursday       16658.40
23-FEB-22   Wednesday      16658.40
22-FEB-22   Tuesday        16658.40
21-FEB-22   Monday         16658.40
20-FEB-22   Sunday         16658.40
19-FEB-22   Saturday       16658.40
18-FEB-22   Friday         17276.30
17-FEB-22   Thurday        17276.30

What should i change in my code to arrive at the expected output? The concept is only the Friday's value1 is available. I need to fill that entire weeks data with the friday's value1.

Answer 1

You are looking for a backfill. A backfill fills nulls with the next valid value. A forwardfill is the opposite, it fills up nulls with the last valid value. This is assuming that data is sorted in ascending order.

You are doing a forward fill without specifying sorting. You just need to add DESC and try it out. Like this:

FIRST_value(VALUE1) over (partition by grp order by date_ DESC) as VALUE_NEW

For reference you can visit see this thread where they use the below code (adjusted to your case) to forward fill values

#standardSQL
SELECT time
LAST_VALUE(VALUE1 IGNORE NULLS) OVER(ORDER BY DATE DESC) VALUE1,
FROM `grouped_table`

Hope I could help!