BACK END/DataBase
4일차(06.15) topN절
라미보
2022. 6. 19. 19:17
● Inline View(Top N 절)
정의
from 절에 select 구문이 나온다.
특수 컬럼(pseudo 컬럼)
▪ rownum
테이블에 레코드가 들어간 순서를 기억하고 있는 특수 컬럼(inser를 삽입한 순서대로)
시스템은 이것을 토대로 들어온 순서를 관리한다.
▪ rowid
행을 구분하기 위한 고유 문자열(unique) =모든 레코드를 구분하기 위한
DB 전체에서 유일 무이하다.(인덱스에 사용)
- rownum, rowid에 대한 실습
SQL> drop table rowtest;
drop table rowtest
*
1행에 오류:
ORA-00942: 테이블 또는 뷰가 존재하지 않습니다
SQL> col name for a20
SQL> create table rowtest(
2 name varchar2(30),
3 jumsu number
4 );
테이블이 생성되었습니다.
SQL> insert into rowtest values('소녀시대', 30 );
1 개의 행이 만들어졌습니다.
SQL> insert into rowtest values('원더걸스', 20 );
1 개의 행이 만들어졌습니다.
SQL> insert into rowtest values('블랙핑크', 50 );
1 개의 행이 만들어졌습니다.
SQL> insert into rowtest values('애프터스쿨', 80 );
1 개의 행이 만들어졌습니다.
SQL> insert into rowtest values('비스트', 70 );
1 개의 행이 만들어졌습니다.
SQL> insert into rowtest values('레드벨벳', 10 );
1 개의 행이 만들어졌습니다.
SQL> insert into rowtest values('티아라', 40 );
1 개의 행이 만들어졌습니다.
SQL> insert into rowtest values('EXO', 60 );
1 개의 행이 만들어졌습니다.
SQL> commit;
커밋이 완료되었습니다.
SQL> select rownum, name, jumsu from rowtest;
ROWNUM NAME JUMSU
------ ------------ -----
1 소녀시대 30
2 원더걸스 20
3 블랙핑크 50
4 애프터스쿨 80
5 비스트 70
6 레드벨벳 10
7 티아라 40
8 EXO 60
-- rowtest 테이블에 인서트시 '소녀시대' 데이터가 가장 먼저 인서트 되었다.
SQL> select rownum, name, jumsu
2 from rowtest
3 order by jumsu desc;
ROWNUM NAME JUMSU
------ ------------ -----
4 애프터스쿨 80
5 비스트 70
8 EXO 60
3 블랙핑크 50
7 티아라 40
1 소녀시대 30
2 원더걸스 20
6 레드벨벳 10
--정렬을 해도 로우넘은 순서가 바뀌지 않는다.
--애프터스쿨은 삽입할때 4번째로 들어왔다.
Q. 원더걸스의 rownum은 얼마인가요?
SQL> select rowid, rownum, name, jumsu
2 from rowtest;
ROWID ROWNUM NAME JUMSU
------------------ ------ ------------ -----
AAASEEAAHAAAAFuAAA 1 소녀시대 30
AAASEEAAHAAAAFuAAB 2 원더걸스 20
AAASEEAAHAAAAFuAAC 3 블랙핑크 50
AAASEEAAHAAAAFuAAD 4 애프터스쿨 80
AAASEEAAHAAAAFuAAE 5 비스트 70
AAASEEAAHAAAAFuAAF 6 레드벨벳 10
AAASEEAAHAAAAFuAAG 7 티아라 40
AAASEEAAHAAAAFuAAH 8 EXO 60
--원더걸스의 rownum은 2이다.
Q.소녀시대 삭제코드
SQL> delete from rowtest where name ='소녀시대';
1 행이 삭제되었습니다.
Q.소녀시대가 사라진 후 원더걸스의 rownum는 얼마인가요?
SQL> select rownum, name, jumsu
2 from rowtest;
ROWNUM NAME JUMSU
------ ------------ -----
1 원더걸스 20
2 블랙핑크 50
3 애프터스쿨 80
4 비스트 70
5 레드벨벳 10
6 티아라 40
7 EXO 60
--삭제하고 순서가 바뀔 수 있다.
-- rownum은 그때 그때 수시로 바뀔수 있지만, 인서트 된 순서는 흐트러지지 않는다.
--※중요 : order by를 사용해도 rownum은 변함이 없다.
SQL> select rownum, name, jumsu from rowtest order by jumsu desc;
ROWNUM NAME JUMSU
------ ------------ -----
3 애프터스쿨 80
4 비스트 70
7 EXO 60
2 블랙핑크 50
6 티아라 40
1 원더걸스 20
5 레드벨벳 10
▪ rownum을 인위적으로 변경하려면 인라인(Inline) 뷰를 사용해야 한다.
- Inline View 필수 요소
from 절에 사용되는 서브 쿼리(select 절)
사용 가능한 비교 연산자 : <, <=
Q.점수 30~70 사이의 레코드 값을 조회
SQL> select rownum,name,jumsu
2 from rowtest
3 where jumsu >=30 and jumsu<=70;
ROWNUM NAME JUMSU
------ ------------ -----
1 블랙핑크 50
2 비스트 70
3 티아라 40
4 EXO 60
Q.rowtest name, jumsu 두개의 칼럼을 jumsu를 내림차순으로 정렬
SQL> select name, jumsu
2 from rowtest
3 order by jumsu desc;
NAME JUMSU
------------ -----
애프터스쿨 80
비스트 70
EXO 60
블랙핑크 50
티아라 40
원더걸스 20
레드벨벳 10
SQL> select rownum,name, jumsu
2 from rowtest
3 order by jumsu desc;
ROWNUM NAME JUMSU
------ ------------ -----
3 애프터스쿨 80
4 비스트 70
7 EXO 60
2 블랙핑크 50
6 티아라 40
1 원더걸스 20
5 레드벨벳 10
---점수컬럼을 내림차순으로 정렬하여 테이블로 보여준다. rownum의 순서 순차적x
Q. rank 3보다 작거나 같은 데이터를 가져오기
SQL> select rownum as rank, name, jumsu
2 from (select name, jumsu
3 from rowtest
4 order by jumsu desc);
RANK NAME JUMSU
---------- ------------ -----
1 애프터스쿨 80
2 비스트 70
3 EXO 60
4 블랙핑크 50
5 티아라 40
6 원더걸스 20
7 레드벨벳 10
--1)점수기준 내림차순의 결과물의 rownum을 lank라는 임시 칼럼 넣어 삽입된 순서대로 보여준다.
SQL> select rank, name, jumsu
2 from(select rownum as rank, name, jumsu --순서2
3 from (select name, jumsu --순서1
4 from rowtest
5 order by jumsu desc))
6 where rank <=3; --순서3
RANK NAME JUMSU
---------- ------------ -----
1 애프터스쿨 80
2 비스트 70
3 EXO 60
--1)점수컬럼을 내림차순으로 정렬하여 테이블로 보여준다. rownum의 순서 순차적x
--2)점수기준 내림차순의 결과물의 rownum을 lank라는 임시 칼럼 넣어 삽입된 순서대로 보여준다./새로 생기는 rownum에는 별칭을 써서 구분해준다.
--3)rank 3보다 작거나 같은 데이터를 가져오기
Q. rowtest에서 rank 3~5사이 조
방법1)
SQL> select rank, name, jumsu
2 from(select rownum as rank, name, jumsu
3 from (select name, jumsu
4 from rowtest
5 order by jumsu desc))
6 where rank >=3 and rank <=5;
RANK NAME JUMSU
---------- ------------ -----
3 EXO 60
4 블랙핑크 50
5 티아라 40
방법2)
SQL> select rank, name, jumsu
2 from(select rownum as rank, name, jumsu
3 from (select name, jumsu
4 from rowtest
5 order by jumsu desc))
6 where rank between 3 and 5;
RANK NAME JUMSU
---------- ------------ -----
3 EXO 60
4 블랙핑크 50
5 티아라 40
Q. members table에서 salary 4~6등 조회해보기
1단계) salary 높은순으로 정렬
SQL> select rownum,name,salary,birth
2 from members
3 order by salary desc;
ROWNUM NAME SALARY BIRTH
------ ------------ -------- --------
13 수영 1000000 22/06/13
1 하하 5000 22/06/14
2 호호 5000 22/06/14
3 수지 2000 88/03/14
7 효연 800 97/05/09
5 티파니 700 85/12/03
12 써니 600 87/03/04
8 제시카 500 91/12/02
9 제시카 500 91/12/02
10 유리 400 89/04/12
6 수영 300 78/08/21
ROWNUM NAME SALARY BIRTH
------ ------------ -------- --------
11 윤아 300 72/09/05
4 제시카 100 90/12/25
--rownum :insert한 순서, rownum빼고 내림차순으로 정렬되었다.
2단계) salay높은순으로 정렬(내림차순)한것을 rank라는 임시칼럼에 넣어서 rownum순서대로 정렬한다.
SQL> select rownum as rank,name,salary,birth
2 from (select rownum,name,salary,birth
3 from members
4 order by salary desc);
RANK NAME SALARY BIRTH
---------- ------------ -------- --------
1 수영 1000000 22/06/13
2 하하 5000 22/06/14
3 호호 5000 22/06/14
4 수지 2000 88/03/14
5 효연 800 97/05/09
6 티파니 700 85/12/03
7 써니 600 87/03/04
8 제시카 500 91/12/02
9 제시카 500 91/12/02
10 유리 400 89/04/12
11 수영 300 78/08/21
RANK NAME SALARY BIRTH
---------- ------------ -------- --------
12 윤아 300 72/09/05
13 제시카 100 90/12/25
3단계) 정렬된 rownum과 salary를 정렬 시킨것에서 4~6순위의 데이터를 가져온다.
SQL> select rank,name,salary,birth
2 from(select rownum as rank,name,salary,birth
3 from (select rownum,name,salary,birth
4 from members
5 order by salary desc))
6 where rank >=4 and rank <=6;
RANK NAME SALARY BIRTH
---------- ------------ -------- --------
4 수지 2000 88/03/14
5 효연 800 97/05/09
6 티파니 700 85/12/03
Q.rowtest 에서 점수 2-3등
1) 이름과 점수별로 조회
SQL> select rownum,name,jumsu
2 from rowtest
3 order by jumsu desc;
ROWNUM NAME JUMSU
------ ------------ -----
3 애프터스쿨 80
4 비스트 70
7 EXO 60
2 블랙핑크 50
6 티아라 40
1 원더걸스 20
5 레드벨벳 10
--insert된 순서대로 rownum의 순위출력됨
2) rank 별칭을 주어 점수의 내림차순에 맞게 rownum 순위가 변경되었다.
SQL> select rownum as rank ,name,jumsu
2 from(select rownum,name,jumsu
3 from rowtest
4 order by jumsu desc);
RANK NAME JUMSU
---------- ------------ -----
1 애프터스쿨 80
2 비스트 70
3 EXO 60
4 블랙핑크 50
5 티아라 40
6 원더걸스 20
7 레드벨벳 10
3) 점수 2~3등만 조회한다.
SQL> select rownum as rank ,name,jumsu
2 from(select rownum as rank ,name,jumsu
3 from(select rownum,name,jumsu
4 from rowtest
5 order by jumsu desc))
6 where rank >= 2 and rank <= 3;
RANK NAME JUMSU
---------- ------------ -----
1 비스트 70
2 EXO 60
● dense_rank()
: 점수 구하는 함수, 같은 점수가 있어도 같은 순위로 안매긴다.중복x
SQL> select rownum, name, jumsu, dense_rank() over( order by jumsu desc ) 순위
2 from rowtest;
ROWNUM NAME JUMSU 순위
------ ------------ ----- ----------
3 애프터스쿨 80 1
4 비스트 70 2
7 EXO 60 3
2 블랙핑크 50 4
6 티아라 40 5
1 원더걸스 20 6
5 레드벨벳 10 7
Q.2~3순위 조회 해보기
SQL> select name, jumsu, dense_rank() over( order by jumsu desc ) rank
2 from rowtest
3 where rank >= 2 and rank <= 3;
where rank >= 2 and rank <= 3
*
3행에 오류:
ORA-00904: "RANK": 부적합한 식별자
아래와 같이 오류를 처리해줍니다.
select name, jumsu,rank
2 from(select name, jumsu, dense_rank() over( order by jumsu desc ) rank
3 from rowtest)
4 where rank >= 2 and rank <= 3;
NAME JUMSU RANK
------------ ----- ----------
비스트 70 2
EXO 60 3
Q.2~4순위 조회 해보기
SQL> select name, jumsu,rank
2 from(select name, jumsu, dense_rank() over( order by jumsu desc ) rank
3 from rowtest)
4 where rank >= 2 and rank <= 4;
NAME JUMSU RANK
------------ ----- ----------
비스트 70 2
EXO 60 3
블랙핑크 50 4
● rank()
: 점수 구하는 함수, 중복된 점수가 있을때 등수를 구분한다. 점수 중복되면 등수도 중복으로 표기해준다.
SQL> select rownum, name, jumsu, rank() over( order by jumsu desc ) rank
2 from rowtest;
ROWNUM NAME JUMSU RANK
------ ------------ ----- ----------
3 애프터스쿨 80 1
4 비스트 70 2
7 EXO 60 3
2 블랙핑크 60 3
6 티아라 40 5
1 원더걸스 20 6
5 레드벨벳 10 7
Q.2~4순위 조회 해보기
SQL> select name, jumsu,rank
2 from(select name, jumsu, rank() over( order by jumsu desc ) rank
3 from rowtest)
4 where rank >= 2 and rank <= 4;
NAME JUMSU RANK
------------ ----- ----------
비스트 70 2
블랙핑크 60 3
EXO 60 3