Oracle analysis function-rank ()

Create Table B
(
Store varchar2 (100 ),
Sales Volume varchar2 (100 ),
Price: varchar2 (100 ),
Date
)
Tablespace users

/

Insert into B (shop, sales quantity, price, date)
Values ('A', '3', '123', to_date ('01-01-2011 ', 'dd-mm-yyyy '));
Insert into B (shop, sales quantity, price, date)
Values ('A', '4', '000000', to_date ('02-02-2011 ', 'dd-mm-yyyy '));
Insert into B (shop, sales quantity, price, date)
Values ('A', '4', '000000', to_date ('03-03-2011 ', 'dd-mm-yyyy '));
Insert into B (shop, sales quantity, price, date)
Values ('B', '10', '000000', to_date ('05-05-2011 ', 'dd-mm-yyyy '));
Insert into B (shop, sales quantity, price, date)
Values ('C', '5', '123', to_date ('06-06-2011 ', 'dd-mm-yyyy '));
Commit;

 

Now we need to group by store and rank by sales volume:

 

1. Select B. *, row_number () over (partition by shop order by to_number (sales quantity) DESC) Rn from B

Row_number (): returns a number for each group record.

 

2. Select B. *, rank () over (partition by shop order by to_number (sales quantity) DESC) Rn from B

Rank (): returns the ranking of data items in a group. Feature: When the rankings are equal, there will be blank spaces in the rankings.

 

3. Select B. *, dense_rank () over (partition by shop order by to_number (sales quantity) DESC) Rn from B

Dense_rank (): different from rank, it does not leave a blank position in the ranking when the ranking is equal.