3

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
Improve this question
7
  • 1
    1) Only tag the actual RDBMS you are using. 2) Show us what you tried that didn't work. Commented Feb 23, 2021 at 20:54
  • I have removed the conflicting dbms tags. Put one of them back, the one for the dbms actually used. Commented Feb 23, 2021 at 20:54
  • Please share the query with Lag() Commented Feb 23, 2021 at 20:58
  • @KaziMohammadAliNur. with cute as(select to_char(created_at,'YYYY') || to char(created_at,'MM') as month,AVG(CASE WHEN status =ready THEN 1 ELSE 0 END) as average from table group by Commented Feb 23, 2021 at 21:02
  • @KaziMohammadAliNur the query has been added to the post Commented Feb 23, 2021 at 21:12

2 Answers 2

Reset to default
3

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.

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

7 Comments

max(average) doesn't work if average is more than the last non null value for that row, right?
@eshirvana . . . That is correct. I think the answer explains that.
@GordonLinoff trying this syntax 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;..I got a syntax error
@Mandibajr . . . I fixed the third query and added a db<>fiddle. There was some wishful programming in my original answer.
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; this works but the other option returns error still
|
-1

use coalesce

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

3 Comments

Thanks @Abelisto, the point was to show how to use coalesce with lag -- but I didn't check if the lag statement made sense....
that doesn't work if there are multiple null values in between , also LAG() has default value parameter , no need for COALESCE
@eshirvana -- I was going by his example he wanted there to be a 0 if multiple nulls

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.