LAG
On this page
Returns an expression evaluated at some offset before the current row.
If there is no such row returns NULL instead.
Syntax
LAG ( expression [, offset]) OVER (
[PARTITION BY (col | expr), ...]
[ORDER BY (col | expr), ...]
)Arguments
-
expression: any expression.
This may be a column name, the result of another function, or a math operation -
offset: A constant integer that specifies the number of rows before the current row to evaluate the expression.
Defaults to 1 if not specified
Return Type
The result of expression
Examples
CREATE TABLE lag_example (a int, b int);INSERT INTO lag_example VALUES(1,2),(2,2),(3,3),(4,3);
SELECT a, LAG(a) OVER(ORDER BY (a)) FROM lag_example;
+------+---------------------------+
| a | LAG(a) OVER(ORDER BY (a)) |
+------+---------------------------+
| 1 | NULL |
| 2 | 1 |
| 3 | 2 |
| 4 | 3 |
+------+---------------------------+SELECT a, LAG(a,2) OVER(ORDER BY (a)) FROM lag_example;
+------+-----------------------------+
| a | LAG(a,2) OVER(ORDER BY (a)) |
+------+-----------------------------+
| 1 | NULL |
| 2 | NULL |
| 3 | 1 |
| 4 | 2 |
+------+-----------------------------+Last modified: February 16, 2023