Cómo realizar una clasificación agrupada en MySQL

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

Luego necesito agruparlos, clasificarlos y ordenarlos para dar:

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 puedes usar una variable temporal para clasificar, como aquí , pero ¿cómo lo hago para un conjunto agrupado? ¡Gracias por cualquier idea!

Feb 10 '09 22:02 achinda99

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 sencilla:

La consulta inicial está ordenada por id_classprimero, id_studentsegundo.
@studenty @classse inicializan a-1
@classse utiliza para probar si se ingresa el siguiente conjunto. Si el valor anterior de id_class(que está almacenado en @class) no es igual al valor actual (que está almacenado en id_class), se @studentpone a cero. De lo contrario, se incrementa.
@classse le asigna el nuevo valor de id_classy se usará en la prueba del paso 3 en la siguiente fila.

Feb 10 '2009 16:02 Quassnoi

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

Tengo el mismo problema (es decir, simulando la función de ventana 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 funcionó. Esto se debe a que no se garantiza el orden de evaluación de los campos en SELECT. La tarea @class podría evaluarse antes de la tarea @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 debes asignar un valor a una variable de usuario y leer el valor dentro de la misma declaración. Es posible que obtenga los resultados que espera, pero esto no está garantizado. El orden de evaluación de expresiones que involucran variables de usuario no está definido y puede cambiar según los elementos contenidos en una declaración determinada; 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 como ese 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 configurar la variable @class. Luego, concatene el resultado de left() (igual a NULL) para que el resultado esperado sea transparente.

¡No es muy elegante pero funciona!

Nov 27 '2012 16:11 Nicolas Payart

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 |
+------------+----------+-------+------+

Oct 08 '2012 08:10 Jon Armstrong - Xgc

Modificado desde arriba, esto funciona pero es más complejo de lo que creo que debería 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

Feb 10 '2009 16:02 achinda99