Determine el rango basado en múltiples columnas en MySQL
sql-rank (4)
Tengo una tabla que tiene 3 campos, quiero clasificar la columna según user_id y game_id.
Aquí está el Fiddle de SQL: http://sqlfiddle.com/#!9/883e9d/1
La mesa ya tengo:
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 |
Rendimiento esperado :
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 |
Mis esfuerzos hasta ahora:
SET @s := 0;
SELECT user_id,game_id,game_detail,
CASE WHEN user_id = user_id THEN (@s:=@s+1)
ELSE @s = 0
END As user_game_rank
FROM game_logs
Edición:
(De los
Comments
OP): los pedidos se basan en el orden descendente de
game_detail
orden de game_detail
En una
Tabla Derivada
(subconsulta dentro de la cláusula
FROM
),
game_detail
nuestros datos de modo que todas las filas que tienen los mismos valores de
user_id
se unan, y la clasificación entre ellas se basa en
game_detail
en orden Descendente.
Ahora, usamos este conjunto de resultados y usamos expresiones condicionales
CASE..WHEN
para evaluar la numeración de filas.
Será como una técnica de bucle (que utilizamos en el código de la aplicación, por ejemplo, PHP).
Almacenaríamos los valores de la fila anterior en las variables definidas por el usuario y luego verificaríamos los valores de la fila actual en comparación con la fila anterior.
Eventualmente, le asignaremos el número de fila en consecuencia.
Edición: Basado en los docs MySQL y la observación de @Gordon Linoff:
El orden de evaluación de las expresiones que involucran variables de usuario no está definido. Por ejemplo, no hay garantía de que SELECT @a, @a: = @ a + 1 evalúe @a primero y luego realice la asignación.
Necesitaremos evaluar el número de fila y asignar el valor
user_id
a
@u
variable dentro de la misma expresión.
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
Resultado
| 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 |
Una nota interesante de MySQL docs , que descubrí recientemente:
Las versiones anteriores de MySQL hicieron posible asignar un valor a una variable de usuario en declaraciones que no sean SET. Esta funcionalidad es compatible con MySQL 8.0 para compatibilidad con versiones anteriores, pero está sujeta a eliminación en una versión futura de MySQL.
Además, gracias a un miembro de SO, encontré este blog del equipo de MySQL: https://mysqlserverteam.com/row-numbering-ranking-how-to-use-less-user-variables-in-mysql-queries/
La observación general es que el uso de
ORDER BY
con la evaluación de las variables de usuario en el mismo bloque de consulta, no garantiza que los valores sean siempre correctos.
Como, el optimizador de MySQL
puede
entrar en su lugar y cambiar nuestro
presunto
orden de evaluación.
El mejor enfoque para este problema sería actualizar a MySQL 8+ y utilizar la funcionalidad
Row_Number()
:
Esquema (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;
Resultado
| 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 |
La mejor solución en MySQL, antes de la versión 8.0 es la siguiente:
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;
El ordenamiento se realiza en una subconsulta. Esto es necesario a partir de MySQL 5.7. Las asignaciones de variables están todas en una expresión, por lo que no importa el orden diferente de evaluación de las expresiones (y MySQL no garantiza el orden de evaluación de las expresiones).
Puede utilizar una sub consulta relacionada muy simple:
SELECT *, (
SELECT COUNT(DISTINCT game_detail) + 1
FROM game_logs AS x
WHERE user_id = t.user_id AND game_detail > t.game_detail
) AS user_game_rank
FROM game_logs AS t
ORDER BY user_id, user_game_rank
Es más lento pero mucho más confiable que las variables de usuario. Todo lo que se necesita es una ÚNETE para romperlos.
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