[6주차] 25. 상위 N개의 데이터를 추출하는 Top N 쿼리(Inline View, 특수컬럼)***

Inline View

from 절에 오는 서브쿼리(select-from)의 결과로 나온 뷰를 인라인뷰라고 한다. 뷰는 저장장치 내에 물리적으로 존재하진 않지만 사용자에게 있는 것처럼 보여지는 가상의 테이블이다. 인라인뷰는 복잡한 SQL 쿼리를 작성할 때 사용되며, 서브쿼리에서 반환된 데이터는 데이터베이스에 저장되지 않기 때문에 임시 테이블이라고 부르기도 한다.

Top N 쿼리 란?

상위 n개의 데이터를 추출하는 쿼리를 의미하며, 이를 구현하고자 한다면 Inline View, 그리고 다음에 오는 특수컬럼 중에서도 rownum을 이용해야 한다.

특수컬럼(pseudo 컬럼) : 자동으로 추가되어 존재하는 컬럼

1. rowid
행(모든 레코드)을 구분하기 위한 고유 문자열(unique)로 DB 전체에서 유일 무이하다.(인덱스에 사용)
자주 사용하지는 않는 것이므로 존재한다는 것만 알고 넘어가자.

select rowid, name
from rowtest;

ROWID              NAME
------------------ --------------------
AAAR+fAAHAAAAEmAAB 원더걸스
AAAR+fAAHAAAAEmAAC 블랙핑크
AAAR+fAAHAAAAEmAAD 애프터스쿨
AAAR+fAAHAAAAEmAAE 비스트
AAAR+fAAHAAAAEmAAF 레드벨벳
AAAR+fAAHAAAAEmAAG 티아라
AAAR+fAAHAAAAEmAAH EXO

2. rownum

테이블에 '레코드가 들어간 순서'를 기억하고 있는 특수 컬럼으로 시스템은 이것을 토대로 들어온 순서를 관리한다.

레코드가 들어온 순서대로 숫자가 1부터 부여되며, 단순히 정렬(order by)만 하면 이 값은 바뀌지 않는다.

이 값이 변경될 수 있는 경우를 기억해야 하는데, 내가 알고있는 수준에서는 알고 있는 경우가 딱 2가지이다.

1) 중간에 있던 레코드가 삭제되는 경우, 기존에 들어온 상대적인 순서에 따라 1부터 다시 부여된다.

2) Top N 쿼리에서 인라인뷰를 이용해 새롭게 select의 결과를 얻은 경우 맨 위부터 1,2,3,, 순으로 다시 부여한다.

select rownum, name, jumsu
from rowtest;

    ROWNUM NAME            JUMSU
---------- ---------- ----------
         1 원더걸스           20
         2 블랙핑크           50
         3 애프터스쿨         80
         4 비스트             70
         5 레드벨벳           10
         6 티아라             40
         7 EXO                60
         8 에이프릴           60
         
--order by로 단순히 정렬을 한 경우 바뀌지 않는 행별 rownum
select rownum, name, jumsu
from rowtest
order by jumsu desc;

    ROWNUM NAME            JUMSU
---------- ---------- ----------
         3 애프터스쿨         80
         4 비스트             70
         8 에이프릴           60
         7 EXO                60
         2 블랙핑크           50
         6 티아라             40
         1 원더걸스           20
         5 레드벨벳           10
         
--위의 결과를 한 번 더 select문을 거치게 해, 위의 정렬된 순서대로 1부터 rownum 재부여 되도록 유도
select rownum as rank, name, jumsu
from (select rownum, name, jumsu from rowtest order by jumsu desc);
      RANK NAME            JUMSU
---------- ---------- ----------
         1 애프터스쿨         80
         2 비스트             70
         3 에이프릴           60
         4 EXO                60
         5 블랙핑크           50
         6 티아라             40
         7 원더걸스           20
         8 레드벨벳           10

---

1. 특수컬럼 rownum을 이용한 rank 부여, 원하는 등수만 가져오는 방법

- 비교값이 동일해도 같은 등수를 부여하지 않는 방식

ex) 비교 값이 같은 경우가 존재해도 무조건 1등, 2등, 3등, 4등, 5등 으로 등수를 부여하여 중복되는 등수가 존재하지 않음

-- 1단계, 점수 순으로 정렬
select rownum, name, jumsu
from rowtest
order by jumsu desc;

    ROWNUM NAME            JUMSU
---------- ---------- ----------
         3 애프터스쿨         80
         4 비스트             70
         8 에이프릴           60
         7 EXO                60
         2 블랙핑크           50
         6 티아라             40
         1 원더걸스           20
         5 레드벨벳           10
         
-- 2단계, 정렬된 순서대로 rownum 1부터 재부여, !! 만약 여기서 rownum에 별칭을 사용하지 않았다면 바로 where 절에 rownum in(2,3) 추가해서 바로 아래의 결과를 유도 가능 
select rownum as rank, name, jumsu
from (select rownum, name, jumsu
from rowtest
order by jumsu desc);

      RANK NAME            JUMSU
---------- ---------- ----------
         1 애프터스쿨         80
         2 비스트             70
         3 에이프릴           60
         4 EXO                60
         5 블랙핑크           50
         6 티아라             40
         7 원더걸스           20
         8 레드벨벳           10

-- 부여한 별칭은 바로 where 절에서 사용할 수 없으므로 한번 더 select 문을 거치게 함 
-- 3단계, where절을 이용해서 원하는 등수의 row만 필터링
select rank, name, jumsu
from (select rownum as rank, name, jumsu
from (select rownum, name, jumsu
from rowtest
order by jumsu desc))
where rank in(2,3);

      RANK NAME            JUMSU
---------- ---------- ----------
         2 비스트             70
         3 에이프릴           60

2. dense_rank() over() 함수를 이용한 rank 부여, 원하는 등수만 가져오는 방법

- 비교값이 동일해 같은 등수가 N개이면, 다음 등수로 N+1을 부여하는 방식, 가장 일반적인 방법

* over( 정렬기준 ) 을 해주면  from 절 뒤에서 oder by 해주지 않아도 정렬기준대로 정렬된다.
  왜냐하면 rank 순으로 정렬되어지기 때문에 

* over( 정렬기준 ) 에서 정렬기준에 인자 2개 이상을 넣을 수 있는데 이러면 정렬은 내가 원하는 대로 나오지만
  순위 값은 중복값 없이 1,2,3,4,5 이렇게 나올 수 있으니 이를 알고 사용하자.

ex) 비교 값이 같아 같은 등수가 존재하게 되면 -> 1등, 2등, 3등, 3등, 4등 이런식의 등수부여

--1단계. dense_rank() over(랭크를 매길지에 대한 기준) 함수를 이용해 rank 별칭을 가지는 열 추가
select rownum, name, jumsu, dense_rank() over(order by jumsu desc) rank
from rowtest;

    ROWNUM NAME                      JUMSU       RANK
---------- -------------------- ---------- ----------
         3 애프터스쿨                   80          1
         4 비스트                       70          2
         8 에이프릴                     60          3
         7 EXO                          60          3
         2 블랙핑크                     50          4
         6 티아라                       40          5
         1 원더걸스                     20          6
         5 레드벨벳                     10          7
         
--2단계. 별칭을 지정했다면 바로 사용할 수 없으므로 select 문을 똑같이 거치며 where절 적용           
select name, jumsu, rank
from (select rownum, name, jumsu, dense_rank() over(order by jumsu desc) rank
from rowtest)
where rank between 2 and 3;

NAME            JUMSU       RANK
---------- ---------- ----------
비스트             70          2
EXO                60          3
에이프릴           60          3

3. rank() over() 함수를 이용한 rank 부여, 원하는 등수만 가져오는 방법

- 비교값이 동일해 같은 등수가 N개이면, 다음 등수로 N+1을 부여하지 않고, N개 등수를 건너뛴 등수가 부여되는 방식

* over( 정렬기준 ) 을 해주면  from 절 뒤에서 oder by 해주지 않아도 정렬기준대로 정렬된다.
  왜냐하면 rank 순으로 정렬되어지기 때문에 

* over( 정렬기준 ) 에서 정렬기준에 인자 2개 이상을 넣을 수 있는데 이러면 정렬은 내가 원하는 대로 나오지만
  순위 값은 중복값 없이 1,2,3,4,5 이렇게 나올 수 있으니 이를 알고 사용하자.

ex) 비교 값이 같아 같은 등수가 존재하게 되면 -> 1등, 2등, 3등, 3등, 5등 이런식의 등수부여(4등이 존재하지 않음)

--1단계. rank() over(랭크를 매길지에 대한 기준) 함수를 이용해 rank 별칭을 가지는 열 추가
select name, jumsu, rank() over(order by jumsu desc) rank
from rowtest;

NAME                      JUMSU       RANK
-------------------- ---------- ----------
애프터스쿨                   80          1
비스트                       70          2
에이프릴                     60          3
EXO                          60          3
블랙핑크                     50          5
티아라                       40          6
원더걸스                     20          7
레드벨벳                     10          8

--2단계. 별칭을 지정했다면 바로 사용할 수 없으므로 select 문을 똑같이 거치며 where절 적용
select name, jumsu, rank
from(select name, jumsu, rank() over(order by jumsu desc) rank
from rowtest)
where rank between 2 and 3;

NAME                      JUMSU       RANK
-------------------- ---------- ----------
비스트                       70          2
EXO                          60          3
에이프릴                     60          3