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
여기처럼 온도 변수를 사용하여 순위를 매길 수 있다는 것을 알고 있지만 그룹화된 집합에 대해서는 어떻게 해야 합니까?어떤 통찰력이라도 감사드립니다!
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, ).@student0다입니다.그렇지 않으면 증분됩니다.@class합니다의 새됩니다.id_class의 3 됩니다에서 됩니다.
Quassnoi의 해결책에 문제가 있습니다(최선의 해결책으로 표시됨).
저는 동일한 문제를 가지고 있으며(즉, MySQL의 SQL Window Function 시뮬레이션), 이전 행 값을 저장하기 위해 사용자 정의 변수를 사용하여 Quassnoi의 솔루션을 구현하곤 했습니다.
그러나 MySQL 업그레이드나 뭐 다른 것을 한 후에 제 쿼리가 더 이상 작동하지 않았습니다.SELECT의 필드 평가 순서가 보장되지 않기 때문입니다.@수업 배정은 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은 최근 몇 년 동안 크게 발전했습니다.Window 함수와 CTE(WITH 절)가 추가되어 랭크(및 row_number 등)가 지원됩니다.
저는 같은 "Jon Armstrong - 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(다른 RDBMS를 위해 말할 수 없음)에 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;
어떤 것이 더 좋은지에 대한 생각은 없습니까?
() over (class_id order by grade desc) 어때요? https://www.mysqltutorial.org/mysql-window-functions/mysql-rank-function/
언급URL : https://stackoverflow.com/questions/532878/how-to-perform-grouped-ranking-in-mysql
'programing' 카테고리의 다른 글
| Django URLs TypeError: 보기는 include()인 경우 호출 가능 또는 목록/튜플이어야 합니다. (0) | 2023.10.03 |
|---|---|
| 작동하지 않는 자바스크립트 규칙은 무엇입니까? (0) | 2023.10.03 |
| Chrome에서는 작동하지만 Safari에서는 작동하지 않습니다.잘못된 정규식: 잘못된 그룹 지정자 이름 /(?<=\/)([^#]+)(?=#*)/ (0) | 2023.10.03 |
| Chrome의 파일 입력에서 "No file choosed" 툴팁을 제거하려면 어떻게 해야 합니까? (0) | 2023.10.03 |
| PHP에서 .html 파일의 전체 내용을 에코하려면 어떻게 해야 합니까? (0) | 2023.10.03 |