Selecting the best score per player

问题:

I have a table called Scores which contains columns: id, player_id, value1, value2, value3 and date.

The table has next following content:

+------+-----------+--------+--------+--------+------------+
|  id  | player_id | value1 | value2 | value3 |    date    |
+------+-----------+--------+--------+--------+------------+
|   1  |     1     |   10   |    0   |   0    | 2012-08-02 |
+------+-----------+--------+--------+--------+------------+
|   2  |     2     |   15   |    1   |   0    | 2012-08-03 |
+------+-----------+--------+--------+--------+------------+
|   3  |     3     |    9   |    0   |   0    | 2012-08-04 |
+------+-----------+--------+--------+--------+------------+
|   4  |     1     |   11   |    0   |   0    | 2012-08-05 |
+------+-----------+--------+--------+--------+------------+
|   5  |     2     |   16   |    2   |   0    | 2012-08-06 |
+------+-----------+--------+--------+--------+------------+
|   6  |     2     |   15   |    0   |   0    | 2012-08-07 |
+------+-----------+--------+--------+--------+------------+

I am trying to get a query which returns the best highscore of each player ordered by the value in "value1, value2, value3". Value1 is the field with more importance, value2 medium importance and value3 minor importance, example:

value1 = 15                              value1 = 15
value2 = 1       is greater than ->      value2 = 0
value3 = 0                               value3 = 1

The expected result from the query which I need is:

+------+-----------+--------+--------+--------+------------+
|  id  | player_id | value1 | value2 | value3 |    date    |
+------+-----------+--------+--------+--------+------------+
|   5  |     2     |   16   |    2   |   0    | 2012-08-06 |
+------+-----------+--------+--------+--------+------------+
|   4  |     1     |   11   |    0   |   0    | 2012-08-05 |
+------+-----------+--------+--------+--------+------------+
|   3  |     3     |    9   |    0   |   0    | 2012-08-04 |
+------+-----------+--------+--------+--------+------------+

I'm trying with MAX, DISTINCT, GROUP BY and sub-queries but I don't get the correct result. Basically it is the next query but picking the first row of each "group":

SELECT id, player_id, value1, value2, value3
   FROM scores
   ORDER BY value1 DESC, value2 DESC, value3 DESC

------EDIT 1-------

eggyal's answer works fine but, maybe, the performance is not too good. I need to benchmark his solution against large database to check response times.

I have had an idea (and possible solution). The solution consists adding new boolean column which says if that score is the best score of that player or not. This way I need to check if the new score is better than the best old score of that player when I'm adding new score into DB, if it is I need to mark the flag as false in the old best score and as true in the new score. This gives me a way to retrieve the best score of each player directly (simple query like SELECT ... FROM .... ORDER BY).

------EDIT 2-------

weicap's answer is the fastest solution. I don't know why but his query is twice more faster than eggyal's query.

------EDIT 3------- I was wrong, weicap's query is more faster if the query was cached previously, if it wasn't the query takes ten or more seconds. In change, weicap's answer always takes 300-400ms against 80.000 rows.

回答1:

you can try this

SELECT player_id,
  (SELECT value1
   FROM Scores b where a.player_id=b.player_id  ORDER BY value1 DESC, value2 DESC, value3 DESC limit 1) as value1,
  (SELECT value2
   FROM Scores b where a.player_id=b.player_id  ORDER BY value1 DESC, value2 DESC, value3 DESC limit 1) as value2,
  (SELECT value3
   FROM Scores b where a.player_id=b.player_id  ORDER BY value1 DESC, value2 DESC, value3 DESC limit 1) as value3

FROM Scores a GROUP BY player_id order by value1 DESC, value2 DESC, value3 DESC

or something like

SELECT * FROM Scores a 

where id =(SELECT id
   FROM Scores b where a.player_id=b.player_id  ORDER BY value1 DESC, value2 DESC, value3 DESC limit 1)

GROUP BY player_id order by value1 DESC, value2 DESC, value3 DESC

回答2:

For each value, you can obtain the groupwise maximum:

SELECT * FROM Scores NATURAL JOIN (
  SELECT player_id, value1, value2, MAX(value3) value3 FROM Scores NATURAL JOIN (
  SELECT player_id, value1, MAX(value2) value2         FROM Scores NATURAL JOIN (
  SELECT player_id, MAX(value1) value1                 FROM Scores
    GROUP BY player_id) t
    GROUP BY player_id) t
    GROUP BY player_id) t
ORDER BY value1 DESC, value2 DESC, value3 DESC

See it on sqlfiddle.

回答3:

Try adding an index on (player_id, value1, value2, value3) and then this query:

SELECT
    s.*
FROM 
        Scores AS s
    JOIN
        ( SELECT DISTINCT
              player_id
          FROM 
              Scores
        ) AS p
      ON s.id =
         ( SELECT
               id
           FROM 
               Scores AS b
           WHERE
               b.player_id = p.player_id
           ORDER BY
               value1 DESC, value2 DESC, value3 DESC
           LIMIT 1
         ) 
ORDER BY
    s.value1 DESC, s.value2 DESC, s.value3 DESC ; 

回答4:

Your actual scores are: 1000, 1510, 900, etc... You got the idea? It's like decimal numbers where position of a single digit matter. You can convert your 3 values to a single one to group by it and you can estimate it either on-the-fly (right in query) or you can pre-calculate it (before you write it to the table).

回答5:

What language are you using (other than SQL)? It would be easiest if the SQL could sort it, but I don't think that this is possible. If you are using PHP, then you could just place it in a for loop, add an index to the array such as 'bestScore' and then check each score individually like so:

//Extract Data Here
for($outI=0;$outI<count($scores);$outI++){
    $scores[$outI]['bestScore'] = 0;
    for($innI=0;$innI<=3;$innI++){
        if ($scores[$outI]['value' . $innI+1] > $scores[$outI]['bestScore'])
            $scores[$outI]['bestScore'] = $scores[$outI]['value' . $innI+1];
    }
    echo 'The best score for ' . $scores[$outI]['player_id'] . ' was ' . $scores[$outI]['bestScore'] . '.<br />';
}

If it works as expected, it should list the best scores for each player.

回答6:

Assuming your maximum sore can be 16 try this,

Select Scores.* from Scores, (SELECT player_id,max(17*17*value1+17*value2+value3) 
as max_score  FROM Scores group by player_id)t where 
(17*17*value1+17*value2+value3) = t.max_score and Scores.player_id=t.player_id

回答7:

SELECT * FROM (
SELECT id, player_id, value1, value2, value3, `date`
   FROM scores
   ORDER BY value1 DESC, value2 DESC, value3 DESC
) x 
GROUP BY player_id
order by value1 DESC, value2 DESC, value3 DESC