lag_not_null¶
Description¶
Return the non-null value of one column in a previous row across an ordered
group. offset is the number of rows backwards from the current row to obtain
a value, and any null value does not count towards the offset. The function can
still return null if no non-null value is found in the window frame. If no
ordering is specified, the default ordering is that of the invoking verb, which
is generally the valid_from timestamp, ascending. In this case,
lag_not_null(col, 1) returns the most recent non-null prior value for column
col.
Return type¶
value
Domain¶
This is a window function (calculates over a group of multiple input rows using windowing.)
Categories¶
Usage¶
lag_not_null(value, lagby)
Argument |
Type |
Optional |
Repeatable |
Restrictions |
|---|---|---|---|---|
value |
storable |
no |
no |
none |
lagby |
int64 |
no |
no |
constant |
Examples¶
make_col prev_value:window(lag_not_null(value, 1), group_by(key))
Obtain the most recent non-null value within the group identified by key and
store the results in a new column named prev_value.
timestamp |
key |
value |
prev_value |
|---|---|---|---|
100 |
A |
5 |
null |
150 |
B |
6 |
null |
180 |
A |
null |
5 |
200 |
A |
null |
5 |
300 |
B |
5 |
6 |
400 |
A |
3 |
5 |