음머어's 까망별

블로그 이미지
무지 인간의 첫 걸음..
by 까망별 음머어
  • 380,707Total hit
  • 126Today hit
  • 129Yesterday hit

'넘버링'에 해당되는 글 1건

  1. 2008.12.22
    [MySQL] Oracle의 그룹별 번호 매기기와 같은 기능 구현하기 (7)

쿼리를 날리다 보면, 필요에 따라 그룹별로 순위를 매겨야 할 때 있다.
이에 대해 오라클에서는 그러한 기능을 제공하는데,
아래가 바로 그 예이다.

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

TRACKBACK 1 AND COMMENT 7
  1. 호이루 2008.12.22 17:15 address edit/delete reply

    웅.....나 이거 이해해야 할터인데....후움.... 하암......zZZZZ 어쩌ㅠㅠ

    • 까망별 음머어 2008.12.22 17:22 신고 address edit/delete

      자네를 생각해서 장황하게 기술하였다네 -0-/
      그런데.... 쉽게 안쓰여지넹 -0-;;

    • 호이루 2008.12.24 11:51 신고 address edit/delete

      웅...응~ 해볼껭...이해하도록 -0-

  2. 이성민 2011.10.26 11:33 address edit/delete reply

    정리 정말 잘해놓으셨네요 퍼갑니다. ~
    출처는 물론 적을게요~
    감사히 잘 봤고 많은 도움 돼었습니다 ~ ^^

  3. 니쯔 2012.04.27 10:12 address edit/delete reply

    저도 퍼갑니다 저도 출저는 꼭꼭 적어둘게요
    잘 봤고요 감사합니다 많이 배우고 가네요

  4. 아리 2013.09.02 15:03 address edit/delete reply

    유용한정보 도움많이되었습니다^^

  5. Louis.Kim 2016.06.07 11:48 신고 address edit/delete reply

    좋은 정보 감사합니다.
    담아가서 다시 한번 상기시키도록 해보겠습니다.





ARTICLE CATEGORY

모든 이야기 (77)
독백 (22)
MySQL (25)
Oracle (7)
영화 (6)
Linux (3)
DW(Data Warehouse) (0)
(7)
음악 (1)
Python (3)
Postgresql (2)

ARCHIVE