음머어's 까망별

블로그 이미지
무지 인간의 첫 걸음..
by 까망별 음머어
  • 295,710Total hit
  • 74Today hit
  • 148Yesterday hit

'over(partition by order by)'에 해당되는 글 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

ARTICLE CATEGORY

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

ARCHIVE