Detailed Explanation of Oracle Ranking Function of Rank Example

--Known: Two ranking methods (partitioned and unpartitioned): with and without partition

--Two calculation methods (continuous and discontinuous), corresponding functions: dense_rank, rank

Query the original data: student number, name, subject name and grade

select * from t_score

S_ID	S_NAME	SUB_NAME	SCORE
1	张3	语文	80.00
2	李4	数学	80.00
1	张3	数学	0.00
2	李4	语文	50.00
3	张3丰	语文	10.00
3	张3丰	数学
3	张3丰	体育	120.00
4	杨过	JAVA	90.00
5	mike	c++	80.00
3	张3丰	Oracle	0.00
4	杨过	Oracle	77.00
2	李4	Oracle	77.00

Query each student subject for Oracle ranking (simple ranking) select sc.s_id,sc.s_name,sub_name,sc.score, Ranking of rank () over (order by score desc) from t_score sc where sub_name='Oracle'

S_ID	S_NAME	SUB_NAME	SCORE	名次
4	杨过	Oracle	77.00	1
2	李4	Oracle	77.00	1
3	张3丰	Oracle	0.00	3

Comparison: rank () vs. dense_rank (): Discontinuous and continuous (both simple) rankings select sc.s_id,sc.s_name,sub_name,sc.score, Ranking of dense_rank () over (order by score desc) from t_score sc where sub_name='Oracle'

S_ID	S_NAME	SUB_NAME	SCORE	名次
4	杨过	Oracle	77.00	1
2	李4	Oracle	77.00	1
3	张3丰	Oracle	0.00	2

Inquire about the ranking of each student in each subject (regional ranking) select sc.s_id,sc.s_name,sub_name,sc.score, rank() over (partition by sub_name order by score desc) from t_score sc

S_ID	S_NAME	SUB_NAME	SCORE	名次
4	杨过	JAVA	90.00	1
4	杨过	Oracle	77.00	1
2	李4	Oracle	77.00	1
3	张3丰	Oracle	0.00	3
5	mike	c++	80.00	1
3	张3丰	数学		1
2	李4	数学	80.00	2
1	张3	数学	0.00	3
3	张3丰	体育	120.00	1
1	张3	语文	80.00	1
2	李4	语文	50.00	2
3	张3丰	语文	10.00	3

Inquire the top 2 in each subject (ranking by district)

Similar: News table, seek the top 3 news with click-through rate.
Commodity list, seeking the top 10 commodities in sales of various categories.

select * from (
select sc.s_id,sc.s_name,sub_name,sc.score,
dense_rank() over
(partition by sub_name order by score desc)  Ranking 
from t_score sc
) x
where x. Ranking <=2

S_ID	S_NAME	SUB_NAME	SCORE	名次
4	杨过	JAVA	90.00	1
4	杨过	Oracle	77.00	1
2	李4	Oracle	77.00	1
3	张3丰	Oracle	0.00	2
5	mike	c++	80.00	1
3	张3丰	数学		1
2	李4	数学	80.00	2
3	张3丰	体育	120.00	1
1	张3	语文	80.00	1
2	李4	语文	50.00	2

Check the total score of each student select s_id,s_name,sum(score) sum_score from t_score group by s_id,s_name

S_ID	S_NAME	SUM_SCORE
1	张3	80.00
2	李4	207.00
3	张3丰	130.00
4	杨过	167.00
5	mike	80.00

Inquire about the ranking of each student according to the total score select x.*, Ranking of rank () over (order by sum_score desc) from ( select s_id,s_name,sum(score) sum_score from t_score group by s_id,s_name ) x

S_ID	S_NAME	SUM_SCORE	名次
2	李4	207.00	1
4	杨过	167.00	2
3	张3丰	130.00	3
1	张3	80.00	4
5	mike	80.00	4

Syntax: rank () over (order by sort field order) rank () over (partition by Packet Field order by Sort Field Order) 1. Sequence: ascdesc ranking is business-related: Example: Looking for excellent students: Grade: Descending Late Times: Ascending 2. Partition field: According to what field to partition. Question: What is the difference between partitioning and grouping? Partitioning only ranks the original data (the number of records remains unchanged), Grouping is to aggregate statistics of original data (the number of records becomes smaller, and each group returns one). Note: Aggregation.

Script:

create table t_score
(
 autoid number primary key,
 s_id  number(3),
 s_name char(8) not null,
 sub_name varchar2(20),
 score number(10,2)
);
insert into t_score (autoid, s_id, s_name, sub_name, score)
values (8, 1, ' Zhang 3 ', ' Language ', 80);
insert into t_score (autoid, s_id, s_name, sub_name, score)
values (9, 2, ' Li 4 ', ' Mathematics ', 80);
insert into t_score (autoid, s_id, s_name, sub_name, score)
values (10, 1, ' Zhang 3 ', ' Mathematics ', 0);
insert into t_score (autoid, s_id, s_name, sub_name, score)
values (11, 2, ' Li 4 ', ' Language ', 50);
insert into t_score (autoid, s_id, s_name, sub_name, score)
values (12, 3, ' Zhang 3 Abundance  ', ' Language ', 10);
insert into t_score (autoid, s_id, s_name, sub_name, score)
values (13, 3, ' Zhang 3 Abundance  ', ' Mathematics ', null);
insert into t_score (autoid, s_id, s_name, sub_name, score)
values (14, 3, ' Zhang 3 Abundance  ', ' Sports ', 120);
insert into t_score (autoid, s_id, s_name, sub_name, score)
values (15, 4, ' Yang Guo  ', 'java', 90);
insert into t_score (autoid, s_id, s_name, sub_name, score)
values (16, 5, 'mike ', 'c++', 80);
insert into t_score (autoid, s_id, s_name, sub_name, score)
values (3, 3, ' Zhang 3 Abundance  ', 'oracle', 0);
insert into t_score (autoid, s_id, s_name, sub_name, score)
values (4, 4, ' Yang Guo  ', 'oracle', 77);
insert into t_score (autoid, s_id, s_name, sub_name, score)
values (17, 2, ' Li 4 ', 'oracle', 77);
commit;