NTH_VALUE [Analytic]
Returns the value evaluated at the row that is the nth row of the window (counting from 1). If the specified row does not exist, NTH_VALUE
returns NULL
.
Behavior Type
Syntax
NTH_VALUE ( expression, row‑number [ IGNORE NULLS ] ) OVER ( ... [ window-frame-clause ] ... [ Window Order Cause ] ... [ window-frame-clause ] )
Parameters
expression |
Expression to evaluate. The expression can be a constant, column name, nonanalytic function, function expression, or expressions that include any of these. |
row‑number | Specifies the row to evaluate, where row‑number evaluates to an integer ≥ 1. |
IGNORE NULLS
|
Specifies to return the first non- |
OVER()
|
Example
In the following example, for each tuple (current row) in table t1, the window frame clause defines the window as follows:
ORDER BY b ROWS BETWEEN 3 PRECEDING AND CURRENT ROW
For each window, n for nth value is a+1
. a
is the value of column a
in the tuple.
NTH_VALUE
returns the result of the expression b+1
, where b
is the value of column b
in the nth row, which is the a+1
row within the window.
=> SELECT * FROM t1 ORDER BY a; a | b ---+---- 1 | 10 2 | 20 2 | 21 3 | 30 4 | 40 5 | 50 6 | 60 (7 rows)
=> SELECT NTH_VALUE(b+1, a+1) OVER (ORDER BY b ROWS BETWEEN 3 PRECEDING AND CURRENT ROW) FROM t1; ?column? ---------- 22 31 (7 rows)