Rank () over, row_number () over, rank_dense () in SQL statements ()
Summary:
I created a table with the following data,
SQL> select * from test;
A1 A2
--------------------
1 3
2 4
3 2
3 5
4 2
Then rank () over,
SQL> select A1, A2, rank () over (order by a1) rank from test;
A1 A2 rank
------------------------------
1 3 1
2 4 2
3 2 3
3 5 3
4 2 5
SQL> select A1, A2, rank () over (order by A2) rank from test;
A1 A2 rank
------------------------------
3 2 1
4 2 1
1 3 3
2 4 4
3 5 5
It turns out that this is the stuff used to look at rankings.
Next let's take a look at row_number () over,
SQL> select A1, A2, row_number () over (order by a1) rank from test;
A1 A2 rank
------------------------------
1 3 1
2 4 2
3 2 3
3 5 4
4 2 5
SQL> select A1, A2, row_number () over (order by A2) rank from test;
A1 A2 rank
------------------------------
3 2 1
4 2 2
1 3 3
2 4 4
3 5 5
The difference is marked in yellow, which is easy to see ~~~
Let's look at dense_rank () over
SQL> select A1, A2, dense_rank () over (order by a1) rank from test;
A1 A2 rank
------------------------------
1 3 1
2 4 2
3 2 3
3 5 3
4 2 4
After dense_rank has the same rank, the rank value is the rank value next to the previous one.
It can be understood that, in general sense, the implementation of Top N should adopt row_number () over instead of rank () over, because row_number is to add an Sn mark to the record, rank is equivalent to the rank after sorting, that is, if the sorting columns are the same, their return values are the same, while row_number cannot return the same value.
Let's take a look:
SQL> select A1, A2,
2 rank () over (order by a1) rank,
3 dense_rank () over (order by a1) dense_rank,
4 row_number () over (order by a1) row_number
5 from test;
A1 A2 rank dense_rank row_number
--------------------------------------------------
1 3 1 1 1
2 4 2 2
3 2 3 3
3 5 3 3 4
4 2 5 4 5
Row_number () is similar to rownum and more powerful (it can be sorted from 1 on each group ).
Rank () is the Skip sorting. When there are two second names, the next is the fourth one (also in each group ).
Dense_rank () L is a continuous sorting, with two second names still followed by the third. In contrast, row_number does not have repeated values.
