7.24 Window functions
Using a window function, you can specify a range of rows derived from the results of a table expression, and then determine aggregated strings for the rows in that range.
The window functions are shown in the following table:
|
No. |
Window function |
Description |
|---|---|---|
|
1 |
RANK |
Determines the ranking of the rows in an ordered set of rows. The ranking values might not be contiguous integer values. |
|
2 |
DENSE_RANK |
Determines the ranking of the rows in an ordered set of rows. The ranking values will be contiguous integer values. |
|
3 |
CUME_DIST |
Determines the relative position of a row in an ordered set of rows. The CUME_DIST of row R is the number of rows that are in front of R in the window (partition) or that have the same sort key value as R, divided by the number of rows in the window (partition) of R. |
|
4 |
ROW_NUMBER |
Assigns a unique number to each row in an ordered set of rows. |
|
5 |
LAG |
Determines the value of the target data evaluated for the rows in the ordered set of rows that precede the current row by the number of rows specified in the offset. If a line that precedes the current line by the number of lines specified in the offset is outside the set, the value specified in the default value is returned. Note that if 0 is specified for the offset, the value of the target data evaluated for the current row is returned. |
|
6 |
LEAD |
Determines the value of the target data evaluated for the rows in the ordered set of rows behind the current row by the number of rows specified in the offset. If a line that succeeds the current line by the number of lines specified in the offset is outside the set, the value specified in the default value is returned. Note that if 0 is specified for the offset, the value of the target data evaluated for the current row is returned. |
|
7 |
Set functions |
Determines the value of the set function for the window frame. |