Как выполнить групповое ранжирование в MySQL
Итак, у меня есть таблица следующим образом:
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
Мне нужно затем сгруппировать, отсортировать и заказать их, чтобы дать:
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
Теперь я знаю, что вы можете использовать временную переменную для ранжирования, как здесь, но как мне сделать это для сгруппированного набора? Спасибо за понимание!
8 ответов
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
Это работает очень просто:
- Первоначальный запрос упорядочен по
id_classпервый,id_studentвторой. @studentа также@classинициализируются в-1@classиспользуется для проверки, введен ли следующий набор. Если предыдущее значениеid_class(который хранится в@class) не равно текущему значению (которое хранится вid_class),@studentобнуляется. В противном случае значение увеличивается.@classназначается с новым значениемid_classи он будет использован в тесте на шаге 3 в следующем ряду.
Существует проблема с решением Quassnoi (помечен как лучший ответ).
У меня такая же проблема (то есть имитация функции окна SQL в MySQL), и я использовал для реализации решения Quassnoi, используя пользовательские переменные для хранения предыдущего значения строки...
Но, возможно, после обновления MySQL или чего-то еще, мой запрос больше не работал. Это связано с тем, что порядок вычисления полей в SELECT не гарантируется. Назначение @class может быть оценено до назначения @student, даже если оно помещено после в SELECT.
Это упоминается в документации MySQL следующим образом:
Как правило, вы никогда не должны присваивать значение пользовательской переменной и читать значение в том же выражении. Вы можете получить ожидаемые результаты, но это не гарантировано. Порядок вычисления для выражений с участием пользовательских переменных не определен и может изменяться в зависимости от элементов, содержащихся в данном выражении; Кроме того, этот порядок не гарантируется одинаковым между выпусками MySQL Server.
источник: http://dev.mysql.com/doc/refman/5.5/en/user-variables.html
Наконец, я использовал такой трюк, чтобы быть уверенным, что присваиваем @class ПОСЛЕ чтения:
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
Использование функции left() просто используется для установки переменной @class. Затем объедините результат left() (равный NULL), чтобы ожидаемый результат был прозрачным.
Не очень элегантно, но это работает!
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
;
Результат:
+------------+----------+-------+------+
| 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 |
+------------+----------+-------+------+
Модифицированный сверху, это работает, но это более сложный, чем я думаю, это должно быть:
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
Хотя у меня недостаточно очков репутации, чтобы комментировать (немного юмористически), MySQL за последние годы прошел долгий путь. Были добавлены оконные функции и CTE (предложение WITH), что означает, что теперь поддерживается ранг (и row_number и т. Д.).
Я тот же «Джон Армстронг - Xgc», но эта учетная запись была потеряна из-за ветров старых адресов электронной почты.
В одном из комментариев возник вопрос о том, поддерживает ли MySQL функцию окна ранжирования. Ответ: Да.
Мой первоначальный ответ несколько лет назад:
SELECT p1.student_id
, p1.class_id
, p1.grade
, COUNT(p2.student_id) AS rank
FROM grades AS p1
JOIN grades AS p2
ON (p2.grade, p2.student_id) >= (p1.grade, p1.student_id)
AND p1.class_id = p2.class_id
GROUP BY p1.student_id, p1.class_id
ORDER BY p1.class_id, rank
;
Полученные результаты:
+------------+----------+-------+------+
| 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 |
+------------+----------+-------+------+
9 rows in set (0.001 sec)
Использование оконной функции ROW_NUMBER:
WITH cte1 AS (
SELECT student_id
, class_id
, grade
, ROW_NUMBER() OVER (PARTITION BY class_id ORDER BY grade DESC) AS rank
FROM grades
)
SELECT *
FROM cte1
ORDER BY class_id, r
;
Результат:
+------------+----------+-------+------+
| 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 |
+------------+----------+-------+------+
9 rows in set (0.002 sec)
Использование функции окна RANK:
WITH cte1 AS (
SELECT student_id
, class_id
, grade
, RANK() OVER (PARTITION BY class_id ORDER BY grade DESC) AS rank
FROM grades
)
SELECT *
FROM cte1
ORDER BY class_id, rank
;
Результат:
+------------+----------+-------+------+
| 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 |
+------------+----------+-------+------+
9 rows in set (0.000 sec)
SELECT ID_STUDENT, ID_CLASS, GRADE, RANK() OVER(
PARTITION BY ID_CLASS
ORDER BY GRADE ASC) AS 'Rank'
FROM table
ORDER BY ID_CLASS;
У меня была аналогичная проблема с домашним заданием, я обнаружил, что MySQL (не может говорить ни о какой другой СУБД) имеет аргумент раздела для своего метода RANK(). Не понимаю, почему это не сработает для этой проблемы.
Я сделал некоторые поиски, нашел эту статью, чтобы придумать это решение:
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;
Есть мысли о том, что лучше?
Как насчет rank() over(partition by class_id order bygrade desc)?https://www.mysqltutorial.org/mysql-window-functions/mysql-rank-функция/