Fill null values with the Last not null using Last_value()

Question

I have this table and I have to fill value with the last not null value. Null value can be consecutive (one or more than one).

|    date    | value |
+------------+-------+
| 2021-01-05 | NULL  |
| 2021-01-04 | NULL  |
| 2021-01-03 | 3     |
| 2021-01-02 | NULL  |
| 2021-01-01 | 1     |
+------------+-------+

expected result

|    date    | value |
+------------+-------+
| 2021-01-05 | 3     |
| 2021-01-04 | 3     |
| 2021-01-03 | 3     |
| 2021-01-02 | 1     |
| 2021-01-01 | 1     |
+------------+-------+

I have tried with this but it doesn't work with two consecutive null values. How can I set the range from the last not null to current rows? How to do if the first value is NULL (eg. 2020-12-31 IS null) ?

SELECT

    date, 
    last_VALUE(val) 
        OVER(
            PARTITION BY scope_field
            ORDER BY date desc
            RANGE BETWEEN '1 day' PRECEDING AND '1 day' FOLLOWING
        ) last_val
FROM table

forpas · Accepted Answer · 2021-07-01 18:14:22Z

1

Use array_agg() window function:

SELECT date,
       COALESCE(value, values[array_upper(values, 1)]::int) AS value
FROM (
  SELECT *,
         array_agg(value::varchar) FILTER (WHERE value IS NOT NULL) OVER (ORDER BY date) AS values
  FROM tablename    
) t  
ORDER BY date DESC

See the demo.

edited Jul 1, 2021 at 18:14

answered Jul 1, 2021 at 17:45

forpas

165k10 gold badges51 silver badges85 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

sparkle Over a year ago

There is a problem if there is a NULL in the middle. It gets 5 instead of 1. dbfiddle.uk/…

forpas Over a year ago

@sparkle you are right. Check my edited answer.

Collectives™ on Stack Overflow

Fill null values with the Last not null using Last_value()

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related