lag

Description

Return the 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. 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(col, 1) returns the most recent 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

• Window

Usage

lag( expression, lagby )

Argument	Type	Required	Multiple	Constant
expression	variant	Required	Only one	Variable
lagby	int64	Required	Only one	Constant

Examples

make_col prev_value:window(lag(value, 1), group_by(key))

Obtain the most recent value within the group identified by key and store the results in a new column named prev_value. Because we did not specify a order_by clause in the window function nor in the verb, the lag function would use the default ordering, which is time ascending.

timestamp	key	value	prev_value
100	A	5	null
150	B	6	null
180	A	4	5
300	B	5	6
400	A	3	4

make_col high_value:window(lag(value, 2), order_by(value, false), group_by(key))

Obtain the second highest value below the current value within the group identified by key and store the results in a new column named high_value. We use an order_by function to order the rows by value ascending.

timestamp	key	value	high_value
100	A	5	3
200	B	6	2
200	A	4	null
300	B	5	null
400	A	3	null
400	B	2	null
500	A	7	4

make_col prev_value:window(lag(value, 1), group_by(key), frame_exact(back:100ns))

Obtain the most recent value within the group identified by key and store the results in a new column named prev_value. Because a frame_exact is specified for the window function, prev_value would be null if the most recent value is found outside the window frame.

timestamp	key	value	prev_value
100	A	5	null
150	B	6	null
180	A	4	5
300	B	5	null
400	A	3	null