[SQL] Advanced SQL - TOP-n 질의 활용

Advanced SQL - TOP-n 질의 활용

1. emp 테이블에서, 급여(sal)를 가장 많이 받는 3명을 검색 하시오. (ROWNUM 활용)

해답 SQL문

-- ROWNUM : 행 번호를 컬럼으로 만들어서 붙혀줌

-- 처음 3개의 행만 출력

-- Subquery로 급여를 기준으로 먼저 정렬 시킨 뒤 3개의 행만 출력

SELECT E.*

FROM 

    (SELECT * FROM emp ORDER BY sal DESC) E

WHERE ROWNUM <= 3;

-- ORDER BY절이 들어가는 Subquery는 FROM절에만 들어올 수 있음

-- ROWNUM은 FROM절에 영향을 받음

-- 실행 결과 --

3 rows selected.

---

2. emp 테이블에서, 급여를 가장 많이 받는 사원 순으로 5 ~ 10등의 사원 정보를 검색 하시오. (ROWNUM 활용)

해답 SQL문

SELECT *

FROM (

        SELECT ROWNUM AS RANK, A.*

        FROM (

                SELECT * 

                FROM emp

                ORDER BY sal DESC

                -- emp 테이블을 sal을 기준으로 정렬을 먼저 한 테이블 A

            ) A

    ) B

    -- sal을 기준으로 정렬된 테이블 A에 ROWNUM을 별칭을 주어 컬럼으로 인식 시킨 테이블 B

WHERE RANK BETWEEN 5 AND 10;

-- ROWNUM에서 BETWEEN은 사용할 수 없음

-- ROWNUM은 작거나 같다(ROWNUM <= n) 혹은 1과 같다(ROWNUM = 1)는 연산만 가능

-- 실행 결과 --

6 rows selected.

---

3. emp 테이블에서 급여를 가장 많이 받는 2명을 검색 하시오.

단, 동일한 급여를 받는 사원이 둘 이상 있다면 함께 검색한다.

해답 SQL문

/*

- ROWNUM으로 조건을 주게 되면 행 번호로만 자름

- 행 번호는 같을 수 없기 때문에 급여가 같더라도 2번행에서 잘린다.

  이 때는 분석 함수인 RANK(), ROW_NUMBER() 혹은 DENSE_RANK() 함수를 이용해야 함

    -> ROW_NUMBER() : OVER절을 사용하여 PARTITION절을 이용해 그룹별 RANK를 할 수 있음

    -> RANK() : 중복된 값이 있다면 동일한 RANK를 부여, 그 다음 번호는 동일한 수 만큼 더한 번호부터 부여

    -> DENSE_RAN() : 중복된 RANK가 있어도 그 다음 번호로 추가

- 위 3가지 함수들은 OVER() 절과 함께 사용 해야 함

*/

SELECT empno, ename, sal, deptno

FROM (

        SELECT 

            empno, ename, sal, deptno, 

            RANK() OVER (ORDER BY sal DESC) AS rank

            /*

                OVER (ORDER BY sal DESC)

                    -> sal을 이용하여 RANK를 부여

                RANK()함수는 중복되는 값은 중복되는 RANK를 부여

            */

        FROM emp )

WHERE rank <= 2 ;

-- 실행 결과 --

3 rows selected.

---

4. emp 테이블에서, 부서별(deptno) 가장 많은 급여(sal)를 받는 사원을 한 명씩 검색하시오.

단, 동일한 급여를 받는 사원이 존재할 경우 임의의 한 명을 검색한다.

해답 SQL문

SELECT 

    empno, ename, sal, deptno

FROM (

        SELECT

            empno, ename, sal, deptno,

            ROW_NUMBER() OVER(PARTITION BY deptno ORDER BY sal DESC) AS rank

            /*

                PARTITION BY deptno ORDER BY sal DESC

                deptno로 그룹별로 RANK를 부여 하지만, 중복되는 값에도 다른 RANK를 부여

            */

        FROM emp )

WHERE rank = 1 ;

/*

    추가

    ORACLE DB 12c 부터는 아래의 FETCH, OFFSET 절을 사용할 수 있음

    FETCH FIRST 2 ROWS ONLY

    -> 2개의 행씩만 볼 수 있음

    OFFSET 10 ROWS

    -> 앞의 10개의 행을 건너 뛰고 그 뒤의 행만 볼 수 있음

*/

-- 실행 결과 --

3 rows selected.