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

SQL 쿼리를 이용하여 DB에 있는 데이터를 추출하다보면, 그룹별로 순위나 번호를 매겨야하는 순간이 있습니다.

최근, SQL 관련 업무를 진행하다 앞서 말한 상황이 발생해서 문제를 해결하기 위해 여러 사이트와 구글링을 이용해서 공부했습니다.

 

Oracle의 경우, ROW_NUMBER() / PARTITION BY를 이용하여 이러한 기능을 사용할 수 있습니다.

하지만, MySQL에는 이러한 기능이 없어 변수를 이용하여 구현해야 합니다.

 

개인적으로 MySQL을 깔아서 사용하기보다는 SQL test를 할 수 있는 사이트를 자주 애용합니다.

아래의 SQL Fiddle은 직접 데이터 테이블을 만들어서 연습할 수 있기에 아래에 올려두겠습니다. 

SQL Test Site :  http://sqlfiddle.com

 

<예시>

Table : 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);

반응형

문제 : emp 테이블에서 직업별 salary의 순위(salary가 높은 순위)를 매겨라. 단, 결과는 직업순으로 나타내라.

<쿼리>

SELECT empno, ename, job, sal, job_sal_rank
FROM ( 
  SELECT a.*,
        (CASE @cjob WHEN a.job THEN @rownum := @rownum + 1 ELSE @rownum := 1 END) job_sal_rank,
        (@cjob := a.job) cjob
  FROM emp a, (SELECT @cjob :='',@rownum := 0 FROM DUAL) b
  ORDER BY a.job,a.sal DESC
 ) c;

 

쿼리를 보면 아시다시피 안에 변수가 많이 들어가 있습니다. 그냥 전체 순위가 아닌 직업별 순위이므로 그룹핑을 해주면서 순위를 매겨야 합니다.

먼저, CASE @cjob WHEN a.job THEN @rownum := @rownum + 1 ELSE @rownum := 1 END) job_sal_rank는

@cjob 변수가 a테이블의 job필드와 같다면 @rownum을 1씩 증가시키고, 만약 다르다면 다시 1로 초기화시켜 순위를 매기는 매커니즘입니다. 즉, 직업별 급여 순위를 나타내주는 쿼리라고 이해하면 편합니다.

그렇다면, 아래 (@cjob := a.job) 의미는 바로 알 수 있는데 이는 위에서 쓰이는 @cjob의 변수를 정해주는 쿼리라고 생각하면 됩니다.

마지막으로 (SELECT @cjob :='',@rownum := 0 FROM DUAL) 는 마치 처음에 변수를 정의해줄 때 사용하는 SET문처럼 @cjob과 @rownum을 초기화시켜주는 역할을 해줍니다. 처음에 SET문으로 정의할 경우, 쿼리가 2번 수행되기에 FROM문에 서브쿼리를 이용하여 한 번에 쿼리가 수행되게 해주었습니다. b ORDER BY a.sal DESC는 salary가 높은 순으로 순위가 매겨지기에 내림차순으로 ORDER BY 해주었습니다.

 

<결과>

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

결과는 이런식으로 나왔습니다. 해당 주제의 경우, 즐겨 보는 까망별님의 티스토리 블로그를 참조했습니다.

 

출처 : https://blackbull.tistory.com/43

[MySQL] Oracle의 그룹별 번호 매기기와 같은 기능 구현하기