LAG() OVER ...

All functions > WINDOW FUNCTION > LAG() OVER ...

Returns the value from the row that lags (precedes) the current row by a specified offset within the result set partition.

Syntax

LAG(expr [, offset [, default_value]]) [WITHIN (WHERE condition)] OVER ([PARTITION BY expr [, ...]] [ORDER BY sort_item [, ...]] [ROWS|RANGE|GROUPS frame])

LAG(expr [, offset [, default_value]]) [WITHIN (WHERE condition)] OVER ([PARTITION BY expr [, ...]] [ORDER BY sort_item [, ...]] [ROWS|RANGE|GROUPS frame])

Notes

• Accesses data from a previous row in the same result set
• First expression is the value to return
• Second expression (optional) is the offset (number of rows backward, default 1)
• Third expression (optional) is the default value if offset exceeds partition bounds
• Requires ORDER BY inside OVER so the previous row is well-defined
• Useful for comparing current row with previous rows or calculating deltas

See also

• OVER clause

Examples

SELECT
    -- Previous row value along id order (v permuted so the result is not a sorted copy of v)
    f1 := ZIP(ARRAY(1, 2, 3) AS id, ARRAY(30, 10, 20) AS v).TRANSFORM(
        SELECT LAG(v, 1) OVER (ORDER BY id ASC)
    ).UNWRAP(),
    -- Previous matching row value; rows outside WITHIN return NULL
    f2 := ZIP(
        ARRAY(1, 2, 3, 4) AS id,
        ARRAY(10, 20, 30, 40) AS v,
        ARRAY(TRUE, FALSE, TRUE, TRUE) AS keep
    ).TRANSFORM(SELECT LAG(v) WITHIN (WHERE keep) OVER (ORDER BY id ASC)).UNWRAP()
;