lead

Description

Return the value of one column in a following row across an ordered group. offset is the number of rows forward 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, lead(col, 1) returns the next 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

lead(value, leadby)

Argument

Type

Optional

Repeatable

Restrictions

value

storable

no

no

none

leadby

int64

no

no

constant

Examples

make_col next_value:window(lead(value, 1), group_by(key))

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

timestamp

key

value

next_value

100

A

5

4

150

B

6

5

180

A

4

3

300

B

5

null

400

A

3

null

make_col low_value:window(lead(value, 2), order_by(value, false), group_by(key))

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

timestamp

key

value

low_value

100

A

5

null

200

B

6

null

200

A

4

7

300

B

5

null

400

A

3

5

400

B

2

6

500

A

7

null

make_col next_value:window(lead(value, 1), group_by(key), frame_exact(ahead:100ns))

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

timestamp

key

value

next_value

100

A

5

4

150

B

6

null

180

A

4

null

300

B

5

null

400

A

3

null