오늘은 오라클의 집계 함수 중에 상관관계를 구하는 CORR 함수에 대해 설명해보려고 합니다.
CORR 함수 하나만으로도 자신의 시스템에서 다양한 인사이트를 찾아낼 수 있습니다.
예를 들어, 비슷한 판매흐름을 갖는 고객을 찾아내거나, 유사한 추세로 팔리는 제품군들을 찾아낼 수 있습니다.
먼저, CORR 함수는 MySQL에서는 사용이 불가능합니다. 이번 글은 불가피하게 오라클을 사용해야 합니다.
CORR 함수를 이해하기 위해 오라클 DB에 아래와 같은 테이블을 만들어 주세요. 판매년월(SALE_YM), 아이템ID(ITEM_ID), 판매수량(SALE_QTY)로 구성된 아주 간단한 테이블입니다. 월별-아이템별 판매량을 관리하는 테이블입니다.
CREATE TABLE T_SALE_YM_01(
SALE_YM VARCHAR2(6) NOT NULL
,ITEM_ID VARCHAR2(100) NOT NULL
,SALE_QTY NUMERIC(18,2) NOT NULL
);
ALTER TABLE T_SALE_YM_01 ADD CONSTRAINT PK_T_SALE_YM_01 PRIMARY KEY(SALE_YM, ITEM_ID);
테이블을 생성했다면, 아래 스크립트를 사용해 데이터를 입력합니다. (양이 많으니까 카피해서 쓰시면 됩니다.)
INSERT INTO T_SALE_YM_01(SALE_YM,ITEM_ID,SALE_QTY) VALUES( '202001', 'M9B', 100);
INSERT INTO T_SALE_YM_01(SALE_YM,ITEM_ID,SALE_QTY) VALUES( '202002', 'M9B', 50);
INSERT INTO T_SALE_YM_01(SALE_YM,ITEM_ID,SALE_QTY) VALUES( '202003', 'M9B', 90);
INSERT INTO T_SALE_YM_01(SALE_YM,ITEM_ID,SALE_QTY) VALUES( '202004', 'M9B', 10);
INSERT INTO T_SALE_YM_01(SALE_YM,ITEM_ID,SALE_QTY) VALUES( '202005', 'M9B', 90);
INSERT INTO T_SALE_YM_01(SALE_YM,ITEM_ID,SALE_QTY) VALUES( '202006', 'M9B', 100);
INSERT INTO T_SALE_YM_01(SALE_YM,ITEM_ID,SALE_QTY) VALUES( '202007', 'M9B', 90);
INSERT INTO T_SALE_YM_01(SALE_YM,ITEM_ID,SALE_QTY) VALUES( '202008', 'M9B', 50);
INSERT INTO T_SALE_YM_01(SALE_YM,ITEM_ID,SALE_QTY) VALUES( '202009', 'M9B', 10);
INSERT INTO T_SALE_YM_01(SALE_YM,ITEM_ID,SALE_QTY) VALUES( '202010', 'M9B', 20);
INSERT INTO T_SALE_YM_01(SALE_YM,ITEM_ID,SALE_QTY) VALUES( '202011', 'M9B', 30);
INSERT INTO T_SALE_YM_01(SALE_YM,ITEM_ID,SALE_QTY) VALUES( '202012', 'M9B', 20);
INSERT INTO T_SALE_YM_01(SALE_YM,ITEM_ID,SALE_QTY) VALUES( '202001', 'M9R', 45);
INSERT INTO T_SALE_YM_01(SALE_YM,ITEM_ID,SALE_QTY) VALUES( '202002', 'M9R', 25);
INSERT INTO T_SALE_YM_01(SALE_YM,ITEM_ID,SALE_QTY) VALUES( '202003', 'M9R', 40);
INSERT INTO T_SALE_YM_01(SALE_YM,ITEM_ID,SALE_QTY) VALUES( '202004', 'M9R', 15);
INSERT INTO T_SALE_YM_01(SALE_YM,ITEM_ID,SALE_QTY) VALUES( '202005', 'M9R', 43);
INSERT INTO T_SALE_YM_01(SALE_YM,ITEM_ID,SALE_QTY) VALUES( '202006', 'M9R', 44);
INSERT INTO T_SALE_YM_01(SALE_YM,ITEM_ID,SALE_QTY) VALUES( '202007', 'M9R', 42);
INSERT INTO T_SALE_YM_01(SALE_YM,ITEM_ID,SALE_QTY) VALUES( '202008', 'M9R', 25);
INSERT INTO T_SALE_YM_01(SALE_YM,ITEM_ID,SALE_QTY) VALUES( '202009', 'M9R', 9);
INSERT INTO T_SALE_YM_01(SALE_YM,ITEM_ID,SALE_QTY) VALUES( '202010', 'M9R', 15);
INSERT INTO T_SALE_YM_01(SALE_YM,ITEM_ID,SALE_QTY) VALUES( '202011', 'M9R', 20);
INSERT INTO T_SALE_YM_01(SALE_YM,ITEM_ID,SALE_QTY) VALUES( '202012', 'M9R', 9);
INSERT INTO T_SALE_YM_01(SALE_YM,ITEM_ID,SALE_QTY) VALUES( '202001', 'W3B', 30);
INSERT INTO T_SALE_YM_01(SALE_YM,ITEM_ID,SALE_QTY) VALUES( '202002', 'W3B', 19);
INSERT INTO T_SALE_YM_01(SALE_YM,ITEM_ID,SALE_QTY) VALUES( '202003', 'W3B', 25);
INSERT INTO T_SALE_YM_01(SALE_YM,ITEM_ID,SALE_QTY) VALUES( '202004', 'W3B', 3);
INSERT INTO T_SALE_YM_01(SALE_YM,ITEM_ID,SALE_QTY) VALUES( '202005', 'W3B', 20);
INSERT INTO T_SALE_YM_01(SALE_YM,ITEM_ID,SALE_QTY) VALUES( '202006', 'W3B', 10);
INSERT INTO T_SALE_YM_01(SALE_YM,ITEM_ID,SALE_QTY) VALUES( '202007', 'W3B', 18);
INSERT INTO T_SALE_YM_01(SALE_YM,ITEM_ID,SALE_QTY) VALUES( '202008', 'W3B', 18);
INSERT INTO T_SALE_YM_01(SALE_YM,ITEM_ID,SALE_QTY) VALUES( '202009', 'W3B', 1);
INSERT INTO T_SALE_YM_01(SALE_YM,ITEM_ID,SALE_QTY) VALUES( '202010', 'W3B', 5);
INSERT INTO T_SALE_YM_01(SALE_YM,ITEM_ID,SALE_QTY) VALUES( '202011', 'W3B', 7);
INSERT INTO T_SALE_YM_01(SALE_YM,ITEM_ID,SALE_QTY) VALUES( '202012', 'W3B', 5);
INSERT INTO T_SALE_YM_01(SALE_YM,ITEM_ID,SALE_QTY) VALUES( '202001', 'G1B', 18);
INSERT INTO T_SALE_YM_01(SALE_YM,ITEM_ID,SALE_QTY) VALUES( '202002', 'G1B', 15);
INSERT INTO T_SALE_YM_01(SALE_YM,ITEM_ID,SALE_QTY) VALUES( '202003', 'G1B', 19);
INSERT INTO T_SALE_YM_01(SALE_YM,ITEM_ID,SALE_QTY) VALUES( '202004', 'G1B', 18);
INSERT INTO T_SALE_YM_01(SALE_YM,ITEM_ID,SALE_QTY) VALUES( '202005', 'G1B', 24);
INSERT INTO T_SALE_YM_01(SALE_YM,ITEM_ID,SALE_QTY) VALUES( '202006', 'G1B', 20);
INSERT INTO T_SALE_YM_01(SALE_YM,ITEM_ID,SALE_QTY) VALUES( '202007', 'G1B', 5);
INSERT INTO T_SALE_YM_01(SALE_YM,ITEM_ID,SALE_QTY) VALUES( '202008', 'G1B', 16);
INSERT INTO T_SALE_YM_01(SALE_YM,ITEM_ID,SALE_QTY) VALUES( '202009', 'G1B', 60);
INSERT INTO T_SALE_YM_01(SALE_YM,ITEM_ID,SALE_QTY) VALUES( '202010', 'G1B', 30);
INSERT INTO T_SALE_YM_01(SALE_YM,ITEM_ID,SALE_QTY) VALUES( '202011', 'G1B', 45);
INSERT INTO T_SALE_YM_01(SALE_YM,ITEM_ID,SALE_QTY) VALUES( '202012', 'G1B', 45);
COMMIT;
어떤 제품들이 있는지 살펴보기 위해 아래와 같이 아이템별로 데이터 건수를 카운트해봅니다. 아래와 같이 GROUP BY를 간단하게 사용하면 되겠죠.
SELECT T1.ITEM_ID, COUNT(*) CNT
FROM T_SALE_YM_01 T1
GROUP BY T1.ITEM_ID
ORDER BY T1.ITEM_ID;
[결과]
ITEM_ID CNT
--------- ---
G1B 12
M9B 12
M9R 12
W3B 12
'G1B, M9B, M9R, W3B' 네 개의 아이템이 있는 것을 알 수 있습니다.
이제, CORR 함수를 사용해 'M9B' 아이템과 월별로 가장 유사하게 팔린 아이템을 찾고자 합니다. 이를 위해, 가장 먼저 할일은 'M9B' 아이템의 월별 판매량과 다른 아이템의 월별 판매량을 조인하는 것입니다. 아래와 같이 SQL을 실행합니다.
SELECT T1.ITEM_ID, T1.SALE_YM, T1.SALE_QTY
,T2.ITEM_ID 비교_ITEM_ID
,T2.SALE_QTY 비교_판매수량
FROM T_SALE_YM_01 T1
,T_SALE_YM_01 T2
WHERE T1.ITEM_ID = 'M9B'
AND T1.ITEM_ID != T2.ITEM_ID -- 또는 T2.ITEM_ID != 'M9B'
AND T1.SALE_YM = T2.SALE_YM -- 제품은 다르지만 월은 같은 데이터간에 조인
ORDER BY T1.SALE_YM ,T2.ITEM_ID;
[결과]
ITEM_ID SALE_YM SALE_QTY 비교_ITEM_ID 비교_판매수량
---------- ------- --------- -------------- ---------
M9B 202001 100 G1B 18
M9B 202001 100 M9R 45
M9B 202001 100 W3B 30
M9B 202002 50 G1B 15
M9B 202002 50 M9R 25
M9B 202002 50 W3B 19
M9B 202003 90 G1B 19
... 생략 ...
위 SQL의 결과 데이터를 보면, 2020년 1월 M9B 아이템 한 건에, 2020년 1월의 다른 아이템 판매 세 건의 데이터가 조인된 것을 볼 수 있습니다. 아이템은 다르면서 월은 같은 경우를 모두 조인 처리했기 때문입니다.
이처럼, 데이터 분석을 위해서는 관계(FK)가 완전히 성립되지 않는 데이터간에 조인을 처리하는 경우가 많습니다.
위와 같이 조인으로 인해 M9B의 데이터가 세 배로 늘어난 것을 잘 이해해야 합니다. (SQL BOOSTER나 평생 필요한 데이터 분석에서 언급했던 내용입니다.)
이제, 위 SQL을 좀 변경해서 GROUP BY와 CORR 처리만 하면, M9B와 판매 추세가 가장 비슷한 아이템을 찾아낼 수 있습니다. 아래와 같습니다.
SELECT T1.ITEM_ID
,T2.ITEM_ID 비교_ITEM_ID
,ROUND(CORR(T1.SALE_QTY,T2.SALE_QTY),6) COR_VAL
FROM T_SALE_YM_01 T1
,T_SALE_YM_01 T2
WHERE T1.ITEM_ID = 'M9B'
AND T1.ITEM_ID != T2.ITEM_ID -- 또는 T2.ITEM_ID != 'M9B'
AND T1.SALE_YM = T2.SALE_YM -- 제품은 다르지만 월은 같은 데이터간에 조인
GROUP BY T1.ITEM_ID ,T2.ITEM_ID
ORDER BY COR_VAL DESC;
[결과]
ITEM_ID 비교_ITEM_ID COR_VAL
------- --------------- ---------
M9B M9R .986024
M9B W3B .813967
M9B G1B -.626263
CORR을 처리한 값이 1에 가까울수록 상관관계가 높습니다. 다시 말해, 위 결과에서 M9B 아이템과 M9R 아이템의 상관 관계 값이 0.98로 가장 좋습니다. M9B와 W3B는 0.81이고요, M9B와 G1B는 -0.62입니다.
상관 관계를 이해하기 위해, 아래와 같이 SQL을 하나 더 실행해봅니다. CASE를 사용해 아이템을 컬럼으로 출력합니다.
SELECT T1.SALE_YM
,MAX(CASE WHEN T1.ITEM_ID = 'M9B' THEN T1.SALE_QTY END) M9B_QTY
,MAX(CASE WHEN T1.ITEM_ID = 'M9R' THEN T1.SALE_QTY END) M9R_QTY
,MAX(CASE WHEN T1.ITEM_ID = 'W3B' THEN T1.SALE_QTY END) W3B_QTY
,MAX(CASE WHEN T1.ITEM_ID = 'G1B' THEN T1.SALE_QTY END) G1B_QTY
FROM T_SALE_YM_01 T1
GROUP BY T1.SALE_YM
ORDER BY T1.SALE_YM;
[결과]
SALE_YM M9B_QTY M9R_QTY W3B_QTY G1B_QTY
------- --------- --------- --------- ---------
202001 100 45 30 18
202002 50 25 19 15
202003 90 40 25 19
202004 10 15 3 18
202005 90 43 20 24
202006 100 44 10 20
202007 90 42 18 5
202008 50 25 18 16
202009 10 9 1 60
202010 20 15 5 30
202011 30 20 7 45
202012 20 9 5 45
위 SQL의 결과를 엑셀로 내려서 간단한 선 차트를 그려봅니다. 아래와 같이, M9B와 M9R의 월별 판매량의 추세가 비슷한 것을 알 수 있습니다.
준비한 내용은 여기까지입니다.
이 기술을 잘 사용하면, 각자의 시스템에서 다양한 상관관계를 찾아낼 수 있습니다. 상관관계가 높다고 해서 인과관계가 있는 것은 아니지만, 상관관계를 이용해 새로운 인사이트를 도출해내고, 이를 응용한 마케팅 전략들을 만들어 낼 수 있을것이라 생각합니다.
감사합니다~!
'데이터분석 > D-Tech 데이터로 하는 재테크' 카테고리의 다른 글
| LG생활건강을 6개월 보유하면? (0) | 2022.05.07 |
|---|---|
| 금리인상, 나의 투자 전략은? Feat.구글트렌드 (1) | 2022.05.05 |
| 카테시안 조인 이해하기 (0) | 2021.09.27 |
| 셀프 조인 이해하기 (0) | 2021.09.23 |
| 복합구조칩 수출과 상관 관계가 가장 높은 종목은? (0) | 2021.09.08 |
| 메모리 수출과 하이닉스와 삼성전자의 주가 (0) | 2021.09.04 |
| 파이썬과 SQL로 메모리 수출입 차트 만들기 (0) | 2021.09.03 |
| 메모리 품목 수출입 데이터 DB화하기 (0) | 2021.09.03 |