I have a table of all games: game id, winners and loosers. Winners and loosers are varchars which are comma-separated lists of ids, for example:

| id | winners | loosers |
|  1 | 10,24,3 |34,158,71|

I'd like to create a query to select a top of users (top-30 for ex.) ordered by rating which is amount of wins divided by amount of looses. But the table with all users has 6k rows and iterating over it to make a top takes 1 minute which is too long for me. Any way to make it simpler?

I got an idea to iterate over the games table, put user_id, wins and looses to a temporary table, and then select the result, but I'm not sure how to make this right.

Desired output format:

| user_id | rating |

and only using mysql query.

CodePudding user response：

Any way to make it simpler?

Glad you asked. Yes, there is a great way to make this simpler:

Stop storing sets of discrete values as comma-separated strings.

SQL works best if you store one discrete value per column on a given row, if you later need to do calculations on the individual values.

Store two tables: The first table has one row for each game. The second table has one row per user per game, and an attribute that says whether they won or lost.

Now you can query it more simply to get the percentage of wins to losses per user:

SELECT user_id, COUNT(NULLIF(won, 0)) / COUNT(*) AS percent_wins
FROM MyTable
GROUP BY user_id;