0 like 0 dislike
7 views
Suppose there is a sign with the players, where each player has the field "score".

And we want to create the overall ranking of players sorted by this field.

The question is, is it possible to get the position of given player in this ranking? I.e. a player with score=12 and when you sort by this field it will list all the players on the 50000-th place.

Can we determine this place the flick of the wrist?

Or there is only one every N time to perform a pass through the sorted table and remember the ratings?

Data volumes are planned 100 thousand users to a million. What actually creates the sorted ratings in such large volumes?
| 7 views

0 like 0 dislike
Suppose there is a table: id, PlayerName, score
\r
there is evidence:
\r
1 player1 12
2 player2 15
3 player3 14
4 player4 14
5 payer5 15
6 player6 12
\r
SELECT COUNT(DISTINCT score) as position FROM table WHERE score >= (SELECT score FROM table WHERE PlayerName='player1' LIMIT 1)
\r
The result will be:
\r
SELECT COUNT(DISTINCT score) FROM table WHERE score >= 12
\r
15
count = 14 = 3 = position
12
by
0 like 0 dislike
UPDATE table SET spore=spore+1/rand(1,10000000000000)
take and silently adds the fluctuation after the decimal point.
If you have spore — determined to 0.001 for example — define the fluctuation as 0.001\\rand
\r
As an option — fixiate this fluctuation to a specific user when creating that user.
\r
Well, it's the details.
The main thing is that you can always run
\r
SELECT COUNT(*) FROM table WHERE spore<?<br/>\r
And get an accurate value, even if you are looking for spore=12 and 12 a few hundred thousand
by
0 like 0 dislike
On the fiftieth position is not so important — what kind of account the position of the gamer. Therefore, update often "fair" rating makes sense for top50, where there is a cabin between the most ardent fans of the game in the first place.
further movement in the rating will not be sharp and will be a multiple of time of one game (the update interval of the rating of one player) and will not bounce on the average number of points in a single session. Thus, the ratings can be updated "on the eye" and to beat them in groups.
That's if the players are millions.
But at the same time online will be high hundreds, so I would suffer the optimization of this table.
And when at the same time to play there are thousands, will make refactoring code and databases, and maybe change the server.
by
0 like 0 dislike
Offtopic: if users planned to do so much, it is best to use splitting in divisions/leagues, than to show a "clean" place. Few people pleased to see that he 239676.
by
0 like 0 dislike
to partitionierung table on the field score
then COUNT(*) FROM table WHERE score<? будет выполняться с использованием primary key на каждой из партиций, что должно работать очень быстро<br/>)
by
0 like 0 dislike
You can create a variable.
\r
\r
`SELECT *, (@position:=@position+1) FROM `users`, (SELECT @position:=0) `a` WHERE 1 `
by
0 like 0 dislike
But better "Or a variant there is only one every N time to perform a pass through the sorted table and remember the ratings?"
by

0 like 0 dislike