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

[출처] http://blackbull.tistory.com/43

쿼리를 날리다 보면, 필요에 따라 그룹별로 순위를 매겨야 할 때가 있다.

이에 대해 오라클에서는 그러한 기능을 제공하는데,

아래가 바로 그 예이다.

[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

---------- -------------------- ------------------ ---------- ----------

      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 개의 행이 선택되었습니다.

상기 쿼리는,

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