MySQL ranking function implementation

Time:2021-10-22

Source of this articleMySQL ranking function implementation
Reprint, please indicate the source

Now there is a need to rank all students’ scores and list their ranking. Just seeing this demand, I was a little confused and had no ideas at all. Why wouldn’t I do it if it was more difficult? Go online to query information, list all the implementations, and learn all of them.

Database preparation

Create a score table s_ score

CREATE TABLE `s_score`  (
  `id` int NOT NULL AUTO_INCREMENT,
  `score` int NOT NULL DEFAULT 0,
  `name` varchar(20) CHARACTER SET utf8mb4 NULL,
  PRIMARY KEY (`id`)
);

insert data

INSERT INTO `s_score` (`name`, `score`) VALUES
('Zhang San', 80),
('Xiao Ming', 90),
('little red', 60),
('Li Si', 70),
('Zhao Wu', 80),
('Liang Chen', 87),
('little green', 69),
('William', 69),
('David', 91),
('Wang Wu', 96),
('Zhao Liu', 96),
('little five', 80),
('Little Dragon', 88);

Common implementation

The rank ranking function rank is launched in MySQL 8.0, which fully supports this requirement, but this feature must be supported by MySQL 8.0 or above. What is the implementation method for versions below 8.0? Use user variables to record the ranking.
User variables: in“@“Start as”@var_name“, to distinguish between user variables and column names. It can be any random, compound scalar expression as long as no column is specified. Here is a small example to show how to use user variables

select @a:=1 a,@b:[email protected]+1 b

results of enforcement

a b
1 2

: = means assignment, which is a little different from programming language assignment. Let’s start to show the effect of using simple SQL to implement rank ranking function

The user variable simply realizes the rank display

SELECT name,score, @rank:[email protected]+1 `rank` from s_score s,(select @rank:=0) q ORDER BY score desc
name score rank
Zhao Liu 96 1
Wang Wu 96 2
David 91 3
Xiao Ming 90 4
Bruce Lee 88 5
Liang Chen 87 6
Small five 80 7
Zhang San 80 8
Zhao Wu 80 9
Li Si 70 10
William 69 11
Little green 69 12
Xiao Hong 60 13

And ranking display

Now there is another problem. If the scores are the same, the ranking should be the same. We use a temp variable to record the previous score value to judge whether the previous score is equal to the current score. If it is equal, it will directly return to the previous ranking, otherwise the ranking will be + 1.

select name,score,case when @temp_score=score then @rank when @temp_score:=score then @rank:[email protected]+1 END 
   `rank` from s_score s,(select @rank:=0,@temp_score:=NULL) q ORDER BY score desc
name score rank
Zhao Liu 96 1
Wang Wu 96 1
David 91 2
Xiao Ming 90 3
Bruce Lee 88 4
Liang Chen 87 5
Small five 80 6
Zhang San 80 6
Zhao Wu 80 6
Li Si 70 7
William 69 8
Little green 69 8
Xiao Hong 60 9

And rank times to skip

If there is a parallel ranking, the next ranking will be skipped automatically. For example, if there are two parallel first, 91 should become the third, and the ranking corresponds to the number of people.

SELECT name,score,rank from (
SELECT name ,score,@rank :=IF( @temp_score = score, @rank, 
@rank_incr ) `rank`,@rank_incr := @rank_incr + 1,
    @temp_score := score FROM s_score s,([email protected] := 
    0,@temp_rank := NULL,@rank_incr := 1 ) q ORDER BY score 
    DESC) a
name score rank
Zhao Liu 96 1
Wang Wu 96 1
David 91 3
Xiao Ming 90 4
Bruce Lee 88 5
Liang Chen 87 6
Small five 80 7
Zhang San 80 7
Zhao Wu 80 7
Li Si 70 10
William 69 11
Little green 69 11
Xiao Hong 60 13

Using SQL window functions

The basic syntax of the window function is as follows:

Select sort function / aggregate function over (< partition by… > partition field order by sort field)

Note that there is a space after the over. This grammar is a little painful. I tried it more than a dozen times to write it successfully.
According to Wikipedia:Window functionAllows access to data in the record before and after the current record. Window function definition IframeOr a columnwindowWhere the current row has a given length around it and the calculation is performed across the dataset in the window. It can be understood that a window is a data set and a function is a method of calculating data.

Parton by is optional. If partition by is not used, the whole table is taken as a set. Finally, the sorting function is used to get the sorting number of each record according to the sorting sequence.
Sorting functions mainly include rank () and deny_ rank、row_ Number, their main differences:

  • Rank (): sort the same field. If the fields are the same, they will be ranked side by side, and there will be a ranking gap.
  • dense_ Rank (): sort the same field. If the fields are the same, they will be ranked side by side and ranked continuously
  • row_ Number (): the ranking of the same field is related. Even if the same field appears, it will not be ranked in parallel
    select name,score, RANK() over (ORDER BY score DESC) `rank`,ROW_NUMBER() over (order by score DESC) `row`,
    DENSE_RANK()over (ORDER BY score DESC) `dense` from s_score
    
name score rank row dense
Zhao Liu 96 1 1 1
Wang Wu 96 1 2 1
David 91 3 3 2
Xiao Ming 90 4 4 3
Bruce Lee 88 5 5 4
Liang Chen 87 6 6 5
Zhao Wu 80 7 7 6
Small five 80 7 8 6
Zhang San 80 7 9 6
Li Si 70 10 10 7
Little green 69 11 11 8
William 69 11 12 8
Xiao Hong 60 13 13 9

The above is the implementation of sorting ranking. There are other implementation methods. Please add them in the comments.

Reference documents

https://cloud.tencent.com/developer/article/1562954
https://www.jianshu.com/p/bb1b72a1623e