[MySQL] rank 순위 구하기

DB에서 데이터를 처리할 때, 순위(랭킹)를 구해야 하는 상황은 자주 일어납니다.

MySQL은 Oracle이나 MSSQL 등에서 제공하는 ROW_NUMBER()나 RANK() 함수가 따로 존재하지 않아

따로 변수를 만들어 순위를 구하는 쿼리문을 만들어야 합니다.

 

과거 이와 비슷한 MySQL 그룹별 순위 매기기에 대한 글을 올렸으니 관심 있으신 분들은 보시는 걸 추천드립니다.

☞ [MySQL] 그룹별 순위 매기기 구현하기

---

- Sample Data : emp

CREATE TABLE emp (
   empno INT,
   ename VARCHAR(30),
   job VARCHAR(30),
   sal INT
)ENGINE=INNODB DEFAULT CHAR SET=UTF8;

INSERT INTO emp
VALUES
(7902,'FORD','ANALYST',3000),
(7788,'SCOTT','ANALYST',3000),
(7369,'SMITH','CLERK',800),
(7900,'JAMES','CLERK',950),
(7876,'ADAMS','CLERK',1100),
(7934,'MILLER','CLERK',1300),
(7782,'CLARK','MANAGER',2450),
(7698,'BLAKE','MANAGER',2850),
(7566,'JONES','MANAGER',2975),
(7839,'KING','PRESIDENT',5000),
(7654,'MARTIN','SALESMAN',1250),
(7521,'WARD','SALESMAN',1250),
(7844,'TURNER','SALESMAN',1500),
(7499,'ALLEN','SALESMAN',1600);

 

▣ 순위(랭킹) 구현

SELECT empno, ename, job, sal, rank FROM (
  SELECT *, @vrank := @vrank + 1 as rank
  FROM emp as e, (SELECT @vrank := 0) as r
  ORDER BY sal DESC 
  ) AS A

서브쿼리에서 emp 테이블에 vrank라는 변수 생성

sal을 내림차순으로 정렬하면서 순위(vrank)에 대한 값을 +1씩 처리

 

▣ 쿼리 결과

empno	ename	job	sal	rank
7839	KING	PRESIDENT	5000	1
7902	FORD	ANALYST	3000	2
7788	SCOTT	ANALYST	3000	3
7566	JONES	MANAGER	2975	4
7698	BLAKE	MANAGER	2850	5
7782	CLARK	MANAGER	2450	6
7499	ALLEN	SALESMAN	1600	7
7844	TURNER	SALESMAN	1500	8
7934	MILLER	CLERK	1300	9
7654	MARTIN	SALESMAN	1250	10
7521	WARD	SALESMAN	1250	11
7876	ADAMS	CLERK	1100	12
7900	JAMES	CLERK	950	13
7369	SMITH	CLERK	800	14

쿼리문을 결과값을 보면 알 수 있듯이 salary가 동일함에도 순위가 다르게 매겨졌음을 알 수 있습니다.

동일한 salary에 대한 순위를 동일 처리해야 하는 상황에서 쓰일 수 있는 쿼리에 대해 소개해드리겠습니다.

반응형

▣ 동일 순위(랭킹) 구현

SELECT empno, ename, job, sal, rank FROM (
  SELECT *,CASE 
  WHEN @pv = sal THEN @vrank
  WHEN @pv := sal THEN @vrank := @vrank + 1
  END AS rank
  FROM emp as e, (SELECT @vrank := 0, @pv := NULL) as r
  ORDER BY sal DESC 
  ) AS A

서브쿼리 내 동일한 sal에 대한 동일 순위 처리 위해 pv 변수 생성

pv변수는 sal 값 배정해주고

Case1. sal 값이 같을 때는 동일한 순위(vrank) 부여

Case2. pv변수에 새로운 sal 값이 배정될 때(sal 값 다를 때), 순위(vrank) +1 처리

 

▣ 쿼리 결과

empno	ename	job	sal	rank
7839	KING	PRESIDENT	5000	1
7902	FORD	ANALYST	3000	2
7788	SCOTT	ANALYST	3000	2
7566	JONES	MANAGER	2975	3
7698	BLAKE	MANAGER	2850	4
7782	CLARK	MANAGER	2450	5
7499	ALLEN	SALESMAN	1600	6
7844	TURNER	SALESMAN	1500	7
7934	MILLER	CLERK	1300	8
7654	MARTIN	SALESMAN	1250	9
7521	WARD	SALESMAN	1250	9
7876	ADAMS	CLERK	1100	10
7900	JAMES	CLERK	950	11
7369	SMITH	CLERK	800	12

salary를 기준으로 순위를 매겼을 때 같은 salary에 대한 순위가 동일하게 처리

 

References

• MySQL 데이터베이스에서 순위(랭킹) 구하기 [세인드의 블로그]