row_number - rank mysql 8




Déterminer le classement en fonction de plusieurs colonnes dans MySQL (3)

J'ai une table qui a 3 champs, je veux classer la colonne basée sur user_id et game_id.

Voici SQL Fiddle: http://sqlfiddle.com/#!9/883e9d/1

la table déjà j'ai:

 user_id | game_id |   game_detial_sum  |
 --------|---------|--------------------|
 6       | 10      |  1000              |   
 6       | 11      |  260               |
 7       | 10      |  1200              |
 7       | 11      |  500               |
 7       | 12      |  360               |
 7       | 13      |  50                | 

production attendue :

user_id  | game_id |   game_detial_sum  |  user_game_rank  |
 --------|---------|--------------------|------------------|
 6       | 10      |  1000              |   1              |
 6       | 11      |  260               |   2              |
 7       | 10      |  1200              |   1              |
 7       | 11      |  500               |   2              |
 7       | 12      |  360               |   3              |
 7       | 13      |  50                |   4              |

Mes efforts jusqu'à présent:

SET @s := 0; 
SELECT user_id,game_id,game_detail, 
       CASE WHEN user_id = user_id THEN (@s:[email protected]s+1) 
            ELSE @s = 0 
       END As user_game_rank 
FROM game_logs

Edit: (à partir des Comments OP): l'ordre est basé sur l'ordre décroissant de game_detail

ordre de game_detail


Dans une table dérivée (sous-requête à l'intérieur de la clause FROM ), nous ordonnons nos données de sorte que toutes les lignes ayant les mêmes valeurs user_id se rejoignent, avec un tri supplémentaire basé sur game_detail par ordre décroissant.

Nous utilisons maintenant cet ensemble de résultats et utilisons des expressions conditionnelles CASE..WHEN pour évaluer la numérotation des lignes. Ce sera comme une technique de Looping (que nous utilisons dans le code d'application, par exemple: PHP). Nous stockons les valeurs de la ligne précédente dans les variables définies par l'utilisateur, puis nous comparons la (les) valeur (s) de la ligne actuelle à la ligne précédente. Finalement, nous attribuerons le numéro de ligne en conséquence.

Edit: Basé sur la docs MySQL et l'observation de @Gordon Linoff:

L'ordre d'évaluation des expressions impliquant des variables utilisateur n'est pas défini. Par exemple, rien ne garantit que SELECT @a, @a: = @ a + 1 évalue d'abord @a, puis effectue l'affectation.

Nous devrons évaluer le numéro de ligne et attribuer la valeur user_id à la variable @u dans la même expression.

SET @r := 0, @u := 0; 
SELECT
  @r := CASE WHEN @u = dt.user_id 
                  THEN @r + 1
             WHEN @u := dt.user_id /* Notice := instead of = */
                  THEN 1 
        END AS user_game_rank, 
  dt.user_id, 
  dt.game_detail, 
  dt.game_id 

FROM 
( SELECT user_id, game_id, game_detail
  FROM game_logs 
  ORDER BY user_id, game_detail DESC 
) AS dt 

Résultat

| user_game_rank | user_id | game_detail | game_id |
| -------------- | ------- | ----------- | ------- |
| 1              | 6       | 260         | 11      |
| 2              | 6       | 100         | 10      |
| 1              | 7       | 1200        | 10      |
| 2              | 7       | 500         | 11      |
| 3              | 7       | 260         | 12      |
| 4              | 7       | 50          | 13      |

Voir sur DB Fiddle

Une note intéressante de MySQL docs , que j'ai découverte récemment:

Les versions précédentes de MySQL permettaient d’attribuer une valeur à une variable utilisateur dans des instructions autres que SET. Cette fonctionnalité est prise en charge dans MySQL 8.0 pour des raisons de compatibilité ascendante, mais peut être supprimée dans une version ultérieure de MySQL.

De plus, grâce à un collègue membre de SO, ce blog de l'équipe MySQL est tombé sur: https://mysqlserverteam.com/row-numbering-ranking-how-to-use-less-user-variables-in-mysql-queries/

L’observation générale est que l’utilisation de ORDER BY avec l’évaluation des variables utilisateur dans le même bloc de requête ne garantit pas que les valeurs seront toujours correctes. De même, l’optimiseur MySQL peut se mettre en place et changer notre ordre d’évaluation présumé .

La meilleure approche à ce problème serait de passer à MySQL 8+ et d’utiliser la fonctionnalité Row_Number() :

Schéma (MySQL v8.0)

SELECT user_id, 
       game_id, 
       game_detail, 
       ROW_NUMBER() OVER (PARTITION BY user_id 
                          ORDER BY game_detail DESC) AS user_game_rank 
FROM game_logs 
ORDER BY user_id, user_game_rank;

Résultat

| user_id | game_id | game_detail | user_game_rank |
| ------- | ------- | ----------- | -------------- |
| 6       | 11      | 260         | 1              |
| 6       | 10      | 100         | 2              |
| 7       | 10      | 1200        | 1              |
| 7       | 11      | 500         | 2              |
| 7       | 12      | 260         | 3              |
| 7       | 13      | 50          | 4              |

Voir sur DB Fiddle


La meilleure solution dans MySQL, antérieure à la version 8.0, est la suivante:

select gl.*, 
       (@rn := if(@lastUserId = user_id, @rn + 1,
                  if(@lastUserId := user_id, 1, 1)
                 )
        ) as user_game_rank
from (select gl.*
      from game_logs gl
      order by gl.user_id, gl.game_detail desc
     ) gl cross join
     (select @rn := 0, @lastUserId := 0) params;

La commande est effectuée dans une sous-requête. Ceci est requis à partir de MySQL 5.7. Les affectations de variables sont toutes dans une seule expression. Par conséquent, l'ordre d'évaluation des expressions n'a pas d'importance (MySQL ne garantit pas l'ordre d'évaluation des expressions).


SELECT user_id, game_id, game_detail, 
       CASE WHEN user_id = @lastUserId 
            THEN @rank := @rank + 1 
            ELSE @rank := 1 
       END As user_game_rank,
       @lastUserId := user_id
FROM game_logs
cross join (select @rank := 0, @lastUserId := 0) r
order by user_id, game_detail desc

Démo SQLFiddle





sql-rank