에드가의 Daily

오늘은 Oracle 에서 통영되는 질의문인 (+) 연산자에 대해서 기록을 해봅니다.

(+) 연산자는 어떠한 역할을 가지고 있는가? Mysql 에서는 OUTER JOIN 와 같은 의미라고 해도 무방하다고 생각합니다.

예시를 통하여 (+) 연산자를 알아보시다.

SELECT * FROM TEST A LEFT OUTER JOIN rank_test B ON A.NAME = B.NAME;

SELECT * FROM TEST A, rank_test B WHERE A.NAME = B.NAME(+);

위의 2개의 쿼리문으로만 봣을때는 결과가 동일하게 나옵니다. 여기서 (+) 연산자에 대해서 눈치 채셨나요?

▲해당 위의 테이블을 참조하여 생각해보자. ▲

 SELECT * FROM TEST A LEFT OUTER JOIN rank_test B ON A.NAME = B.NAME;

해당 코드는 TEST 테이블과 RANK_TEST 테이블을 OUTER JOIN을 하는데 LEFT 명칭을 붙여서 TEST 테이블을 다 출력되도록 OUTER JOIN 을 걸은 것을 볼 수 있다.

SELECT * FROM TEST A, rank_test B WHERE A.NAME = B.NAME(+);

이 쿼리문은 어떠한 의미를 가지고 있을까? 일단 결과는 위에서 말했다 시피 같다.

왜??? 같은 결과가 나오는 것인가?

쿼리문에서 보면은 B.NAME(+) 에 (+) 연산자를 사용할 것을 확인 할 수 있다.

아~ 그럼 A.NAME = B.NAME(+) 해당 조건에서 A 라는 별칭을 사용하고 있는 TEST 테이블이 OUTER JOIN이 걸리는걸 확인 가능하다.

반대로 A.NAME(+) =B.NAME 이렇게 사용한다면 B별칭을 사용하고 있는 RANK_TEST 테이블이 OUTER JOIN 이 걸릴 것이다.

※ 간단하게 (+) 연산자에 대해서 알아보았습니다. ※

오늘은 Oracle 에서 순위함수에 대해서 알아봅시다.

• ROW_NUMBER()
• RANK()
• DENSE_RANK()

위의 3개의 함수를 사용하면서 개념을 알아봅시다.

아래는 예제로 사용할 테이블 컬럼들 입니다.

 SELECT ROWNUM ,rank_test.* FROM rank_test ORDER BY BOARD_CNT DESC;

위의명령어로 실행을 하였을때 우리의 생각과 다른 문제점이 1개가 발생합니다.

ROWNUM 시스템의 순번을 메기는 함수입니다. 하지만 ORDER BY 를 사용하는 순간

ROWNUM 의 순번이 뒤죽박죽으로 나오는 것을 확인할 수 있습니다.

분명히 ROWNUM 은 순번을 메기는 거라고 알고 있엇는데 왜 뒤죽박죽으로 나온것일까?

그 이유는 쿼리가 실행되는 순서에 관련이 있습니다.

지금 저 쿼리문에서 실행순서는 FROM -> ORDER BY -> SELECT  이 순서대로 실행되고 있기때문에 당현히 ORDER BY 된 후 SELECT 되기에 순서가 뒤죽박죽으로 나오는 것입니다.

자 그럼 어떻게 저 쿼리문을 사용하되 ROWNUM이 순서대로 나오게 할수 있을까?

이때 사용하는 함수가 ROW_NUMBER() 라는 함수 입니다.

SELECT  ROW_NUMBER() OVER (ORDER BY BOARD_CNT) AS SEQ ,rank_test.* FROM rank_test;

바뀐점은 ROWNUM => ROW_NUMBER() OVER ( ORDER BY 문 ) ROW_NUM() 함수를 사용하면서 ORDER BY 를 함수 () 안에 넣을 것입니다.

결과가 어떻게 나올까요?

SEQ 순번으로 지정한것이 순서대로 바르게 나오는 것을 확인할 수 있습니다.

SELECT RANK() OVER (ORDER BY BOARD_CNT) AS SEQ ,rank_test.* FROM rank_test;

RANK() 함수를 사용하면은 결과가 어떻게 나올까요?

결과창에서 이상한점이 보이시나요? SEQ 컬럼이 분명 1 다음 2가 나와야 할텐데 어째서 해당컬럼만큼 건너뛰어서 6이 나올까요?

어떻게 하면은 정상적으로 순번이 나오도록 할수 있을까?

SELECT DENSE_RANK() OVER (ORDER BY BOARD_CNT) AS SEQ ,rank_test.* FROM rank_test;

▼해당 쿼리문의 결과▼

정상적으로 1 - 2 - 3 으로 출력되는 것을 확인 할 수 있습니다.

추가적으로 지역별로 랭킹을 알고 싶을때는 PARTITION BY  라는 것을 사용하면 보다 편리하게 지역별 랭킹을 알 수있습니다.

SELECT DENSE_RANK() OVER ( PARTITION BY ADDRESS ORDER BY BOARD_CNT DESC) AS SEQ ,rank_test.* FROM rank_test;

▼해당 쿼리문의 결과▼

경기도/광주/서울 에서의 랭킹을 한눈에 볼 수 있습니다.

상황에 따라서 PARTITION BY 을 사용하면 보다 편리하게 사용할 수 있을거 같은 생각이 듭니다.