LAG

<< Click to Display Table of Contents >>

Navigation:  Streaming SQL Reference Guide > Basic Building Blocks > Functions > Built-in Functions >

LAG

Previous pageReturn to chapter overviewNext page

5_2_indicator Version 5.2 Feature

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

 

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.