3

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.

2

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:

CREATE TABLE blah (
    id INT
  , value INT
);

INSERT blah (id, value)
VALUES 
    (1, 3)
  , (2, 5)
  , (3, 2)
  , (4, 5)
;

And here's the query you need:

-- find the min and max ranks for later use
SELECT
    @min_rank := 1 AS min_rank
  , @max_rank := (SELECT COUNT(DISTINCT value) FROM blah) AS max_rank
;

-- use the calculated min and max ranks to calculate score
SELECT
    id
  , value
  , rank
  , 10.0 * (@max_rank - rank) / (@max_rank - @min_rank) AS score
FROM (
  SELECT
      id
    , value
    , @curr_rank := IF(@prev_rank = value, @curr_rank, @curr_rank + 1) AS rank
    , @prev_rank := value
  FROM blah, (SELECT @curr_rank := 0) r, (SELECT @prev_rank := NULL) p
  ORDER BY value DESC
) ranked_blah;

The output I get for the second query on SQL Fiddle is as follows:

| ID | VALUE | RANK | SCORE |
-----------------------------
|  2 |     5 |    1 |    10 |
|  4 |     5 |    1 |    10 |
|  1 |     3 |    2 |     5 |
|  3 |     2 |    3 |     0 |

Note that this approach will work best if you have an index on value. This will let you find @max_rank quickly using the COUNT technique @ypercube described.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.