Database Reference
In-Depth Information
Table 11.3
Window Functions
Function
Description
Number of the current row within its partition, counting
from 1.
row_number()
Rank of the current row with gaps; same as
row_number
of its first peer.
rank()
Rank of the current row without gaps; this function
counts peer groups.
dense_rank()
Relative rank of the current row: (rank - 1) / (total rows
- 1).
percent_rank()
Relative rank of the current row: (number of rows
preceding or peer with current row) / (total rows).
cume_dist()
Integer ranging from 1 to the argument value, dividing
the partition as equally as possible.
ntile(num_buckets
integer)
Returns the value evaluated at the row that is offset rows
before the current row within the partition; if there is no
such row, instead return default. Both offset and default
are evaluated with respect to the current row. If omitted,
offset defaults to 1 and default to
null
.
lag(value any [,
offset integer [,
default any ]])
Returns the value evaluated at the row that is offset rows
after the current row within the partition; if there is no
such row, instead return default. Both offset and default
are evaluated with respect to the current row. If omitted,
the offset defaults to 1 and the default to
null
.
lead(value any [,
offset integer [,
default any ]])
Returns the value evaluated at the first row of the
window frame.
first_value(value
any)
Returns the value evaluated at the last row of the
window frame.
last_value(value
any)
Returns the value evaluated at the nth row of the
window frame (counting from 1);
null
if no such row.
nth_value(value
any, nth integer)
