In depth discussion: usage of row_number of over of analysis function in oracle

row_number () over (partition by col1 order by col2) indicates grouping according to col1 and sorting according to col2 within the grouping, and the value calculated by this function indicates the sequence number after sorting within each group (only one consecutive within the group).
The difference with rownum is that when using rownum to sort, it first adds fake rownum to the result set and then sorts it, while this function sorts it first and then calculates the row number after including sorting clause.
row_number () is similar to rownum and is one point more powerful (you can sort from one within each group).
rank () is a jump sort, and when there are two 2nd places, the next place is 4th (again within each group)
dense_rank () is also sequential, with two 2nd places still followed by 3rd place. In contrast, row_number has no duplicate values.
The oracle parse function row_number () returns an integer value ( > =1);
Syntax format:
1.row_number() over (order by col_1[,col_2 ...])
Function: Sort according to col_1 [, col_2...], return the sorted result set,
This usage is a bit like rownum, which returns one different value for every one row:

select rownum,ename,job,    
    row_number() over (order by rownum) row_number    
from emp;    
    ROWNUM ENAME      JOB       ROW_NUMBER    
---------- ---------- --------- ----------    
         1 SMITH      CLERK              1    
         2 ALLEN      SALESMAN           2    
         3 WARD       SALESMAN           3    
         4 JONES      MANAGER            4    
         5 MARTIN     SALESMAN           5    
         6 BLAKE      MANAGER            6    
         7 CLARK      MANAGER            7    
         8 SCOTT      ANALYST            8    
         9 KING       PRESIDENT          9    
        10 TURNER     SALESMAN          10    
        11 ADAMS      CLERK             11    
        12 JAMES      CLERK             12    
        13 FORD       ANALYST           13    
        14 MILLER     CLERK             14  

Without the partition by clause, the result set will be sorted by the columns specified by order by;

with row_number_test as(    
     select 22 a,'twenty two' b from dual union all    
     select 1,'one' from dual union all    
     select 13,'thirteen' from dual union all    
     select 5,'five' from dual union all    
     select 4,'four' from dual)    
select a,b,    
       row_number() over (order by b)    
from row_number_test    
order by a;  

As we would expect, row_number () returns the results sorted by the b column,
Then sort according to a to get the following result:

A B          ROW_NUMBER()OVER(ORDERBYB)    
-- ---------- --------------------------    
1 one                                 3    
4 four                                2    
5 five                                1    
13 thirteen                            4    
22 twenty two                          5  

2.row_number() over (partition by col_n[,col_m ...] order by col_1[,col_2 ...])
Function: First, group according to col_n [, col_m...,
Then, each packet is sorted according to col_1 [, col_2...] (ascending order).
Finally, the ordered result set is returned:

with row_number_test as(    
     select 22 a,'twenty two' b,'*' c from dual union all    
     select 1,'one','+' from dual union all    
     select 13,'thirteen','*' from dual union all    
     select 5,'five','+' from dual union all    
     select 4,'four','+' from dual)    
select a,b,    
       row_number() over (partition by c order by b) row_number    
from row_number_test    
order by a;  

In this example, we first group according to the c column and divide it into two groups ('*' group, '+' group).
Then sort by the b column of each group (by the ascii code of the first letter of the string),
Finally, sort according to the a column, and get the following result set:

A B          ROW_NUMBER    
-- ---------- ----------    
1 one                 3    
4 four                2    
5 five                1    
13 thirteen            1    
22 twenty two