MySQL ROW_NUMBER ORDER BY 정렬한 후 번호 매기기
MySQL ROW_NUMBER ORDER BY 정렬한 후 번호 매기기
아..오라클은 ROW_NUMBER() OVER (PARTITION BY ...ORDER BY) 로 정렬한 후 번호 매기기가 매우 쉽다.
하지만 우리의 MySql 은 그러한 함수가 없다.
그렇지만 MySql은 쿼리문에서 변수를 사용할 수 있는 막강한 기능이 있다.
변수를 이용하여 order by 한 쿼리에 rownumber 를 구하는 방법에 대해 알아보자.
우선 감을 잡기 위해 제일 기본이 되는 쿼리문 부터 보겠다.
기본 SELECT 문이다.
SELECT T1.GG4ID AS GG4ID
, T1 .BASIC_DAY AS DAY
, T1 .BASIC_HOUR AS HOUR
, T1 .BASIC_MIN AS MIN
, T1 .VOLUME AS VOLUME
FROM MART_GUGAN4_STAT_15MIN T1
WHERE BASIC_DAY = '20151201'
ORDER BY GG4ID, HOUR, MIN
결과는 아래와 같다.
정말 말그대로 조건에 맞게 SELECT 해온 결과 이다.
이제 이 쿼리문에 ROWNUMBER를 매겨보자.
딱 2가지만 추가해 주면 된다.
SELECT T1.GG4ID AS GG4ID
, T1 .BASIC_DAY AS DAY
, T1 .BASIC_HOUR AS HOUR
, T1 .BASIC_MIN AS MIN
, T1 .VOLUME AS VOLUME
, @rownum := @rownum+1 AS RNUM
FROM MART_GUGAN4_STAT_15MIN T1, (SELECT @rownum :=0) AS R
WHERE BASIC_DAY = '20151201'
ORDER BY GG4ID, HOUR, MIN
@rownum이라는 변수를 사용했다.
(SELECT @rownum :=0) AS R 는 @rownum을 0으로 초기화해 SELECT 하겠다 이다.
그리고 @rownum := @rownum+1 AS RNUM 은 행을 불러올때마다 1을 더해서 출력하겠다 이다.
말그대로 변수를 선언하고 변수에 1씩을 더해 행에 가져오는 것이다.
결과는 위와 같이 출력된다.
그럼 여기서 한발짝 더 나아가 보자. 요즘 JOIN 없이 SELECT 하는게 있을까 싶어..
JOIN을 사용해 보자.
SELECT T2 .GG4_NAME AS GUGANNAME
, T2 .GG4_DIRECTION AS DIRECTION
, T1 .BASIC_DAY AS DAY
, T1 .BASIC_HOUR AS HOUR
, T1 .BASIC_MIN AS MIN
, T1 .VOLUME AS VOLUME
FROM MART_GUGAN4_STAT_15MIN T1
LEFT JOIN MART_GUGAN4 T2 ON (T2 .GG4ID = T1. GG4ID)
WHERE BASIC_DAY = '20151201'
AND GG4_NAME LIKE '%'
ORDER BY GUGANNAME, DIRECTION, HOUR, MIN
첫번째 쿼리문에 JOIN을 사용해 값을 더 가져오고 조건도 더 추가 했다.
여기에 ROWNUMBER을 매겨보자. 두가지만 추가 하면 되니깐...간단하겠지...
생각하여
SELECT T2 .GG4_NAME AS GUGANNAME
, T2 .GG4_DIRECTION AS DIRECTION
, T1 .BASIC_DAY AS DAY
, T1 .BASIC_HOUR AS HOUR
, T1 .BASIC_MIN AS MIN
, T1 .VOLUME AS VOLUME
, @rownum := @rownum+1 AS RNUM
FROM MART_GUGAN4_STAT_15MIN T1, (SELECT @rownum := 0) AS R
LEFT JOIN MART_GUGAN4 T2 ON (T2 .GG4ID = T1. GG4ID)
WHERE BASIC_DAY = '20151201'
AND GG4_NAME LIKE '%'
ORDER BY GUGANNAME, DIRECTION, HOUR, MIN
위와 같이 추가를 해줬다. 하지만 결과는.
아.. 위치가 잘못됬구나 싶어
SELECT T2 .GG4_NAME AS GUGANNAME
, T2 .GG4_DIRECTION AS DIRECTION
, T1 .BASIC_DAY AS DAY
, T1 .BASIC_HOUR AS HOUR
, T1 .BASIC_MIN AS MIN
, T1 .VOLUME AS VOLUME
, @rownum := @rownum+1 AS RNUM
FROM MART_GUGAN4_STAT_15MIN T1
LEFT JOIN MART_GUGAN4 T2 ON (T2 .GG4ID = T1. GG4ID)
, (SELECT @rownum := 0) AS R
WHERE BASIC_DAY = '20151201'
AND GG4_NAME LIKE '%'
ORDER BY GUGANNAME, DIRECTION, HOUR, MIN
위치를 바쿼 출력했다. 오류가 안난다. 하지만 결과는.
꼬였다.. 조인을 하는 시점에서 @rownum이 잘못 더해지고 있었다.
이런 문제를 해결하는 방법은 서브쿼리를 사용하여 @rownum이 더해지는 시점을 바꾸는 것이다.
SELECT A.*
, @rownum := @rownum+1 AS RNUM
FROM (
SELECT T2 .GG4_NAME AS GUGANNAME
, T2 .GG4_DIRECTION AS DIRECTION
, T1 .BASIC_DAY AS DAY
, T1 .BASIC_HOUR AS HOUR
, T1 .BASIC_MIN AS MIN
, T1 .VOLUME AS VOLUME
FROM MART_GUGAN4_STAT_15MIN T1
LEFT JOIN MART_GUGAN4 T2 ON (T2 .GG4ID = T1. GG4ID)
JOIN (SELECT @rownum := 0) AS R
WHERE BASIC_DAY = '20151201'
AND GG4_NAME LIKE '%'
ORDER BY GUGANNAME, DIRECTION, HOUR, MIN) AS A
참고) JOIN (SELECT @rownum := 0) AS R 과 ,(SELECT @rownum := 0) AS R 은 같다. 표현방식이 다를뿐이다.
간단하다. 데이터를 만든 시점에서 @rownum을 더하느냐 만들면서 더하느냐의 차이다
결과를 보면 원하는데로. order by 되고 조건에 맞는 값을 가져온 후에 @rownum을 구한다.
여기서 RNUM을 컨트롤 하기 위해선 한번더 서브쿼리를 써주면 된다..
SELECT *
FROM ( SELECT A.*
, @rownum := @rownum + 1 AS RNUM
FROM (
SELECT T2 .GG4_NAME AS GUGANNAME
, T2 .GG4_DIRECTION AS DIRECTION
, T1 .BASIC_DAY AS DAY
, T1 .BASIC_HOUR AS HOUR
, T1 .BASIC_MIN AS MIN
, T1 .VOLUME AS VOLUME
FROM MART_GUGAN4_STAT_15MIN T1
LEFT JOIN MART_GUGAN4 T2 ON (T2 .GG4ID = T1. GG4ID)
JOIN ( SELECT @rownum := 0) R
WHERE BASIC_DAY = '20151201'
AND GG4_NAME LIKE '%') AS A
ORDER BY A.GUGANNAME, A.DIRECTION, A.HOUR, A.MIN) AS B
WHERE RNUM BETWEEN 1 AND 10
