행 제한 구문을 활용한 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쿼리​

오라클 SQL, 서브쿼리, WITH 구문(서브쿼리 팩토링)

 

오라클 SQL, 서브쿼리, WITH 구문(서브쿼리 팩토링)

WITH 구문은 복잡한 SQL문을 단순화 시키고  반복을 줄이기 위해 사용합니다. 

위 쿼리문에서 박스 영역은 동일하게 반복되는 SQL문장 입니다. 이를 WITH 구문으로 정의해서 공통으로 사용할 수 있습니다.

쿼리문이 단순해지고 반복도 즐었습니다. 물론 성능도 좋아집니다.^^

오라클9i R2 이후 사용 가능한 WITH 구문은 인라인 뷰로 처리되거나 임시 테이블로 해석 될 수 있습니다. 복잡한 SQL에서 동일 쿼리블록을 반복 사용한다면 해당 블록에 이름을 부여해 정의하고 재사용하는 편이 낫습니다. 쿼리문 코딩양도 줄이고 성능도 향상시킬 수 있기 때문입니다. 자주 실행되는 SQL 구문이라면 한 번만 파싱parsing되어 처리되므로 수행 성능상 장접도 있습니다. SQL문에서 서브 쿼리에 대한 반복적 인 참조가 매번 다시 쿼리되는 것이 아니라 WITH문으로 정의한 임시 테이블에서 쉽게 검색되므로 더 효율적일 수 있다는 것입니다.

WITH구문을 서브 쿼리 분해절(sub query factoring clause)이라고도 합니다.

<사용형식>

WITH query_name AS (   sql_query ) SELECT * FROM query_name query_name : sql_query문의 이름,임시테이블 이름. sql_query : WITH문으로 정의될 SQL 쿼리.

WITH문을 다중으로 사용할 수 있으며 형식은 다음과 같습니다.

WITH query_name1 AS (   sql_query1 ) ,           query_name2 AS (   sql_query2 ) ,           query_name3 AS (   sql_query3 )  SELECT ...

직원 목록과 해당 부서원 수를 출력하는 예문을 만들겠습니다. 인라인뷰, WITH 구문을 사용합니다.  FROM절 내의 SELECT 쿼리문이 한곳에서만 사용된다면 인라인 뷰나 WITH문 어느것을 사용해도 관계없겠지만 반복된다면 WITH문을 사용 하는것이 좋습니다.

5.7.1 직원 목록과 해당 부서원 수 출력하기 : 인라인뷰

EMP 테이블에서 직원 목록을 출력하면서 그직원이 속한 부서의 부서원 수를 같이 출력합시다. 인라인뷰를 이용하여 작성하세요.

SELECT E.ENAME              , EMPS.EMP_COUNT  FROM    EMP E,          ( ❶ SELECT DEPTNO                        , COUNT(*) AS EMP_COUNT           FROM    EMP           GROUP BY DEPTNO) EMPS WHERE  E.DEPTNO = EMPS.DEPTNO ❷;

<실행 결과>

	ENAME	EMP_COUNT
1	SMITH	5
2	ALLEN	6
...	...	...
13	MILLER	2

❶ 인라인 뷰에서 부서별로 GROUP BY한 결과를 EMPS라고 했습니다. 

❷ EMPS.DETNO와 EMP.DEPTNO를 조인하여 부서별 사원수를 조회 합니다.

조인은 양쪽다 일치하는 데이터만 조회되므로 부서코드값이 NULL인 “KING” 직원은 출력되지 않습니다.

5.7.1 직원 목록과 해당 부서원 수 출력하기 : WITH 구문

EMP 테이블에서 직원 목록을 출력하면서 그직원이 속한 부서의 부서원 수를 같이 출력합시다. WITH 구문으로 작성하세요.

WITH EMPS AS (   SELECT DEPTNO, COUNT(*) AS EMP_COUNT ❶   FROM    EMP   GROUP BY DEPTNO ) SELECT E.ENAME, EMPS.EMP_COUNT  FROM     EMP E,  ❷ EMPS  WHERE  E.DEPTNO = EMPS.DEPTNO;

<실행 결과>

	ENAME	EMP_COUNT
1	SMITH	5
2	ALLEN	6
...	...	...
13	MILLER	2

❶ WITH문에서 DEPTNO별로 GROUP BY한 결과를 EMPS라고 칭합니다. ❷ FROM 절의 테이블명 이 오는 자리에 WITH로 정의한 임시테이블의 이름을 기술하여 쿼리를 작성 합니다.​

 

#오라클 #서브쿼리 #SQL #WITH #WITH구문 #서브쿼리리팩토링