PostgreSQL – Using Window Function to Carry Forward First Non-Null Value

postgresqlwindow functions

Consider a table that records visits

create table visits (
  person varchar(10),
  ts timestamp, 
  somevalue varchar(10) 
)

Consider this example data (timestamp simplified as counter)

ts| person    |  somevalue
-------------------------
1 |  bob      |null
2 |  bob      |null
3 |  jim      |null
4 |  bob      |  A
5 |  bob      | null
6 |  bob      |  B
7 |  jim      |  X
8 |  jim      |  Y
9 |  jim      |  null

I'm trying to carry forward the last non-null somevalue of the person to all his future visits until that value changes (ie becomes the next non-null) value.

Expected result set looks like this:

ts|  person   | somevalue | carry-forward 
-----------------------------------------------
1 |  bob      |null       |   null
2 |  bob      |null       |   null
3 |  jim      |null       |   null
4 |  bob      |  A        |    A
5 |  bob      | null      |    A
6 |  bob      |  B        |    B
7 |  jim      |  X        |    X
8 |  jim      |  Y        |    Y
9 |  jim      |  null     |    Y

My attempt looks like this:

 select *, 
  first_value(somevalue) over (partition by person order by (somevalue is null), ts rows between UNBOUNDED PRECEDING AND current row  ) as carry_forward

 from visits  
 order by ts

Note: the (somevalue is null) evaluates to 1 or 0 for the purposes of sorting so I can get the first non-null value in the partition.

The above doesn't give me the result I'm after.

Best Answer

The following query achieves the desired result:

select *, first_value(somevalue) over w as carryforward_somevalue
from (
  select *, sum(case when somevalue is null then 0 else 1 end) over (partition by person order by id ) as value_partition
  from test1

) as q
window w as (partition by person, value_partition order by id);

Note the null case statement - if IGNORE_NULL was supported by postgres window functions this wouldnt be needed (as mentioned by @ypercubeᵀᴹ)

Related Question