CODEWITHSUNDEEP
postgresql
sparkle
sparkle

Reputation: 7390

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

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

Upvotes: 0

Views: 971

Answers (1)

forpas
forpas

Reputation: 164064

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.

Upvotes: 1

Related Questions