Lead/Lag analytic functions
Lead/Lag functions help
to get data from the next/previous row in a query.
Syntax for these
functions is:
Lead (expr [, offset,
default-val ] ) over (order by expr)
Lag (expr [, offset,
default-val ] ) over (order by expr)
Expr can be anything
except another analytic function i.e. these functions cannot be
nested.
Offset is the number of
rows looked ahead or behind. Default is 1
Default-val is returned
from these functions, if there is no row at the given offset from
current row. Default is null.
Example:

The
above functions can come very handy when we want to have a difference
or sum or average etc between two consecutive rows in a query.
|