16

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.

3
  • Could you just paste the pg_dump for your test data rather than pasting the data in a psql output, and the schema for the table? pg_dump -t table -d database we need the create and COPY commands. Commented Nov 22, 2016 at 21:55
  • 3
    wiki.postgresql.org/wiki/First/last_%28aggregate%29
    – user1822
    Commented Nov 22, 2016 at 22:18
  • 2
    @a_horse_with_no_name that deserves to be an answer. Commented Nov 22, 2016 at 22:21

2 Answers 2

20

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ᵀᴹ)

2
  • 5
    Also the simple count(somevalue) over (...) Commented Nov 24, 2016 at 0:07
  • Thanks for this solution, it fixed the problem for me. Can you please add some explanation to this solution? As of how it's working? Thanks.
    – saadi
    Commented Jun 20, 2021 at 9:25
6

The problem is in the gaps-and-islands category of problems. It's a pity that Postgres has not yet implemented IGNORE NULL in window functions like FIRST_VALUE(), otherwise it would be trivial, with a simple change in your query.

There are probably many ways for this to be solved using window functions or recursive CTEs.

Not sure if it is the most efficient way but a recursive CTE does solve the problem:

with recursive 
    cf as
    (
      ( select distinct on (person) 
            v.*, v.somevalue as carry_forward
        from visits as v
        order by person, ts
      ) 
      union all
        select 
            v.*, coalesce(v.somevalue, cf.carry_forward)
        from cf
          join lateral  
            ( select v.*
              from visits as v
              where v.person = cf.person
                and v.ts > cf.ts
              order by ts
              limit 1
            ) as v
            on true
    )
select cf.*
from cf 
order by ts ;
2
  • It does indeed solve the problem however its more complex than it needs to be. See my answer below Commented Nov 23, 2016 at 18:44
  • 2
    Yes, your answer seems good! Commented Nov 23, 2016 at 19:36

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.