LAG
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 | +------+-----------------------------+