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.
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 andCOPY
commands.