Shift / Array Layer

Shift values in fixed-length arrays by n positions, filling with nulls. Similar to SQL's LAG/LEAD for arrays or Python's pandas shift(). Positive shift moves values forward (to the right), negative shift moves values backward (to the left).

Example transformation with n=1 (arrays of length 3):

arrays	shifted
[1, 2, 3]	[null, 1, 2]
[x, y, z]	[null, x, y]
[4, 5, 6]	[null, 4, 5]

With n=-1 (arrays of length 3):

arrays	shifted
[1, 2, 3]	[2, 3, null]
[x, y, z]	[y, z, null]
[4, 5, 6]	[5, 6, null]

Common applications:

Lagging time series data
Creating offset comparisons
Analyzing sequential changes
Computing previous/next values
Time-based calculations

Note: Arrays must have the same fixed length across all rows. Shifted elements that move beyond array bounds are dropped. Vacated positions are filled with null values.

Table

Select

column

The fixed-length array column to shift. Supports arrays of any type:

Numeric arrays: [1, 2, 3] → [null, 1, 2]
String arrays: [x, y, z] → [null, x, y]
Boolean arrays: [true, true, false] → [null, true, true] All arrays must have the same length

ShiftBy

oneof

N

i32

Number of positions to shift array elements:

Positive n: Shift forward (right), fill start with nulls [1, 2, 3] with n=2 → [null, null, 1]
Negative n: Shift backward (left), fill end with nulls [1, 2, 3] with n=-2 → [2, 3, null]
Zero n: No shift, returns original array Default is 1 (forward shift by one position)

SelectN

column

Select a column containing shift by values.

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.