Shift / List Layer

Shift values in variable-length lists by n positions, filling with nulls. Similar to SQL's LAG/LEAD or pandas shift(). Positive shift moves values forward (right), negative shift moves values backward (left). Preserves original list length.

Example transformation with n=1:

lists	shifted
[1, 2, 3, 4]	[null, 1, 2, 3]
[x, y]	[null, x]
[a]	[null]
[]	[]

With n=-1:

lists	shifted
[1, 2, 3, 4]	[2, 3, 4, null]
[x, y]	[y, null]
[a]	[null]
[]	[]

Common applications:

Computing period-over-period changes
Analyzing sequential patterns in time series
Creating sliding window comparisons
Calculating rolling differences
Detecting trend changes
Processing event sequences
Analyzing lead/lag relationships

Note: Lists can have any length. Empty lists remain empty. Shifted elements that move beyond list bounds are dropped. Vacated positions are filled with nulls. Particularly useful for time-based analysis and sequential comparisons.

Table

Select

column

The variable-length list column to shift. Examples:

Time series: [100, 101, 102, 103] → [null, 100, 101, 102]
Event sequence: [login, view, purchase] → [null, login, view]
Measurements: [1.1, 1.2] → [null, 1.1]
Status changes: [active] → [null] Lists can have different lengths. Supports any data type.

ShiftBy

oneof

N

i32

Number of positions (N) to shift list elements:

Positive N: Forward shift (right), fill start with nulls [1, 2, 3, 4] with N=2 → [null, null, 1, 2]
Negative N: Backward shift (left), fill end with nulls [1, 2, 3, 4] with N=-2 → [3, 4, null, null]
Zero: No shift, returns original list Default is 1 (forward shift by one position)

SelectN

column

Column containing shift values. Each row can have a different shift amount. Allows dynamic shifting based on data conditions.

AsColumn

name

Name for the new column. If not provided, the system generates a unique name. If AsColumn matches an existing column, the existing column is replaced. The name should follow valid column naming conventions.