[SQL] 탑 앤 쿼리 (TOP N QUERY)

TOP N QUERY

-페이징 처리를 효과적으로 수행하기 위해 사용

-전체 결과에서 특정 N개 추출

예) 성적 상위자 3명

 

 

TOP-N 행 추출 방법

1.ROWNUM

2.RANK

3.FETCH

4.TOP N(SQL Server)

 

 

1.ROWNUM

-출력된 데이터 기준으로 행 번호 부여

-절대적인 행 번호가 아닌 가상의 번호이므로 특정 행을 지정할 수 없음(=연산 불가)

-첫번째 행이 증가한 이후 할당되므로 '>' 연산 사용 불가(0은 가능)

EX) ROWNUM 출력 형태
SELECT ROWNUM, EMP.*
  FROM EMP
 WHERE SAL >=1500;

 

 

ROWNUM 잘못된 사용

#ROWNUM 잘못된 사용1
#크다 조건 전달 불가
SELECT * 
FROM EMP
WHERE ROWNUM > 1


#ROWNUM 잘못된 사용2
#항상 불변하는 절대적 번호가 아니므로 '='연산자 단독 전달 불가
SELECT * 
FROM EMP
WHERE ROWNUM = 4

 

 

ROWNUM 올바른 사용

#ROWNUM 올바른 사용법
SELECT EMPNO, ENMAE, DPETNO, SAL
FROM  EMP
WHERE ROWNUM <=5;

 

 

 

예제) EMP 테이블에서 급여가 높은 순서대로 상위 5명의 직원 정보 출력

잘못된 예시)

#EX)EMP 테이블에서 급여가 높은 순서대로 상위 5명의 직원 정보 출력
#잘못된 예
#SELECT EMPNO, ENAME, DEPTNO, SAL
#FROM EMP
#WHERE ROWNUM <= 5
#ORDER BY SAL DESC;

#실제로 상위 5명 출력 안됨
#추출 원리 : WHERE절에 의해 먼저 5개를 추출 뒤 이 결과 집합에 대해 정렬 수행

 

올바른 예시)

#해결 : 먼저 서브쿼리를 사용하여(인라인뷰)SAL에 대해 내림차순 정렬을 해놇고 5개 가져옴
SELECT *
  FROM (SELECT *
          FROM EMP
          ORDER BY SAL DESC)
 WHERE ROWNUM <= 5
 ORDER BY SAL DESC;
 #즉 ROWNUM이 결정되기 전에 먼저 데이터 정렬 순서를 바꿔 놓음

 

EMPNO	ENAME	JOB	SAL
0005	민우5	풀스택 개발자	1000
0004	민우4	백앤드	800
0003	민우3	프론트 앤드	600
0002	민우2	퍼블리셔	400
0001	민우1	디자이너	200

 

 

예제) EMP 테이블에서 급여가 높은 순서대로 4 ~ 6번째 해당하는 직원 정보 출력

잘못된 예시)

#ROWNUM 시작값(1)이 정의되지 않았으므로 1을 건너띄고 그 다음 행번호에 대한 추출 불가
SELECT *
  FROM (SELECT * FROM EMP ORDER BY SAL DESC)
 WHERE ROWNUM BETWEEN 4 AND 6
 ORDER BY SAL DESC;

 

 

올바른 예시 1.) 인라인 뷰에서 각 행마다의 순위를 직접 부여

#인라인 뷰에서 각 행마다의 순위를 직접 부여
SELECT *
  FROM (SELECT ROWNUM AS RN, A.*
          FROM (
                SELECT *
                FROM EMP
                ORDER BY SAL DESC
                ) A
        ) B
 WHERE RB BETWEEN 4 AND 6
 ORDER BY SAL DESC;
 #서브쿼리를 통해 얻은 결과에 ROWNUM을 다시 부여하여 새로운 테이블인것 처럼 사용(인라인 뷰)

 

 

 

2.RANK절

올바른 예시 2.) 윈도우 함수의 RANK 사용

#윈도우 함수의 RANK 사용
SELECT *
  FROM (SELCET EMP.*,
               RANK() OVAER(ORDER BY SAL DESC) AS RN
               FROM EMP) A
 WHERE RN BETWEEN 4 AND 6
 ORDER BY SAL DESC;

EMPNO	ENAME	JOB	SAL
0005	민우5	풀스택 개발자	1000
0004	민우4	백앤드	800
0003	민우3	프론트 앤드	600
0002	민우2	퍼블리셔	400
0001	민우1	디자이너	200

 

 

3.FETCH절

-출력될 행의 수를 제한하는 절

-ORACLE 12C 이상부터 제공(이전버전에는 ROWNUM 주로 사용)

-SQL-Server 사용 사능

-ORDER BY절 뒤에 사용(내부 파싱 순서도 ORDER BY 뒤)

#문법
SELECT
  FROM
 WHERE
 GROUP BY
HAVING
 ORDER BY
OFFSET N { ROW | ROWS }
 FETCH {FIRST | NEXT } N { ROW | ROWS } ONLY

-OFFSET : 건너뛸 행의 수 

                  ex) 성적 높은순 1등 제외, 나머지 3명

-N : 출력할 행의수

-FETCH : 출력할 행의 수를 전달하는 구문

-FIRST : OFFSET을 을 쓰지 않았을 때 처음부터 N행 출력 명령

-NEXT : OFFSET을 사용했을 경우 제외한 행 다음부터 N행 출력 명령

-ROW | ROWS : 행의 수에 따라 하나일 경우 단수, 여러값이면 복수형(특별히 구분하지 않아도 됨)

 

#EX) EMP에서 SAL 순서대로 상위 5명(19C에서 실행)
SELECT EMPNO, ENAME, JOB, SAL
FROM EMP
ORDER BY SAL DESC FETC FIRST 5 ROWS ONLY;

EMPNO	ENAME	JOB	SAL
0005	민우5	풀스택 개발자	1000
0004	민우4	백앤드	800
0003	민우3	프론트 앤드	600
0002	민우2	퍼블리셔	400
0001	민우1	디자이너	200

 

 

#EX) EMP테이블에서 급여가 높은 순서대로 4~5번째 해당하는 직원 정보를 출력
SELECT EMPNO, ENAME, JOB, SAL
  FROM EMP
 ORDER BY SAL DESC
OFFSET 3 ROW                     #3줄 건너뛰고
 FETCH FIRST 2 ROW ONLY;         #2줄을 출력해줘

EMPNO	ENAME	JOB	SAL
0002	민우2	퍼블리셔	400
0001	민우1	디자이너	200

 

 

 

---

SQL-Server 에서의 TOP N 쿼리

-SQL Server 에서의 상위 N개 행 추출 문법

-서브쿼리 사용 없이 하나의 쿼리로 정렬된 순서대로 상위 N개 추출 가능

-WITH TIES를 사용하여 동순위까지 함께 출력 가능

#문법
SELECT TOP N 컬럼1, 컬럼2, ....
  FROM 테이블명
 ORDER BY 정렬컬럼명 [ASC | DESC] ....

#EX) EMP테이블의 상위 급여자 2명 출력(SQL Server에서 수행)
SELECT TOP 2 ENAME, SAL
  FROM EMP
 ORDER BY SAL DESC;

 

ENAME	SAL
사장님	5000
사원1	3000

 

 

#EX) EMP테이블의 상위 급여자 2명 출력(SQL Server에서 수행)
#SAL은 큰 순서대로 5000,3000,3000 이라서 3000이 공동 2위이지만 ,TOP 2는 2개만 출력한다
#하지만 WITH TIES를 사용하면 동순위 행도 함께 출력 가능
SELECT TOP 2 WITH TIES ENAME, SAL
  FROM EMP
ORDER BY SAL DESC;

ENAME	SAL
사장님	5000
사원1	3000
사원2	3000