오라클 - 질의문을 이용한 책 추천 시스템 풀이

** 추천시스템과 관련한 것

고객번호 6번에게 어떤 책을 추천 해야 할까?

1) 6번 고객이 구매한 도서목록 출력

select bookid from orders where custid = 6; //A

2) A책을 구매한 회원의 아이디와 구매권수를 출력

select custid, count(bookid) cnt 

from orders 

where bookid in (A) 

and custid != 6 

group by custid;

select custid, count(bookid) cnt 

from orders 

where bookid in (select bookid from orders where custid = 6)

and custid != 6 

group by custid; // B

3) B 중에 cnt가 가장 높은값

select max(count(bookid)) cnt 

from orders 

where bookid in (select bookid from orders where custid = 6)

and custid != 6 

group by custid; // max

4) B 중에 B의 cnt가 max인 custid를 검색

select custid from (B) 

where cnt = (max);

select custid from (select custid, count(bookid) cnt 

from orders 

where bookid in (select bookid from orders where custid = 6)

and custid != 6 

group by custid) 

where cnt = (select max(count(bookid)) cnt 

from orders 

where bookid in (select bookid from orders where custid = 6)

and custid != 6 

group by custid); // R

5) R이 구매한 도서 목록 minus 6번 고객이 구매한 도서목록

select bookid from orders 

where custid = (R) 

minus select bookid from orders 

where custid = 6;

select bookid from orders 

where custid = (select custid from (select custid, count(bookid) cnt 

from orders 

where bookid in (select bookid from orders where custid = 6)

and custid != 6 

group by custid) 

where cnt = (select max(count(bookid)) cnt 

from orders 

where bookid in (select bookid from orders where custid = 6)

and custid != 6 

group by custid)) 

minus select bookid from orders 

where custid = 6; // C

6) A의 책이름 출력(최종질의문)

select bookname from book where bookid = (C);

select bookname from book where bookid = (select bookid from orders 

where custid = (select custid from (select custid, count(bookid) cnt 

from orders 

where bookid in (select bookid from orders where custid = 6)

and custid != 6 

group by custid) 

where cnt = (select max(count(bookid)) cnt 

from orders 

where bookid in (select bookid from orders where custid = 6)

and custid != 6 

group by custid)) 

minus select bookid from orders 

where custid = 6);

--------------------------------------------------------------

1) 6번 고객과 동일한 도서를 가장 많이 구매한 고객의 id를 검색

select bookid from orders where custid = 6; //B

select distinct custid from orders 

where bookid in (select bookid from orders where custid = 6) 

and custid != 6;

권수가 가장 많이 겹치는 사람

select max(custid) from orders 

where bookid in (select bookid from orders where custid = 6) 

and custid != 6;

권수가 가장 많이 겹치는 사람의 도서목록 뽑고

select bookid from orders 

where custid in (select max(custid) from orders 

where bookid in (select bookid from orders where custid = 6) 

and custid != 6);

6번 고객이 구매한 것을 빼서 추천도서가 나오도록 함 //A

--------------------------------------------------------------------------------------------------------------

1) 모든도서 minus 내가 구매한 도서 

select bookid from book 

minus 

select bookid from orders where custid = 1; // A

2) A도서의 도서별 구매건수

형태)

select bookid, count(saleprice) from orders 

where bookid in (A) 

group by bookid 

order by cnt desc;

실제)

select bookid, count(saleprice) cnt from orders 

where bookid in (select bookid from book 

minus 

select bookid from orders where custid = 1) 

group by bookid

order by cnt desc;  // B

3) B에 행번호를 붙인다.

형태)

select rownum, bookid, cnt from (B);

실제)

select rownum, bookid, cnt from (select bookid, count(saleprice) cnt from orders 

where bookid in (select bookid from book 

minus 

select bookid from orders where custid = 1) 

group by bookid

order by cnt desc); // C

4) 행번호가 3이하 인것 추출

형태)

select bookid from (C) where rownum <= 3;

실제)

select bookid from (select rownum, bookid, cnt from (select bookid, count(saleprice) cnt from orders 

where bookid in (select bookid from book 

minus 

select bookid from orders where custid = 1) 

group by bookid

order by cnt desc)) where rownum <= 3; // D

5) 책번호가 D에 해당하는 모든책 검색
(D 에서 bookid과 비교할 bookid를 제외한 rownum과 cnt를 지워준다.)

형태)

select * from book where bookid in (D);

실제)

select * from book where bookid in (select bookid from (select bookid from (select bookid, count(saleprice) cnt from orders 

where bookid in (select bookid from book 

minus 

select bookid from orders where custid = 1) 

group by bookid

order by cnt desc)) where rownum <= 3);