행 제한 구문을 활용한 Top-N 쿼리,FETCH NEXT, FETCH FIRST,OFFSET,ROWS ONLY,페이지 단위로 출력,상위 10% 직원 출력하기

 

행 제한 구문을 활용한 Top-N 쿼리,FETCH NEXT, FETCH FIRST,OFFSET,ROWS ONLY,페이지 단위로 출력,상위 10% 직원 출력하기

행 제한 구문을 활용한 Top-N 쿼리

오라클 12.1 이상에서 사용가능한 행 제한 구문(row limiting clause)은 SQL문의 반환하는 행 수를 제한하고 반환되는 행의 시작 행을 지정할 수 있습니다.  이 구문을 이용하면 기존 MySQL에서 제공하는 전체 데이터 중 상위 몇개를 추츨하는 top-N 쿼리, 웹페이지 등에서 페이지 나누기 할 때 사용되는 페이징 쿼리등을 쉽게 구현해 사용할 수 있습니다. 

<기본형식>

[ OFFSET offset ROWS]  FETCH  NEXT [  rowCount| percentOfRows  ] ROWS  [ ONLY | WITH TIES ]  offset : SQL문 실행 결과가 나오기전에 SKIP할 행 수를 지정합니다. OFFSET 절은 선택 사항이며 생략하면 오프셋은 0이고 행 제한은 첫 번째 행부터 시작됩니다. 숫자 또는 숫자로 평가되는 표현식이어야 하고 음수이면 0으로 처리되고 NULL이거나 쿼리에서 반환 된 행 수보다 크면 행이 반환되지 않습니다. rowCount| percentOfRows : FETCH는 행을 추출하는 방법을 정의하는데 rowCount 값은 리턴 할 행 수, percentOfRow값은 리턴할 행의 백분율(예 : 10 PERCENT)을 지정합니다. FETCH절을 생략하면 OFFSET ~  ROWS에서 지정한 스킵대상 이후의 모든 행을 리턴 합니다. FETCH절은 ROWS 대신 ROW, NEXT 대신 FIRST 키워드를 사용할 수 있습니다. 예를 들어 offset으로부터 다음 하나를 꺼내는 아래 예문은 동일합니다. FETCH NEXT 1 ROWS or FETCH FIRST 1 ROWS FETCH NEXT 1 ROW or FETCH FIRST 1 ROW ONLY | WITH TIES : ONLY는 FETCH NEXT (또는 FIRST) 이후의 행 수 또는 행 백분율을 반환합니다. WITH TIES는 가져온 마지막 행과 동일한  ORDER BY 키 가있는 행을 반환합니다(정렬키가 같은 데이터를 보여줌). WITH TIES를 사용하는 경우 쿼리에 ORDER BY 절을 지정해야합니다. 그렇지 않으면 쿼리가 추가 행을 반환하지 않습니다.

기본 문법만 보면 이해가 어려우니 아래에서 OFFSET, FETCH절과 관련된 다양한 예문들을 실습해 보겠습니다.

5.8.1 처음 N개 행을 조회하기 :  FETCH NEXT, FETCH FIRST

EMP 테이블에서 처음 3개의 행을 조회하세요.

SELECT ENAME, SAL FROM EMP   FETCH  FIRST 3 ROWS ONLY;  ❶ 아래와 동일 합니다. SELECT ENAME, SAL FROM EMP  FETCH  NEXT 3 ROWS ONLY; ❷

<실행 결과>

	ENAME	SAL
1	SMITH	800
2	ALLEN	1600
3	WARD	1250

ROWS 대신 ROW를 사용해도 부방하며 ❶ EMP 테이블에서 SELECT * FROM EMP 했을때의 상의 3개를 추출하기 위해 FETCH FIRST를 사용했습니다. ❷ FETCH NEXT 도 동일하게 작동 합니다.

5.8.2 처음 N개 행 스킵하고 전체행을 조회하기 :  OFFSET ROWS

EMP 테이블에서 처음 1개의 행을 스킵하고 전체 행을 조회하세요.

SELECT ENAME, SAL FROM EMP  ❶OFFSET 1 ROWS;

<실행 결과>

	ENAME	SAL
1	ALLEN	1600
2	WARD	1250
...	…...	…...
13	MILLER	1300

❶ OFFSET 1 ROWS로 처음 하나의 행을 스킵하도록 했고 FETCH절을 생략해서 모든 그 다음 모든행이 추출되도록 했습니다.

5.8.3 급여 상위 3명을 스킵하고 다음 상위  7명 정보 출력하기 : OFFSET, FETCH, ROWS ONLY

EMP 테이블에서 급여 상위 3명을 스킵하고 다음 상위  7명의  직원명과 급여를 출력하세요. 마지막 출력되는 직원과 같은 급여가 있다고 해도 무조건 7개만 출력합니다.

SELECT ENAME, SAL FROM   EMP ORDER BY SAL DESC  ❶  OFFSET 3 ROWS          ❷  FETCH NEXT 7              ❸ ROWS ONLY;                 ❹

<실행 결과>

	ENAME	SAL
1	JONES	2975
2	BLAKE	2850
3	CLARK	2450
4	ALLEN	1600
5	TURNER	1500
6	MILLER	1300
7	WARD	1250

❶ 급여 내림차순으로 출력합니다.

❷ OFFSET 키워드를 사용해 3개 행을 스킵합니다. 

❸ 급여 상위 4등부터 다음 7개의 행을 추출합니다.

❹ ROW ONLY 키워드를 사용해 마지막 행인 WARD의 급여 1250과 같은 직원이 있더라도 7개 행에서 잘라서 보여줍니다. 

5.8.4 급여 상위 3명을 스킵하고 다음 상위  7명 정보 출력하기 : OFFSET, FETCH, ROWS WITH TIES

EMP 테이블에서 급여 상위 3명을 스킵하고 다음 상위  7명의  직원명과 급여를 출력합니다. 마지막 출력되는 직원과 급여가 같은 직원 모두를 출력합니다.

SELECT ENAME, SAL FROM    EMP ORDER  BY SAL DESC ❶ OFFSET 3 ROWS          ❷ FETCH   NEXT 7            ❸ ROWS   WITH TIES;      ❹

<실행 결과>

	ENAME	SAL
1	JONES	2975
2	BLAKE	2850
3	CLARK	2450
4	ALLEN	1600
5	TURNER	1500
6	MILLER	1300
7	WARD	1250
8	MARTIN	1250

❶ 급여 내림차순으로 출력합니다.

❷ OFFSET 키워드를 사용해 3개 행을 스킵합니다. 

❸ 급여 상위 4등부터 다음 7개의 행을 추출합니다.

❹ ROW WITH TIES 키워드를 사용해 마지막 행인 WARD의 급여 1250과 같은 직원이 있으면 모두 보여 줍니다.

5.8.5 급여 상위 10% 직원 출력하기 : FETCH, PERCENT ROWS ONLY

EMP 테이블에서 급여 상위 10%에 해당하는 직원명과 급여를 출력합니다. 마지막 행 데이터와 같은 급여를 받는 직원이 있더라도 자릅니다.

SELECT ENAME, SAL FROM   EMP ORDER BY SAL DESC           ❶ FETCH  FIRST 10 PERCENT ❷ ROWS ONLY;                          ❸

<실행 결과>

	ENAME	SAL
1	KING	5000
2	FORD	3000

❶ 급여 내림 차순으로 정렬합니다. 

❷ OFFSET값이 생략 되었으므로 0개의 행을 스킵합니다.즉  처음 10% 사원을 추출 합니다.

❸ 마지막 행인 FORD의 급여 3000과 같은 직원이 있더라도 잘라서 보여줍니다.

5.8.6 급여 상위 10% 직원 출력하기 : FETCH, PERCENT ROWS WITH TIES

EMP 테이블에서 급여 상위 10%에 해당하는 직원명과 급여를 출력합니다. 마지막 행 데이터와 같은 급여를 받는 직원이 있다면 모두 출력합니다.

SELECT ENAME, SAL FROM   EMP ORDER BY SAL DESC          ❶ FETCH FIRST 10 PERCENT ❷ ROWS WITH TIES;                ❸

<실행 결과>

	ENAME	SAL
1	KING	5000
2	FORD	3000
3	SCOTT	3000

❶ 급여 내림 차순으로 정렬합니다. 

❷ 처음 10% 사원을 추출 합니다. 

❸ 마지막 행인 FORD의 급여 3000과 같은 직원이 있으면 모두 보여 줍니다.

5.8.7 페이지 단위로 직원정보 출력하기 : FETCH, PERCENT ROWS ONLY

이런 쿼리문들은 웹페이지 개발시 페이징 처리할 때 유용하니 잘 기억해주세요.

EMP 테이블에서 급여 내림차순으로 직원명, 급여를 출력합니다. 이때 한 페이지에 3개 행을 출력합니다. 1번째 페이지 데이터를 출력하는데 마지막 행 데이터와 같은 급여를 받는 직원이 있다면 출력하지 않습니다.

SELECT ENAME, SAL FROM    EMP ORDER BY SAL DESC ❶ FETCH  FIRST 3           ❷ ROWS ONLY;                ❸

<실행 결과>

	ENAME	SAL
1	KING	5000
2	SCOTT	3000
3	FORD	3000

❶ 급여 내림 차순으로 출력 합니다.

❷ 처음 3개의 행을 리턴 합니다. OFFSET이 생략 되었으므로 0 입니다. 즉 스킵할 행이 없으니 처음 행부터 3개가 리턴 됩니다.

❸ 마지막 행인 FORD의 급여와 같은 직원이 있더라도 출력하지 않습니다.

5.8.8 페이지 단위로 직원정보 출력하기 : OFFSET, FETCH, PERCENT ROWS ONLY

EMP 테이블에서 급여 내림차순으로 직원명, 급여를 출력합니다. 이때 한 페이지에 3개 행을 출력합니다. 2번째 페이지 데이터를 출력하는데 마지막 행 데이터와 같은 급여를 받는 직원이 있다면 출력하지 않습니다.

SELECT ENAME, SAL FROM   EMP ORDER BY SAL DESC ❶ OFFSET 3 ROWS         ❷ FETCH NEXT 3             ❸ ROWS ONLY;                ❹

<실행 결과>

	ENAME	SAL
1	JONES	2975
2	BLAKE	2850
3	CLARK	2450

❶ 급여 내림 차순으로 정렬 합니다.

❷ 처음 3개의 행을 스킵 합니다. 

❸ 그 다음 3개의행을 리턴 합니다.

❹ CLARK의 급여 2450과 같은 직원이 있더라도 출력하지 않습니다.

 

#오라클 #SQL페이징 #TopN쿼리 #오라클페이징 #FETCH #TopN쿼리​