lag ​

Returns the value from a row at a specified offset before the current row in its ordered partition.

lag uses the whole partition, regardless of a ROWS, RANGE or GROUPS frame. To look in the opposite direction, use lead.

Syntax

lag(value [, offset [, default]])
    OVER ([PARTITION BY expression, ...] ORDER BY expression, ...)

Arguments

• value — The expression evaluated at the target row.
• offset — The number of rows to move before the current row. Defaults to 1. It is converted to an integer once for the query; a negative converted offset raises a SQL error. An offset of 0 selects the current row.
• default — The value returned when the target is outside the partition. Defaults to NULL. A supplied expression is evaluated against the current row.

Returned value The target row's value, preserving its native type, or the fallback when no target row exists. A NULL at an existing target is returned as NULL; it does not select the fallback.

Examples Comparing daily sales

CREATE TABLE sales (day INTEGER, amount INTEGER);
INSERT INTO sales VALUES (1, 120), (2, 150), (3, 90);

SELECT day, amount,
       lag(amount) OVER (ORDER BY day) AS adjacent_amount
FROM sales
ORDER BY day
FORMAT csv;

1,120,\N
2,150,120
3,90,150

Providing a fallback

Use the current amount when there is no adjacent row:

SELECT day,
       lag(amount, 1, amount) OVER (ORDER BY day) AS adjacent_amount
FROM sales
ORDER BY day
FORMAT csv;

1,120
2,120
3,150

See window functions for partitioning, named windows and output ordering.