고급쿼리) TOP-N 분석 (rownum)

안녕하세요, 코린이의 코딩 학습기 채니 입니다.

 

개인 포스팅용으로 내용에 오류 및 잘못된 정보가 있을 수 있습니다.

---

고급쿼리

 

TOP-N 분석

- 실제 컬럼/가상 컬럼에 대해서 정렬 후 top 또는 bottom에서 n개의 레코드를 추려낸 쿼리

 

☞ rownum

- 테이블 레코드에 대해서 부여하는 식별번호

- 오라클에서 insert 시에 자동으로 부여

① where 절을 통해 결과집합이 변경된 경우, rownum 새로 부여

② inline-view를 통해 조회된 경우, rownum 새로 부여

 

select
    rownum,
    e.*
from
    employee e;

rownum에서 부여해준 순서와 행 번호가 동일한 것을 확인할 수 있습니다.

그렇다면 조회된 순서에 따라서 값이 부여되는 건지 생각 들 수도 있지만, 그렇지는 않습니다.

 

select
    rownum,
    e.*
from
    employee e
order by
    emp_name;

이름 순으로 order by했을 때 rownum을 확인해보니, 뒤죽박죽인 것을 확인할 수 있습니다.

이를 통해 rownum은 조회 순이 아닌 행마다 부여된 숫자라는 것을 알 수 있습니다.

 

select
    rownum,
    e.*
from
    employee e
where
    dept_code = 'D5';

where절을 통해 결과집합이 변경 되었더니, rownum이 새로 부여된 것을 확인할 수 있습니다.

 

급여가 높은 순으로 5명 조회

select
        rownum as old, emp_name, salary
    from
        employee
    order by
        salary desc

where절이나 inline-view가 없기 때문에 order by를 하면 위처럼 rownum이 뒤죽박죽될 것입니다.

salary는 내림차순으로 잘 정렬 되었지만 여기서 5명을 추려내기는 어렵죠.

이런 경우 inline-view를 사용해서 rownum을 다시 부여해주면 추려낼 수 있을 것입니다.

 

select
    rownum new,
    e.*
from (
    select
        rownum as old, emp_name, salary
    from
        employee
    order by
        salary desc
) e;

이렇게 inline-view를 사용해서 먼저 salary를 내림차순으로 정렬 해준 후 정렬된 행에 따라 rownum을 다시 부여해준 후 랭킹을 추려낼 수 있습니다.

 

select
    rownum new,
    e.*
from (
    select
--        rownum as old, 
        emp_name, salary
    from
        employee
    order by
        salary desc
) e
where
    rownum between 1 and 5;

이렇게 급여가 높은 다섯 명을 추려 내었습니다.

 

최근 입사한 10명 조회 (사번, 사원명, 입사일)

select
    *
from (
    select
        emp_id,
        emp_name,
        hire_date
    from
        employee
    order by
        hire_date desc
)
where
    rownum <= 10;

 

급여가 높은 순으로 6~10위 조회

select 
    *
from (
    select
        rownum rnum,
        e.*
    from (
        select
            emp_id,
            emp_name,
            salary
        from
            employee
        order by
            salary desc
    ) e
) e
where
    rnum between 6 and 10;

위에서 했던 것처럼 rownum between 6 and 10;을 하면 될 것 같지만 그렇게 하면 결과가 나오지 않습니다.

offset이 발생하여 접근이 불가하기 때문이죠.

따라서 inline-view를 또 생성하여 2번째 inline-view의 rnum을 가져와 정렬을 해야 합니다.

1부터 순차 접근을 해야만 같은 레벨에서 rownum을 사용할 수 있습니다.

 

※ rownum 새로 부여 시, where절이 끝날 때 부여 완료

※ 1부터 순차접근 시만 같은 레벨에서 사용 가능