Roll over value from previous row when row is null

user_12637088 0
Viewed 474

I have problem in rolling over value from previous row when value is null. I currently have a dataset

+------+---------+
month.   average
+------+---------+
01/2018    null
02/2018    null
03/2018    4
04/2018    10
05/2018    null
06/2018    20
07/2018    null

My desired output should look similar to this. I'm trying to roll over previous value where average is null

+------+---------+
month.   average
+------+---------+
01/2018    0
02/2018    0
03/2018    4
04/2018    10
05/2018    10
06/2018    20
07/2018    20

I tried the lag function but the null keeps appearing.

my current query looks similar to this:

with cte as(
select to_char(created_at,'MM') || to char(created_at,'YYYY') as month
,AVG(CASE WHEN status ='ready' THEN col ELSE null END) as average
from table
group by month
)
select 
month
,lag(average) over (order by month ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) as average
from cte
postgresql sql
2 Answers

The idea would be lag(ignore nulls), but Postgres does not support that.

If you just have one NULL in a row, then lag() is sufficient (as another answer suggests). But that is not very generalizable.

If the values are known to be increasing a cumulative max is probably the most efficient:

select t.*,
       coalesce(max(average) over (order by month), 0) as imputed_average
from t;

This works with multiple NULLs in a row. If the data doesn't have that increasing property, then another alternative is two levels of window functions:

select t.*,
       coalesce(max(average) over (partition by non_null_month), 0) as imputed_average
from (select t.*,
             max(month) filter (where average is not null) over (order by month) as non_null_month
      from t
     ) t;

There is another method using arrays. It doesn't require subqueries, but it might not scale well:

select t.*,
       coalesce((array_remove(array_agg(average) over (order by month), null))[cardinality(array_remove(array_agg(average) over (order by month), null))], 0) as imputed_average
from t;

Here is a db<>fiddle.

user_11440350

use coalesce

select 
  month,
  COALESCE(average,
           lag(average) over (order by month ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW),
           0) as average
from cte
user_2157520
Related