Oracle's Row_number () over, rank () over and Dense_rank () over three analytic functions (RPM)

Suppose there is a student table student, the student table has a name, a score, a course number, and now I need to sort the students ' grades according to the course.

SELECT * FROM Student

1. Rank over () can be achieved on the student rankings, characterized by the same results as two are tied, as follows 1 2 2 4 5

Select Name,
Course
Rank () Over (partition by course ORDER BY score Desc) as rank
from student;

2. Dense_rank () and rank over () are similar, but the students ' grades will not be vacated by the rank of the tie, as follows 1 2 2 3 4

Select Name,
Course
Dense_rank () Over (partition by course ORDER BY score Desc) as rank
from student;

3. Row_number This function does not need to consider whether or not to tie, that is afraid to query the same value according to the same number will be continuously ranked

Select Name,
Course
Row_number () Over (partition by course ORDER BY score Desc) as rank
from student;

Answering:

1. Partition by is used to partition the result set.

2. What is the difference between partition by and group by?

Partition by simply rank the original data (the number of records is the same)

Group BY is an aggregated statistic of the raw data (the number of records may be less, one for each group)

3. When you use rank over (), the null value is the largest, and if the sort field is null, it may cause null fields to be ranked first, affecting the sorting result.

Can do this: rank over (partition by course ORDER BY score Desc nulls last)

Oracle's Row_number () over, rank () over and Dense_rank () over three analytic functions (RPM)