Processing involving analytic functions involves three phases:
1. computation of products, selections, grouping, and having clauses;
2. application of analytic functions to the resulting sets of rows;
3. processing of the final order by clauses.
The results of the first phase can be partitioned. Partitions are created after
the groups defined by the group by clauses and, thus, may use any aggregate
functions such as sum, count, etc.
For each row in a partition, a sliding window of data may be defined. The
window determines a sequence of rows that is used to perform calculations for
the current row. Window sizes can be specified as numbers of rows or can be
determined by intervals in a domain. Either end of a window or both ends can
move, depending on the definition of the window.
Each computation involving an analytic function is based on a current row.
This row serves as reference for the ends of the window.
5.18.1 Ranking Functions
SQL Plus contains the ranking functions rank() and dense_rank() that can
be use to rank tuples in an order determined by certain attributes or expres-
sions. Both functions generate ranks in either ascending or descending order,
but dense_rank() does not leave gaps in rank numbers when a tie occurs. The
default order is, as usual, ascending order.
Example 5.18.1 To rank the grade records based on the grade obtained in
any course we may write:
select stno, grade,
rank() over (order by grade)
from grades;