[Oracle]select , order by, fetch first rows only, rownum 에 대해서 알아 보자.

안녕하세요.  

지난번에는 테이블을 만들고 데이터를 가져오기 하여 import를 해 보았습니다.

 

2022.04.10 - [SW교육/DB] - [Oracle]Dbeaver에서 테이블 생성 및 데이터 가져오기(import, insert)

[Oracle]Dbeaver에서 테이블 생성 및 데이터 가져오기(import,insert)

 

오늘은 그 테이블을 이용해서 몇 가지 예제로 아주 간단한 select 쿼리를 실행하여 보겠습니다.

이 쿼리를 통해서 order by의 용법 fetch first rows only, ruwnum 등에 대해서 알아보도록 하겠습니다. 

우선 예제에서 사용할 테이블은 다음과 같습니다.

오늘은 하나의 테이블만 사용하겠습니다.

 

1. LOTTO_AMT : 회차별, 로또 당첨 순위별 로또 금액과 당첨자 수

 : LOTTO_CFCD - '01' : 1등, '02' : 2등 ~ '05' : 5등

 : 총 5,035 건

 

예제 1) 로또 당첨금액이 높은 순으로 정렬하라. 조회되는 값은 금액, 회차, 당첨 건수

쿼리 :

SELECT A.LOTTO_AMT AS "당첨금액"
     , A.LOTTO_NO  AS "회차" 
     , A.LOTTO_CNT AS "당첨건수"  
 FROM JUNG.LOTTO_AMT A
ORDER BY A.LOTTO_AMT DESC ;

결과 : 총 건수 5035 건이 조회되었는데 금액이 큰 순서대로 정렬하였습니다. 

ORDER BY

예제 2) 예제 1과 같이 로또 당첨금액이 높은 순으로 정렬하되 상위 5건만 조회하라.

쿼리 1 :

 => Oracle DBMS에서 제공하는 ROWNUM 을 이용.

  ※ ROWNUM 은 Oracle에서만 사용하며 DB2에서는 ROW_NUMBER()를 사용합니다.

SELECT A.LOTTO_AMT AS "당첨금액"
     , A.LOTTO_NO  AS "회차" 
     , A.LOTTO_CNT AS "당첨건수"  
 FROM (
SELECT A.LOTTO_AMT
     , A.LOTTO_NO  
     , A.LOTTO_CNT
 FROM JUNG.LOTTO_AMT A
 ORDER BY LOTTO_AMT DESC
 ) A
 WHERE ROWNUM < 6 ;

ROWNUM

그런데 만약 아래와 같이 조회하면 어떻게 될까요?

SELECT A.LOTTO_AMT AS "당첨금액"
     , A.LOTTO_NO  AS "회차" 
     , A.LOTTO_CNT AS "당첨건수"  
 FROM JUNG.LOTTO_AMT A

 WHERE ROWNUM < 6
 ORDER BY LOTTO_AMT DESC ;

결과는 아래와 같이 전혀 엉뚱한 결과가 나오는군요.

ROWNUM

쿼리의 실행 순서상 ROWNUM 이 정렬이 되기 전에 매겨지기 때문이라고 생각해 볼 수 있겠습니다.

이번에는 또 다른 방법을 한번 보시죠.

 

쿼리 2 

=> FETCH FIRST 숫자 ROWS ONLY 이용

SELECT A.LOTTO_AMT AS "당첨금액"
     , A.LOTTO_NO  AS "회차" 
     , A.LOTTO_CNT AS "당첨건수"  
 FROM JUNG.LOTTO_AMT A
ORDER BY A.LOTTO_AMT DESC
FETCH FIRST 5 ROWS ONLY ;

Oracle을 오랜만에 사용해서 안 되는 줄 알았는데 되는군요.

맨 아래에 FETCH FIRST 5 ROWS ONLY 이렇게 넣고 조회하면 5건만 조회됩니다.

결과는 쿼리 1과 동일하면서 더 간단하게 사용이 가능합니다.

FETCH FIRST

그럼 또 다른 문제를 한번 보시죠.

예제 2) 예제 1과 같이 로또 당첨금액이 높은 순으로 정렬하되 상위 5건만 조회하라. 그리고 순서를 표기하라.

위 예제 2번의 1번 쿼리를 응용해 보겠습니다.

SELECT A.LOTTO_AMT AS "당첨금액"
     , A.LOTTO_NO  AS "회차" 
     , A.LOTTO_CNT AS "당첨건수"  
     , ROWNUM AS "금액랭킹"
 FROM (
SELECT A.LOTTO_AMT
     , A.LOTTO_NO  
     , A.LOTTO_CNT
 FROM JUNG.LOTTO_AMT A
 ORDER BY LOTTO_AMT DESC
 ) A
 WHERE ROWNUM < 6 ;

=> , ROWNUM AS "금액 랭킹"만 추가했더니 원하는 결과가 나오는군요.

ROWNUM

그럼 예제 2번의 두 번째 쿼리에 동일하게 , ROWNUM AS "금액랭킹" 를 추가해도 같은 결과가 나올까요?

SELECT A.LOTTO_AMT AS "당첨금액"
     , A.LOTTO_NO  AS "회차" 
     , A.LOTTO_CNT AS "당첨건수"  
     , ROWNUM AS "금액랭킹"     
 FROM JUNG.LOTTO_AMT  A
ORDER BY A.LOTTO_AMT DESC
FETCH FIRST 5 ROWS ONLY ;

결과는 전혀 다름을 알 수 있습니다.

FETCH FIRST

즉, ROWNUM을 이용해서 순번을 매길 때는 어쩔 수 없이 아래와 같이 서브 쿼리(subquery)를 사용해야 동일한 결과가 나왔습니다.

SELECT A.LOTTO_AMT AS "당첨금액"
     , A.LOTTO_NO  AS "회차" 
     , A.LOTTO_CNT AS "당첨건수"   
     , ROWNUM AS "금액랭킹"     
FROM 
     (
SELECT A.LOTTO_AMT 
     , A.LOTTO_NO 
     , A.LOTTO_CNT 
 FROM JUNG.LOTTO_AMT  A
ORDER BY A.LOTTO_AMT DESC
FETCH FIRST 5 ROWS ONLY 
) A ;

                                    결과 :

FETCH

 

※ DESC 내림차순 정렬, 오름차순일 때는 아무것도 안 붙여도 됨

ORDER BY A.LOTTO_AMT DESC  대신 ORDER BY 1 DESC 이런 식으로 조회되는 항목의 순서의 숫자를 사용할 수 있음.

 

사실, 제가 오라클 함수를 많이 모르기 때문에 다른 방법이 있을지도 모르겠습니다.

쿼리에는 사실 정답은 없습니다. 몇 건 안될 때에는 모르지만 몇 천만 건 되는 대용량에서 여러 테이블을 조인하다 보면 쿼리는 복잡해 보이지만 실행시간은 빠를 수도 있고 간단해 보이지만 엄청 느릴 수 있습니다.

빠른 실행 속도로 최대한 간단히 작성하는 것이 정답이겠죠. 우선순위를 두자면 빠른 속도가 되겠습니다.