functions - mysql workbench

Cómo realizar clasificaciones agrupadas en MySQL

Question

Entonces tengo una tabla de la siguiente manera:

ID_STUDENT | ID_CLASS | GRADE
-----------------------------
1 | 1 | 90
1 | 2 | 80
2 | 1 | 99
3 | 1 | 80
4 | 1 | 70
5 | 2 | 78
6 | 2 | 90
6 | 3 | 50
7 | 3 | 90

Necesito agrupar, ordenar y ordenar que den:

ID_STUDENT | ID_CLASS | GRADE | RANK
------------------------------------
2 | 1 | 99 | 1
1 | 1 | 90 | 2
3 | 1 | 80 | 3
4 | 1 | 70 | 4
6 | 2 | 90 | 1
1 | 2 | 80 | 2
5 | 2 | 78 | 3
7 | 3 | 90 | 1
6 | 3 | 50 | 2

Ahora sé que puede usar una variable de temperatura para clasificar, como aquí , pero ¿cómo lo hago para un conjunto agrupado? Gracias por cualquier idea!

Answer 1

Hay un problema con la solución de Quassnoi (marcado como la mejor respuesta).

Tengo la misma problemática (es decir, simulando la función de ventana de SQL en MySQL) y solía implementar la solución de Quassnoi, usando variables definidas por el usuario para almacenar el valor de la fila anterior ...

Pero, tal vez después de una actualización de MySQL o lo que sea, mi consulta ya no funcionaba. Esto se debe a que el orden de evaluación de los campos en SELECT no está garantizado. La asignación de clase @ podría evaluarse antes de la asignación de @student, incluso si se coloca después en SELECT.

Esto se menciona en la documentación de MySQL de la siguiente manera:

Como regla general, nunca debe asignar un valor a una variable de usuario y leer el valor dentro de la misma instrucción. Puede obtener los resultados que espera, pero esto no está garantizado. El orden de evaluación para expresiones que involucran variables de usuario no está definido y puede cambiar en función de los elementos contenidos en una declaración dada; Además, no se garantiza que este orden sea el mismo entre versiones del servidor MySQL.

fuente: http://dev.mysql.com/doc/refman/5.5/en/user-variables.html

Finalmente, he usado un truco así para asegurarme de asignar @class DESPUÉS de leerlo:

SELECT id_student, id_class, grade,
@student:=CASE WHEN @class <> id_class THEN concat(left(@class:=id_class, 0), 0) ELSE @student+1 END AS rn
FROM
(SELECT @student:= -1) s,
(SELECT @class:= -1) c,
(SELECT *
FROM mytable
ORDER BY id_class, grade desc
) t

El uso de la función left () solo se usa para establecer la variable @class. Luego, concatene el resultado de left () (igual a NULL) al resultado esperado es transparente.

No es muy elegante pero funciona!

Answer 2

Hice algunas búsquedas, encontré este artículo para encontrar esta solución:

SELECT S2.*,
FIND_IN_SET(
S2.GRADE
, (
SELECT GROUP_CONCAT(GRADE ORDER BY GRADE DESC)
FROM Students S1
WHERE S1.ID_CLASS = S2.ID_CLASS
)
) AS RANK
FROM Students S2 ORDER BY ID_CLASS, GRADE DESC;

¿Alguna idea de cuál es mejor?

Answer 3

Modificado desde arriba, esto funciona, pero es más complejo de lo que creo que debe ser:

SELECT ID_STUDENT, ID_CLASS, GRADE, RANK
FROM
(SELECT ID_STUDENT, ID_CLASS, GRADE,
@student:=CASE WHEN @class <> id_class THEN 1 ELSE @student+1 END AS RANK,
@class:=id_class AS CLASS
FROM
(SELECT @student:= 0) AS s,
(SELECT @class:= 0) AS c,
(SELECT *
FROM Students
ORDER BY ID_CLASS, GRADE DESC
) AS temp
) AS temp2

Answer 4

SELECT g1.student_id
, g1.class_id
, g1.grade
, COUNT(*) AS rank
FROM grades AS g1
JOIN grades AS g2
ON (g2.grade, g2.student_id) >= (g1.grade, g1.student_id)
AND g1.class_id = g2.class_id
GROUP BY g1.student_id
, g1.class_id
, g1.grade
ORDER BY g1.class_id
, rank
;

Resultado:

+------------+----------+-------+------+
| student_id | class_id | grade | rank |
+------------+----------+-------+------+
| 2 | 1 | 99 | 1 |
| 1 | 1 | 90 | 2 |
| 3 | 1 | 80 | 3 |
| 4 | 1 | 70 | 4 |
| 6 | 2 | 90 | 1 |
| 1 | 2 | 80 | 2 |
| 5 | 2 | 78 | 3 |
| 7 | 3 | 90 | 1 |
| 6 | 3 | 50 | 2 |
+------------+----------+-------+------+

Answer 5

SELECT id_student, id_class, grade,
@student:=CASE WHEN @class <> id_class THEN 0 ELSE @student+1 END AS rn,
@class:=id_class AS clset
FROM
(SELECT @student:= -1) s,
(SELECT @class:= -1) c,
(SELECT *
FROM mytable
ORDER BY id_class, id_student
) t

Esto funciona de una manera muy simple:

1. La consulta inicial está ordenada por id_class primero, id_student segunda.
2. @student y @class se inicializan en -1
3. @class se usa para probar si se ingresa el siguiente conjunto. Si el valor anterior de id_class (que se almacena en @class ) no es igual al valor actual (que se almacena en id_class ), @student se pone a cero. De lo contrario, se incrementa.
4. @class se asigna con el nuevo valor de id_class , y se usará en la prueba en el paso 3 en la siguiente fila.