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

storable value

Domain

This is a window function (calculates over a group of multiple input rows using windowing.)

Categories

Usage

lag_not_null(expression, lagby)

Argument

Type

Optional

Repeatable

Restrictions

expression

storable value

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