patternsqlMajor
Using window function to carry forward first non-null value in a partition
Viewed 0 times
forwardpartitionnonnullfunctionvaluefirstusingwindowcarry
Problem
Consider a table that records visits
Consider this example data (timestamp simplified as counter)
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:
My attempt looks like this:
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.
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 | nullI'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 | YMy 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 tsNote: 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.
Solution
The following query achieves the desired result:
Note the null case statement - if IGNORE_NULL was supported by postgres window functions this wouldnt be needed (as mentioned by @ypercubeᵀᴹ)
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ᵀᴹ)
Code Snippets
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);Context
StackExchange Database Administrators Q#156068, answer score: 20
Revisions (0)
No revisions yet.