--샘플 데이타 입력
CREATE TABLE TBL_ORDER (
ORDERSERIAL VARCHAR2(32) NOT NULL, --결제번호
USERID VARCHAR2(32) NOT NULL, --구매자ID
ITEMID NUMBER(11) NOT NULL, --상품번호
PRIMARY KEY (ORDERSERIAL,USERID,ITEMID)
)
;
INSERT ALL
INTO TBL_ORDER VALUES ('20190701000000001', 'A', 1)
INTO TBL_ORDER VALUES ('20190701000000001', 'A', 2)
INTO TBL_ORDER VALUES ('20190701000000001', 'A', 3)
INTO TBL_ORDER VALUES ('20190701000000002', 'B', 1)
INTO TBL_ORDER VALUES ('20190701000000002', 'B', 4)
INTO TBL_ORDER VALUES ('20190701000000003', 'C', 2)
INTO TBL_ORDER VALUES ('20190701000000003', 'C', 3)
INTO TBL_ORDER VALUES ('20190701000000003', 'C', 5)
INTO TBL_ORDER VALUES ('20190701000000004', 'D', 1)
INTO TBL_ORDER VALUES ('20190701000000004', 'D', 2)
INTO TBL_ORDER VALUES ('20190701000000004', 'D', 6)
SELECT *
FROM DUAL
;
INSERT ALL
INTO TBL_ORDER VALUES ('20190701000000005', 'A', 1)
INTO TBL_ORDER VALUES ('20190701000000005', 'A', 2)
INTO TBL_ORDER VALUES ('20190701000000005', 'A', 6)
SELECT *
FROM DUAL
;
COMMIT;
--확인
SELECT * FROM
TBL_ORDER;
--협업 필터링(collaborative filtering) SQL을 이용하여 기능
--해석)TEMP를 구매하였으면서 TEMP2를 구매한 고객의 수 = CNT
SELECT A.ITEMID AS TEMP, B.ITEMID AS TEMP2, COUNT(B.ITEMID) AS CNT
FROM
TBL_ORDER A
JOIN TBL_ORDER B
ON
1 = 1
AND A.ORDERSERIAL = B.ORDERSERIAL
AND A.USERID = B.USERID
AND A.ITEMID <> B.ITEMID
GROUP BY
A.ITEMID, B.ITEMID
ORDER BY
A.ITEMID, B.ITEMID, COUNT(B.ITEMID) DESC;
--웹로그 정제
CREATE TABLE TBL_WEBLOG (
YYYYMMDDHHMMSS VARCHAR2(32) NOT NULL,
USERID VARCHAR2(32) NOT NULL,
ITEMID NUMBER(11) NOT NULL,
PRIMARY KEY (YYYYMMDDHHMMSS,USERID,ITEMID)
);
INSERT ALL
INTO TBL_WEBLOG VALUES ('2019-07-01 11:22:33', 'A', 1)
INTO TBL_WEBLOG VALUES ('2019-07-01 12:22:33', 'A', 2)
INTO TBL_WEBLOG VALUES ('2019-07-01 13:22:33', 'A', 3)
INTO TBL_WEBLOG VALUES ('2019-07-01 11:40:33', 'B', 1)
INTO TBL_WEBLOG VALUES ('2019-07-01 11:41:33', 'B', 4)
INTO TBL_WEBLOG VALUES ('2019-07-01 09:22:33', 'C', 2)
INTO TBL_WEBLOG VALUES ('2019-07-01 09:55:33', 'C', 3)
INTO TBL_WEBLOG VALUES ('2019-07-01 09:59:33', 'C', 5)
INTO TBL_WEBLOG VALUES ('2019-07-01 15:22:33', 'D', 1)
INTO TBL_WEBLOG VALUES ('2019-07-01 16:22:33', 'D', 2)
INTO TBL_WEBLOG VALUES ('2019-07-01 17:22:33', 'D', 6)
SELECT *
FROM DUAL
;
INSERT ALL
INTO TBL_WEBLOG VALUES ('2019-07-02 15:22:33', 'A', 1)
INTO TBL_WEBLOG VALUES ('2019-07-02 16:22:33', 'A', 2)
INTO TBL_WEBLOG VALUES ('2019-07-02 17:22:33', 'A', 6)
SELECT *
FROM DUAL
;
COMMIT;
--같은 날짜에 본 상품을 같이 본 상품으로 정하면 동일한 쿼리를 적용할 수 있습니다.
SELECT A.ITEMID AS TEMP, B.ITEMID AS TEMP2, COUNT(B.ITEMID) AS CNT
FROM
TBL_WEBLOG A
JOIN TBL_WEBLOG B
ON
1 = 1
AND LPAD(A.YYYYMMDDHHMMSS, 10) = LPAD(B.YYYYMMDDHHMMSS, 10)
AND A.USERID = B.USERID
AND A.ITEMID <> B.ITEMID
GROUP BY
A.ITEMID, B.ITEMID
ORDER BY
A.ITEMID, B.ITEMID, COUNT(B.ITEMID) DESC;
--여기까지는 협업 필터링을 SQL 로 구현한 것에 불과하고 실제로 서비스에는 사용할 수 없는 데이타입니다.
--같이 구매한 상품 을 예로 들어봅니다. 우리가 원하는 같이 구매한 상품은 "노트북을 살 때 마우스도 같이 살 것이다" 라는 생각이지만 현실은 베스트셀러가 같이 구매한 상품이 되는 경우가 더 많습니다.
--간단한 예로 엑셀 도서를 구매하려는 고객이 있습니다. 그런데 메인 페이지에 삼겹살이 특가로 세일을 하여 그것을 본 고객은 엑셀도서와 삼겹살을 같이 구매하게 되는거죠.
--위 문제를 해결하는 가장 간단한 방법은 카테고리 제한이었습니다.
CREATE TABLE tbl_order_v2 (
orderserial varchar2(32) NOT NULL,
userid varchar2(32) NOT NULL,
category varchar2(32) NOT NULL,
itemid number(11) NOT NULL,
PRIMARY KEY (orderserial,userid,itemid)
);
insert all
into tbl_order_v2 values ('20190701000000001', 'A', 'book', 1)
into tbl_order_v2 values ('20190701000000001', 'A', 'book', 2)
into tbl_order_v2 values ('20190701000000001', 'A', 'food', 3)
into tbl_order_v2 values ('20190701000000002', 'B', 'book', 1)
into tbl_order_v2 values ('20190701000000002', 'B', 'food', 4)
into tbl_order_v2 values ('20190701000000003', 'C', 'book', 2)
into tbl_order_v2 values ('20190701000000003', 'C', 'food', 3)
into tbl_order_v2 values ('20190701000000003', 'C', 'food', 5)
into tbl_order_v2 values ('20190701000000004', 'D', 'book', 1)
into tbl_order_v2 values ('20190701000000004', 'D', 'book', 2)
into tbl_order_v2 values ('20190701000000004', 'D', 'food', 6)
select *
from dual
;
commit;
--협업조회
select A.itemid, B.itemid, count(B.itemid) as cnt
from
tbl_order_v2 A
join tbl_order_v2 B
on
1 = 1
and A.orderserial = B.orderserial
and A.userid = B.userid
and A.category = B.category
and A.itemid <> B.itemid
group by
A.itemid, B.itemid
order by
A.itemid, count(B.itemid) desc;
참고 :
https://github.com/skyer9/collaborative-filtering-in-mysql
