LAG

LAG is an analytic function that returns the evaluation of the expression (such as the name of a column) for the row that is N rows before from the current row in a given window. Both offset and default are evaluated with respect to the current row. If there is no such row, it instead returns a specified default expression. LAG returns a value of the same type as the expression.

Syntax

LAG(expr [ , N [ , defaultExpr]]) [ IGNORE NULLS | RESPECT NULLS ] over \<window-specification\>

where

  • expr is an expression which can be evaluated on a row
  • N is a BIGINT
  • defaultExpr is an expression which can be evaluated on a row and which can be CAST to the same type as expr.

See the topic WINDOW clause of the SELECT statement for more details on defining windows.

Notes:

N defaults to 1. (If defaultExpr is not specified LAG is equivalent to NTH_VALUE(expr, N + 1) FROM LAST.)

defaultExpr defaults to NULL. If specified, defaultExpr is returned instead of NULL if N falls outside of the window.

When calculating offset rows, rows for which expr evaluates to null are eliminated if IGNORE NULLS is specified.

If null behavior is not specified, defaults to RESPECT NULLS.