I am using MYSQL to create a rating system to implement my database. What I want to do is to rate each attribute by its percentage. Here is the example database:
ID, value
1, 3
2, 5
3, 2
4, 5
The output I want is:
ID, value, rank, score
1, 3, 2, 5
2, 5, 1, 10
3, 2, 3, 0
4, 5, 1, 10
score's value will based on the rank so it becomes such as
10*(MAX(rank)-(rank))/(MAX(rank)-MIN(rank))
I have done the rank query but stuck with transforming it into scores. Here is the query I got so far:
SELECT `ID`, `value`, FIND_IN_SET( `value`, (
SELECT GROUP_CONCAT(DISTINCT `value`
ORDER BY `value` DESC)
FROM table)
) AS rank
FROM table;
Thank you all guys 🙂
Another question:
| ID | VALUE1 | RANK1 | VALUE2 | RANK2 |
----------------------------------------
| 2 | 5 | 1 | 20 | 2|
| 4 | 5 | 1 | 30 | 1|
| 1 | 3 | 2 | 5 | 4|
| 3 | 2 | 3 | 8 | 3|
How to generate multiple ranking like the table? I have tried
SELECT
@min_rank := 1 AS min_rank
, @max_rank1 := (SELECT COUNT(DISTINCT value1) FROM table) AS max_rank1
, @max_rank2 := (SELECT COUNT(DISTINCT value2) FROM table) AS max_rank2
;
SELECT
ID
, R1
, TRUNCATE(5.0+5.0 * (@max_rank1 - R1) / (@max_rank1 - @min_rank), 2) AS Score1
, R2
, TRUNCATE(5.0+5.0 * (@max_rank2 - R2) / (@max_rank2 - @min_rank), 2) AS Score2
FROM (
SELECT
ID
, value1
, FIND_IN_SET( `value1`, (SELECT GROUP_CONCAT(DISTINCT `value1` ORDER BY `value1` DESC) FROM table)) AS R1
, value2
, FIND_IN_SET( `value2`, (SELECT GROUP_CONCAT(DISTINCT `value2` ORDER BY `value2` DESC) FROM table)) AS R2
FROM table
) ranked_table;
It works fine with ranking below 170. My database has approximate 200+ ranking for some values and ranks larger then 170 will be seen as 0 when it returns. In that case, some scores will be miscalculated.
Best Answer
Using the techniques described here to rank items in MySQL, you can calculate the scores you need using a derived table and some hackery with user variables.
Here's your table with the sample data you provided:
And here's the query you need:
The output I get for the second query on SQL Fiddle is as follows:
Note that this approach will work best if you have an index on
value
. This will let you find@max_rank
quickly using theCOUNT
technique @ypercube described.