데이터베이스4 - Top N Query

함수(Function)

- 입력과 출력으로 이루어진 기능 집합이다.

- 부르면 무언가 작업해주는 도구이다.

- 함수는 혼자 있을 수 있지만 메소드(method)는 어디에 포함되어 있어야 한다.

 

-- <단일행 함수> - 입력과 출력이 1대1인 함수

select book_title from book;

select upper(book_title) from book;

select lower(book_title) from book;

select length(book_title) from book;

select * from book where lower(book_title) like 'harry%';

-- 단일행 함수는 테이블의 구초를 해치지 않기 때문에 아무 곳에서 써도 상관 없다.

 

-- <집계 함수> - 데이터를 모아서 하나의 결과를 만드는 함수

select max(book_price) from book;

select min(book_price) from book;

select sum(book_price) from book;

select avg(book_price) from book;

-- count는 해당 항목이 null이면 카운트 하지 않는다.

select count(book_price) from book;

-- null 여부와 관계 없이 카운트 하고 싶다면 다음과 같이 사용

select count(*) from book;

 

-- (주의) 집계함수는 정해진 자리에만 사용할 수 있다.

-- select * from book where book_price = max(book_price); -- 구문오류... 사용 불가능

-- <분석 함수>

서브쿼리(sub query)

- 여러 개의 구문을 순차적으로 실행하도록 만드는 기술

-- (ex) 가장 비싼 금액의 상품 정보를 조회

-- 따로 했을 경우

select max(price) from product;

select * from product where price = 3000;

 

-- 서브쿼리로 변환해서 같이 한 경우

select * from product where price = (

select max(price) from product

);

 

-- (Q) 가장 최근에 제조된 상품 정보

select * from product where made = (select max(made) from product);

-- (Q) 가격이 가장 저렴한 책 정보

select * from book where book_price = (select min(book_price) from book);

Top N Query

- 서브쿼리를 사용하여 데이터를 원하는 구간만큼 끊어내는 구문이다.
- 오라클은 rownum이라는 값을 조회 시 사용할 수 있다.
- rownum은 조회 결과에 순서를 부여한다.

/*

select * from (

select rownum rn, TMP.* from (

원하는 순서로 정렬하는 구문

)TMP

) where rn between 시작 and 종료;

*/

 

 

-- (Q) 상품을 번호순으로 3개만 조회

-- (Q) 상품을 가격이 저렴한순으로 3개만 조회

 

select * from product order by price asc;

select * from product order by price desc;

select * from product order by no asc;

 

select rownum, product.* from product order by price asc;

select rownum, product.* from product order by price desc;

select rownum, product.* from product order by no asc;

 

-- 정렬을 먼저하고 rownum을 나중에 붙이도록 구조 개선

select rownum, TMP.* from (

select * from product order by price asc

)TMP where rownum <= 3;

 

-- 문제점 : 중간부터는 조회가 안됨

select rownum, TMP.* from (

select * from product order by price asc

)TMP where rownum between 3 and 5;

 

-- 해결책 : rownum을 모두 붙인 다음 조회하도록 서브쿼리 추가

-- 문제점 : rownum은 select마다 생긴다

select * from (

select rownum, TMP.* from (

select * from product order by price asc

)TMP

) where rownum between 3 and 5;

 

 

-- 해결책 : 두 번째 select에서 발생한 rownum에 별칭을 부여

select * from (

select rownum rn, TMP.* from (

select * from product order by price asc

)TMP

) where rn between 3 and 5;

 

-- (Q) 가격이 가장 비싼 책 Top 5 조회

select * from (

select rownum rn, TMP.* from (

select * from book order by book_price desc

)TMP

) where rn between 1 and 5;

 

-- (Q) 제목이 가장 짧은 책 Top 3 조회

select * from (

select rownum rn, TMP.* from (

select * from book order by length(book_title) asc

)TMP

) where rn between 1 and 3;

 

-- (Q) 출간한지 가장 오래된 책 6등~10등 조회

select * from (

select rownum rn, TMP.* from (

select * from book order by book_publication_date asc

)TMP

) where rn between 6 and 10;

 

(Q) Top N Query 통합 예제

- (1) 좋아요를 가장 많이 받은 곡 Top 100을 출력

- 좋아요 오름차순으로 정렬을 먼저 한 뒤 1부터 100까지의 행을 추출

SELECT * FROM (

SELECT rownum rn, TMP.* FROM (

SELECT * FROM kpop_song ORDER BY kpop_song_likecount desc

)TMP

) WHERE rn BETWEEN 1 AND 100;

(2) 재생을 가장 많이 한 곡 Top 100을 출력

SELECT * FROM (

SELECT rownum rn, tmp.* FROM (

SELECT * FROM kpop_song ORDER BY kpop_song_playcount DESC

)TMP

) WHERE RN BETWEEN 1 AND 100;

(3) 랭킹포인트를 계산하여 Top 100을 출력, 랭킹포인트는 재생수*0.6 + 좋아요*0.4 로 계산합니다

-- 두 개의 풀이

SELECT * FROM (

SELECT rownum rn, TMP.* from(

SELECT kpop_song_no, kpop_song_title, kpop_song_artist, kpop_song_album, kpop_song_playcount, kpop_song_likecount,

kpop_song_playcount * 0.6 + kpop_song_likecount * 0.4 랭킹포인트 FROM kpop_song ORDER BY 랭킹포인트 DESC

)TMP

) WHERE rn BETWEEN 1 AND 100

;

 

SELECT * FROM (

SELECT rownum rn, TMP.* from(

select KPOP.*,

kpop_song_playcount * 0.6 + kpop_song_likecount * 0.4 랭킹포인트

from kpop_song kpop

order by 랭킹포인트 DESC

)TMP

) where rn BETWEEN 1 and 100;