쿼리를 날리다 보면, 필요에 따라 그룹별로 순위를 매겨야 할 때가 있다.
이에 대해 오라클에서는 그러한 기능을 제공하는데,
아래가 바로 그 예이다.
[Oracle]
SELECT empno, ename, job, sal,
ROW_NUMBER() OVER(PARTITION BY job ORDER BY sal) AS rnum
FROM scott.emp;
<<결과>>
|
EMPNO ENAME JOB SAL RNUM 14 개의 행이 선택되었습니다. |
상기 쿼리는,
emp 테이블의 JOB을 기준으로 하여 그룹을 정하고 (PARTITION BY job), -- 1
sal을 기준으로 하여 순위를 매겨(ORDER BY sal),
각각의 행에 ROW_NUMBER를 부여하겠다는 의미이다. -- 2
여기서 'PARTITION BY job'은 job별 정렬을 발생시킨다.
즉, 최종 결과물의 넘버링은 ORDER BY job, sal의 순으로 결과가 나오는 것이다.
[MySQL]
그런데, 불행하게도..... MySQL에는 저 기능이 없다.
그렇기 때문에 우리의 친구 꼼수(?)를 이용하여 저것을 구현해 내야 하는데.....
SELECT empno, ename, job, sal, rnum
FROM (
SELECT a.*,
(CASE @vjob WHEN a.job THEN @rownum:=@rownum+1 ELSE @rownum:=1 END) rnum,
(@vjob:=a.job) vjob
FROM emp a, (SELECT @vjob:='', @rownum:=0 FROM DUAL) b
ORDER BY a.job, a.sal
) c;
<<결과>>
| +-------+--------+-----------+------+------+ | empno | ename | job | sal | rnum | +-------+--------+-----------+------+------+ | 7902 | FORD | ANALYST | 3000 | 1 | | 7788 | SCOTT | ANALYST | 3000 | 2 | | 7369 | SMITH | CLERK | 800 | 1 | | 7900 | JAMES | CLERK | 950 | 2 | | 7876 | ADAMS | CLERK | 1100 | 3 | | 7934 | MILLER | CLERK | 1300 | 4 | | 7782 | CLARK | MANAGER | 2450 | 1 | | 7698 | BLAKE | MANAGER | 2850 | 2 | | 7566 | JONES | MANAGER | 2975 | 3 | | 7839 | KING | PRESIDENT | 5000 | 1 | | 7654 | MARTIN | SALESMAN | 1250 | 1 | | 7521 | WARD | SALESMAN | 1250 | 2 | | 7844 | TURNER | SALESMAN | 1500 | 3 | | 7499 | ALLEN | SALESMAN | 1600 | 4 | +-------+--------+-----------+------+------+ 14 rows in set (0.00 sec) |
어때... 결과가 같아 보이는가?
자, 그럼 쿼리를 뜯어보자.
여기서 궁금하게 생각되는 부분은 아래 3개의 쿼리라고 예상 된다.
1. (CASE @vjob WHEN a.job THEN @rownum:=@rownum+1 ELSE @rownum:=1 END) rnum,
--> 이전 job 필드와 동일한 그룹인가를 판별하고, 그룹에 따라 순번을 부여하기 위함이며,
테이블에서 각각의 행을 읽을 때마다,
변수 @vjob 값이 지금 새로 읽은 job 과 같다면 변수 @rownum을 1증가 시키고,
그렇지 않은 경우(@vjob이 현재 읽은 job값과 같지 않다면) @rownum을 1로 초기화 시킨다.
2. (@vjob:=a.job) as vjob
--> 테이블에서 각각의 행을 읽을 때마다,
그룹 판별을 위해 현재 읽고 있는 행의 job값을 변수 @vjob에 입력
3. (SELECT @vjob:='', @rownum:=0 FROM DUAL) b
--> 원래는 쿼리를 수행하기 이전에,
SET @vjob:=0, @rownum:=0; 을 수행하여 변수를 초기화 해야 한다.
만약 해주지 않으면, NULL 값이 들어가게 된다.
하지만 그럴 경우 쿼리가 2번 수행되어야 하기 때문에,
하나의 쿼리로 만들기 위해서 이런 식의 서브 쿼리를 이용한 것이다.
이 서브쿼리는 초기 테이블 확인시 1회만 수행되고,
이후부터는 열람되지 않는다.
!! 주의 !!
서브쿼리 안에서의 결과값만 가지고 현재의 결과값을 얻고자 할 때,
변수가 되는 항목의 값을 동일한 자료형으로 맞춰주지 않으면,
정상적인 결과값이 나오지 않는다.
가령 위의 예를 이용하자면, @vjob의 초기값을 @vjob:=0 으로 수행 하고
서브쿼리만을 수행하면 정상적인 결과값이 나오지 않게 된다.
한 번 해보자~
이 3가지를 이해한다면 아마 이해할 수 있을 것이라 생각되지만,
한 가지 짚고 넘어가야 할 것이 있다.
Q. 우리가 흔히 쓰는 SELECT 문장의 수행순서는 어떻게 될까?
무슨의미냐 하면..
위에서 사용한 것처럼 변수를 이용한 SELECT 내 연속적인 값의 할당은,
수행결과에 영향을 미치게 되지 않을까?
라는 질문이다.
흠.. 내가 말을 써놓고 난해하군..
예제를 보도록 하자.
<<예제>>
SET @val1=0, @val2=0; #아까도 말했듯이 변수 초기화는 먼저 선행되어야 한다.
SELECT @val1:=@val1+1, @val2:=@val1+1, @val2:=0, @val1=@val2+1
FROM DUAL;
자.... 당신이 예상하는 결과는?.....
<<쿼리 수행 결과>>
| +----------------+----------------+----------+---------------+ | @val1:=@val1+1 | @val2:=@val1+1 | @val2:=0 | @val1=@val2+1 | +----------------+----------------+----------+---------------+ | 1 | 2 | 0 | 1 | +----------------+----------------+----------+---------------+ 1 row in set (0.00 sec) |
상기와 같이 SELECT 내 수행 결과는,
왼쪽에서 오른쪽으로 순차적인 수행이 이루어짐을 알 수 있다.
즉, @val1:=@val1+1 → @val2:=@val1+1 → @val2:=0 → @val1=@val2+1
로 수행 순서가 정해진다는 의미.
그러므로,
변수를 이용한 SELECT를 이용할 때는 반드시 수행순서를 염두해 두고 쿼리를 작성하도록 하자.
PS : 오라클에는 예제 테이블이 있지만 MySQL 에는 없으니
혹시 테스트 해보고 싶은 사람은 아래 쿼리를 수행해서 테스트 해보도록...
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); |
