[21,22] 검색 기능 평가하기, 데이터마이닝 - NoMatch, 재검색, 재현율, 검색 이탈 비율, 정확률, MAP, 어소시에이션 분석

목차

3. 데이터 가공을 위한 SQL
 3-1. 하나의 값 조작하기 (5강)
 3-2. 여러 개의 값에 대한 조작 (6강)
  3-3. 하나의 테이블에 대한 조작 (7강)
3-4. 여러 개의 테이블 조작하기 (8강)
4. 매출을 파악하기 위한 데이터 추출 
  4-1. 시계열 기반으로 데이터 집계하기 (9강)
4-2. 다면적인 축을 사용해 데이터 집약하기 (10강)
5. 사용자를 파악하기 위한 데이터 추출 
  5-1. 사용자 전체의 특징과 경향 찾기 (11강)
  5-2. 시계열에 따른 사용자 전체의 상태 변화 찾기 (12강)
  5-3. 시계열에 따른 사용자의 개별적인 행동 분석하기 (13강)
6. 웹사이트에서 행동을 파악하는 데이터 추출하기
  6-1. 사이트 전체의 특징/경향 찾기
  6-2. 사이트 내의 사용자 행동 파악하기
  6-3. 입력 양식 최적화하기
7. 데이터 활용의 정밀도를 높이는 분석 기술
  7-1. 데이터를 조합해서 새로운 데이터 만들기
  7-2. 이상값 검출하기
  7-3. 데이터 중복 검출하기
  7-4. 여러 개의 데이터셋 비교하기
8. 데이터를 무기로 삼기 위한 분석 기술
  8-1. 검색 기능 평가하기
  8-2. 데이터 마이닝
  8-3. 추천
  8-4. 점수 계산하기
9. 지식을 행동으로 옮기기
  9-1. 데이터 활용의 현장

---

※ Mysql 기준
8장. 데이터를 무기로 삼기 위한 분석 기술

21강.  검색 기능 평가하기

 
검색하는 사용자의 행동 패턴 정리하기
: 사용자는 검색 결과 원하는 화면이 나오면 상세 화면으로 이동하고, 없다면 재검색하거나 서비스를 이탈할 것
-> 사용자가 무엇을 검색하고, 그 결과에 대해 어떤 행동을 취하는지 구분할 수 있다면, 기능 개선을 요청할 수 있
 
검색 기능 개선 방법

1. 검색 키워드의 흔들림을 흡수할 수 있게 동의어 사전 추가하기 - 단축된 이름, 별칭 등 검색의 흔들림 흡수
2. 검색 키워드를 검색 엔진이 이해할 수 있게 사용자 사전 추가하기 - 서비스가 의도한대로 단어가 분해되지 않기 때문
3. 검색 결과가 사용자가 원하는 순서로 나오게 정렬 순서 조정하기 - 검색 쿼리와의 연관성을 수치화해서 높은 순서대로 정렬

---

21-1. NoMatch 비율과 키워드 집계하기

SUM(CASE~), AVG(CASE~), NoMatch 비율, NoMatch 키워드

NoMatch 비율
: 검색 총 수 중에서 검색 결과를 0으로 리턴하는 검색 결과 비율
<NoMatch 비율> = <검색 결과가 0인 수[NoMatch 수]> / <검색 총 수>
 
NoMatch 비율 집계하기
 
table

access_log

 

SELECT
SUBSTRING(stamp, 1, 10) AS dt,
COUNT(*) AS search_count,
SUM(CASE WHEN result_num = 0 THEN 1 ELSE 0 END) AS no_match_count,
AVG(CASE WHEN result_num = 0 THEN 1.0 ELSE 0.0 END) AS no_match_rate
From access_log
WHERE action = 'search'
GROUP BY substring(stamp, 1, 10);

 

 
NoMatch 키워드 집계하기
: NoMatch가 될 때의 검색 키워드가 무엇이 있는지 추출하기 (어떤 키워드가 0개의 결과를 내는지)

WITH
search_keyword_stat AS(
SELECT
keyword,
result_num,
COUNT(*) AS search_count,
100.0 * COUNT(*) / COUNT(*) OVER() AS search_share
FROM access_log
WHERE action='search'
GROUP BY keyword, result_num)
SELECT
keyword,
search_count,
search_share,
100.0 * search_count / SUM(search_count) OVER() AS no_match_share
FROM search_keyword_stat
WHERE result_num = 0;

 
-> 어떤 검색어의 검색 결과가 0이 자주 나오는지 확인 

---

21-2. 재검색 비율과 키워드 집계하기

LEAD 함수, SUM(CASE~), AVG(CASE~), 재검색 비율, 재검색 키워드

 
재검색 비율
: 사용자가 검색 결과의 출력과 관계 없이, 어떤 결과도 클릭하지 않고 새로 검색한 비율
 
재검색 비율 집계하기

• 검색 결과 출력 로그(action=search)와 검색 화면에서 상세 화면으로의 이동 로그(action=detail)를 시계열 순서로 나열
• 각각의 줄에 다음 줄의 액션을 기록

WITH
access_log_with_next_action AS(
SELECT 
stamp,
session,
action,
LEAD(action) OVER(PARTITION BY session 
ORDER BY stamp ASC ROWS BETWEEN 1 FOLLOWING AND 1 FOLLOWING)
AS next_action
FROM access_log)
SELECT * FROM access_log_with_next_action
ORDER BY session, stamp;

 

search -&gt; search 가 재검색

 
action이 search인 레코드를 집계하고 이를 검색 총 수로 보고 재검색 비율 집계하기

-- access_log_with_next_action까지 동일
SELECT 
SUBSTRING(stamp, 1, 10) AS dt,
COUNT(*) AS search_count,
SUM(CASE WHEN next_action = 'search' THEN 1 ELSE 0 END) AS retry_count,
AVG(CASE WHEN next_action = 'search' THEN 1.0 ELSE 0.0 END) AS retry_rate
FROM access_log_with_next_action
WHERE action='search'
GROUP BY dt, substring(stamp, 1, 10)
ORDER BY dt;

 
재검색 키워드 집계하기

• 동의어 사전이 흔들림을 잡지 못하는 범위를 확인할 수 있음
• 추가로 콘텐츠 명칭의 새로운 흔들림과 사람들이 일반적으로 해당 콘텐츠를 무엇이라고 부르는치 찾는 계기가 될 수 있음
• 재검색 키워드를 집계해서 대처해야 하는 부분을 동의어 사전에 반영해야 함

WITH
access_log_with_next_search AS(
SELECT
stamp,
session,
action,
keyword,
result_num,
LEAD(action) OVER(PARTITION BY session
ORDER BY stamp ASC) AS next_action,
LEAD(keyword) OVER(PARTITION BY session
ORDER BY stamp ASC) AS next_keyword,
LEAD(result_num) OVER(PARTITION BY session
ORDER BY stamp ASC) AS next_result_num
FROM access_log)
SELECT
keyword,
COUNT(*) AS retry_count,
next_keyword,
next_result_num
FROM access_log_with_next_search
WHERE action = 'search'
AND next_action = 'search'
GROUP BY keyword, result_num, next_keyword, next_result_num;

 
-> 동의어 사전을 사람이 직접 관리하는 것은 힘들기 때문에, 재검색 키워드를 집계하여 검색 시스템이 자동으로 흔들림을 제거하게 개선할 수 있음

---

21-3. 재검색 키워드를 분류해서 집계하기

LIKE 연산자, 재검색 키워드

사용자의 재검색 패턴 유형
1. NoMatch에서의 조건 변경: 검색 결과가 0개이므로 다른 검색어로 검색
2. 검색 결과 필터링: 검색 결과가 너무 많아 단어를 필터링
3. 검색 키워드 변경: 검색 결과가 나오기는 했지만, 다른 검색어로 다시 검색 
 
1. NoMatch에서의 조건 변경
: 조건을 변경했다면, 해당 키워드는 동의어 사전과 사용자 사전에 추가할 키워드 후보들이 됨

-- access_log_with_next_search까지 동일
SELECT
keyword,
result_num,
COUNT(*) AS retry_count,
next_keyword,
next_result_num
FROM access_log_with_next_search
WHERE action = 'search'
AND next_action = 'search'
AND result_num = 0
GROUP BY keyword, result_num, next_keyword, next_result_num;

 
2. 검색 결과 필터링
: 재검색한 키워드가 원래의 키워드를 포함하고 있다면, 검색을 조금 더 필터링하고 싶다는 의미라고 생각할 수 있다. 자주 사용되는 필터링 키워드를 원래 검색 키워드로 검색했을 때 연관 검색어 등으로 출력하여 사용자가 요구하는 콘텐츠로 빠르게 유도하기
 
검색 결과 필터링 시의 재검색 키워드 집계하기

-- WHERE
AND next_keyword LIKE '%' || keyword || '%'

 
-> 재검색 시 원래의 검색 keyword가 포함되어 있는 데이터만 필터링

-- access_log_with_next_search까지 동일
SELECT
keyword,
result_num,
COUNT(*) AS retry_count,
next_keyword,
next_result_num
FROM access_log_with_next_search
WHERE action = 'search'
AND next_action = 'search'
AND next_keyword LIKE '%' || keyword || '%'
GROUP BY keyword, result_num, next_keyword, next_result_num;

 
검색 키워드 변경
: 완전히 다른 키워드로 재검색을 했다면, 원래 검색 결과에 원하는 내용이 없다는 의미
= 즉, 동의어 사전이 제대로 기능하지 못한다는 의미 
 
검색 키워드 변경 때 재검색을 집계하는 쿼리

-- WHERE
AND next_keyword NOT LIKE '%' || keyword || '%'

---

21-4. 검색 이탈 비율과 키워드 집계하기

SUM(CASE~), AVG(CASE~), 검색이탈률, 검색 이탈 키워드
 

검색 결과가 출력된 이후에 아무 액션을 취하지 않고 이탈한 사용자는 검색 결과를 만족하지 못한 경우
: 검색 결과가 0개이거나, 원하는 결과가 나오지않은 사용자가 얼마나 존재하는지와 그때의 키워드 확인하기
 
검색 이탈 집계 방법

• action이 search이고, next action이 NULL이면 검색 이탈
• action이 search인 것을 검색 총수로 사용하여 검색 이탈 수를 검색 총 수로 나눠 검색 이탈 비율 구하기

searh-&gt;null

-- 'next action' IS NULL
SUM(CASE WHEN next_action IS NULL THEN 1 ELSE 0 END) AS exit_count,
AVG(CASE WHEN next_action IS NULL THEN 1.0 ELSE 0.0 END) AS exit_rate

WITH
access_log_with_next_action AS(
SELECT 
stamp,
session,
action,
LEAD(action) OVER(PARTITION BY session 
ORDER BY stamp ASC ROWS BETWEEN 1 FOLLOWING AND 1 FOLLOWING)
AS next_action
FROM access_log)
SELECT 
SUBSTRING(stamp, 1, 10) AS dt,
COUNT(*) AS search_count,
SUM(CASE WHEN next_action IS NULL THEN 1 ELSE 0 END) AS exit_count,
AVG(CASE WHEN next_action IS NULL THEN 1.0 ELSE 0.0 END) AS exit_rate
FROM access_log_with_next_action
WHERE action='search'
GROUP BY dt, substring(stamp, 1, 10)
ORDER BY dt;

 
검색 이탈 키워드 집계하기

WITH
access_log_with_next_search AS(
SELECT
stamp,
session,
action,
keyword,
result_num,
LEAD(action) OVER(PARTITION BY session
ORDER BY stamp ASC) AS next_action,
LEAD(keyword) OVER(PARTITION BY session
ORDER BY stamp ASC) AS next_keyword,
LEAD(result_num) OVER(PARTITION BY session
ORDER BY stamp ASC) AS next_result_num
FROM access_log)
SELECT 
keyword,
COUNT(*) AS search_count,
SUM(CASE WHEN next_action IS NULL THEN 1 ELSE 0 END) AS exit_count,
AVG(CASE WHEN next_action IS NULL THEN 1.0 ELSE 0.0 END) AS exit_rate,
result_num
FROM access_log_with_next_search
WHERE action='search'
GROUP BY keyword, result_num
HAVING SUM(CASE WHEN next_action IS NULL THEN 1 ELSE 0 END) > 0;

 
-> 검색에서 이탈한 사용자가 검색 결과에 만족하지 못한 이유는 다양한 경우가 있다.
ex. 원하는 상품이 상위에 표시되지 않는 등 출력 순서와 관련된 문제
 

---

21-5. 검색 키워드 관련 지표의 집계 효율화하기

 
검색과 관련된 지표를 집계하기 위해 쉽게 중간 데이터를 생성하는 쿼리

• access_log_with_next_search로 next_action/keyword/result_num 생성 후
• search_log_with_next_search로 action이 search인 데이터만 필터링

WITH
access_log_with_next_search AS(
SELECT
stamp,
session,
action,
keyword,
result_num,
LEAD(action) OVER(PARTITION BY session
ORDER BY stamp ASC) AS next_action,
LEAD(keyword) OVER(PARTITION BY session
ORDER BY stamp ASC) AS next_keyword,
LEAD(result_num) OVER(PARTITION BY session
ORDER BY stamp ASC) AS next_result_num
FROM access_log),
search_log_with_next_action AS (
SELECT *
FROM access_log_with_next_search
WHERE action='search')
SELECT *
FROM search_log_with_next_action
ORDER BY session, stamp;

 
-> 중간 데이터로 NoMatch 수, 재검색 수, 검색 이탈 수를 포함해 키워드 등을 간단하게 집계할 수 있음

---

21-6. 검색 결과의 포괄성을 지표화하기

FULL OUTER JOIN, SUM 윈도 함수, 재현율

 
검색 키워드에 대한 지표를 사용해 검색 엔진 자체의 정밀도 평가하기
테이블
search_result: 검색 키워드에 대한 결과 순위

search_result

correct_result: 검색 키워드에 대한 정답 아이템(어떤 검색 키워드에 대해 히트 되었으면 하는 아이템을 미리 정리한 것)

correct_result

 
재현율(Recall)을 사용해 검색의 포괄성 평가하기
 
재현율(Recall)
: 어떤 키워드의 검색 결과에서 미리 준비한 정답 아이템이 얼마나 나왔는지 비율로 나타낸 것
ex. 특정 키워드로 10개의 검색 결과가 나왔으면 했는데, 실제로 4개만 나왔다면 재현율은 40%. (Tp/Tp+Fn)
= 1인 것들 중 실제로 1인 비율

• 검색 결과와 정답 아이템을 결합하고,
• 어떤 아이템이 정답 아이템에 포함되는지 판단해야 함

mysql은 FULL OUTER JOIN을 지원하지 않아 LEFT JOIN ~ UNION ~ RIGHT JOIN 사용

1. search_result 테이블의 항목 keyword, rank, item과 correct_result 테이블의 항목을 결합
2. COALESCE 함수를 사용해 두 테이블 중 하나에서만 존재하는 값을 가져옴
3. correct_result 테이블에 항목이 있으면 correct컬럼을 1로 설정

WITH search_result_with_correct_items AS(
SELECT
    COALESCE(r.keyword, c.keyword) AS keyword,
    r.rank,
    COALESCE(r.item, c.item) AS item,
    CASE WHEN c.item IS NOT NULL THEN 1 ELSE 0 END AS correct
FROM search_result AS r
LEFT JOIN correct_result AS c ON r.keyword = c.keyword AND r.item = c.item
UNION
SELECT
    COALESCE(r.keyword, c.keyword) AS keyword,
    r.`rank`,
    COALESCE(r.item, c.item) AS item,
    CASE WHEN c.item IS NOT NULL THEN 1 ELSE 0 END AS correct
FROM search_result AS r
RIGHT JOIN correct_result AS c ON r.keyword = c.keyword AND r.item = c.item
ORDER BY keyword, `rank`)

 

 
->correct 컬럼의 플래그가 1인 아이템이 정답 아이템에 포함된 아이템
 
정답 아이템의 총 수(Tp+Fn)를 구하기 위해 검색 결과에 포함되지 않은 정답 아이템 레코드도 같이 집계하기

• 정답 항목 총 수 구하기
• 상위 n개의 재현율 계산하기

1. 각 키워드에 대해 누적된 정답의 수(cum_correct)와 재현율 계산
2. sum(correct) 윈도 함수로 각 키워드에 대해 현재 행까지의 정답 누적 합 구하기
3. 재현율은 각 키워드의 누적 정답 수를 정체 정답 수로 나눈 값에 100 곱하기 (rank가 null이면 0.0으로 계산)

-- search_result_with_correct_items까지 동일
,
search_result_with_recall AS(
SELECT *,
SUM(correct) OVER(PARTITION BY keyword
ORDER BY COALESCE(`rank`, 100000) ASC
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
AS cum_correct,
CASE WHEN `rank` IS NULL THEN 0.0
ELSE 100.0 * SUM(correct) OVER(
PARTITION BY keyword ORDER BY COALESCE(`rank`, 100000) ASC
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
/ SUM(correct) OVER(PARTITION BY keyword)
END AS recall
FROM search_result_with_correct_items)
SELECT * FROM
search_result_with_recall
ORDER BY keyword,`rank`;

 
재현율의 값을 집약해서 비교하기
: 검색 결과의 출력 결과가 디폴트로 5개라고 가정하고, 재현율을 키워드별로 계산하기

• 검색 결과가 5개 이상이면 상위 5개로 재현율을 구하고,
• 검색 결과가 5개보다적을 경우 검색 결과 전체를 기반으로 구하기

검색 결과 상위 5개 재현율을 키워드별로 추출하기
ROW_NUMBER()

-- 검색 결과 순위가 높은 순서로 번호 붙이기
-- 결과에 나오지 않는 아이템은 0으로
ROW_NUMBER() OVER(PARTITION BY keyword
ORDER BY COALESCE(`rank`, 0) DESC)
AS desc_number

-- search_result_with_recall까지 동일
,
recall_over_rank_5 AS(
SELECT
keyword,
`rank`,
recall,
ROW_NUMBER() OVER(PARTITION BY keyword
ORDER BY COALESCE(`rank`, 0) DESC)
AS desc_number
FROM search_result_with_recall
-- 검색 결과 상위 5개 치하 또는 검색 결과에 포함되지않은 아이템만 출력
WHERE COALESCE(`rank`, 0) <= 5)
SELECT
keyword,
recall AS recall_at_5
FROM recall_over_rank_5
-- 상위 5개 중 가장 순위가 높은 레코드 추출
WHERE desc_number = 1;

키워드별 겸색 결과 상위 5개 현율

검색 엔진 전체의 평균 재현율 계산하기

-- recall_over_rank_5까지 동일
SELECT
AVG(recall) AS average_recall_at_5
FROM recall_over_rank_5
WHERE desc_number=1;

---

21-7. 검색 결과의 타당성 지표화하기

FULL OUTER JOIN, SUM 윈도 함수, 정확률

 

정확률
: 검색 결과에 포함되는 아이템 중 정답 아이템이 어느정도 비율로 포함되는지
EX. 검색 결과 상위 10개에 정답 아이템이 5개 포함되어있다면, 정확률은 50%
(<->특정 키워드로 10개의 검색 결과가 나왔으면 했는데, 실제로 4개만 나왔다면 재현율은 40%)
 
정확률을 사용해 검색의 타당성 평가하기
: 상위 N개의 정확률 계산하기
- 재현율 식과 동일, 분모 부분만 검색 결과 순위까지의 누계 아이템 수로 바뀜

-- 검색 결과까지의 누계 아이템 수 
COUNT(*) OVER(PARTITION BY keyword
ORDER BY COALESCE(`rank`, 100000) ASC
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
END AS `precision`

-- search_result_with_correct_items 까지 동일
,
search_result_with_precision AS(
SELECT *,
SUM(correct) OVER(PARTITION BY keyword
ORDER BY COALESCE(`rank`, 100000) ASC
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
AS cum_correct,
CASE WHEN `rank` IS NULL THEN 0.0
ELSE 100.0 * SUM(correct) OVER(
PARTITION BY keyword ORDER BY COALESCE(`rank`, 100000) ASC
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
-- 분모만 변경, 검색 결과 순위까지의 누계 아이템 수
/ COUNT(*) OVER(PARTITION BY keyword
ORDER BY COALESCE(`rank`, 100000) ASC
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
END AS `precision`
FROM search_result_with_correct_items)
SELECT * 
FROM search_result_with_precision
ORDER BY keyword, `rank`;

 
정확률 값을 집약해서 비교하기 쉽게 만들기
: 검색 결과 상위 5개 정확률을 키워드별로 추출하기

-- search_result_with_precision까지 동일
,
precision_over_rank_5 AS(
SELECT
keyword,
`rank`,
`precision`,
ROW_NUMBER() OVER(PARTITION BY keyword
ORDER BY COALESCE(`rank`, 0) DESC)
AS desc_number
FROM search_result_with_precision
WHERE COALESCE(`rank`, 0) <= 5)
SELECT
keyword,
`precision` AS precision_at_5
FROM precision_over_rank_5
WHERE desc_number=1;

 
 

SELECT
AVG(`precision`) AS average_precision_at_5
FROM precision_over_rank_5
WHERE desc_number=1;

---

21-8. 검색 결과 순위와 관련된 지표 계산하기

AVG, MAP(Mean Average Precision)

재현율과 정확률의 부족한 부분

1. 검색 결과의 순위 고려 X
2. 정답과 정답이 아닌 아이템을 0과 1이라는 두 가지 값으로밖에 표현할 수 없음
3. 모든 아이템에 대한 정답을 미리 준비하는 것은 거의 불가능

검색 순위를 고려한 지표로는 MAP(Mean Average precision)과 MRR(Mean Reciprocal Rank)등이 있음
 
DGG (Discounted Cumulated Gain) & NDGG (Normalized DGG) 
정답/정답이 아닌 아이템을 '관련도가 높은 아이템', '관련도가 낮은 아이템'처럼 단계적인 점수를 사용해 다루고 싶은 경우
 
BPREF(Binary Preference)
: 검색 대상 아이템 수에서 정답 아이템 수가 한정된 경우
 
MAP으로 검색 결과의 순위 고려해 평가하기
MAP

• 검색 결과 상위 N개의 단일 쿼리에 대한 정확률의 평균
• ex.
  • 10개 중에서 4개가 정답 아이템이라고 할 때, 상위 N개의 정확률은 40%이다.
  • 만약 상위 1~4번째까지 모두 정답 아이템이면, MAP값은 100* ((1/1)+(2/2)+(3/3)+(4/4))/4 =100이 된다
  • 만약 상위 7~10번째가 정답 아이템이라면, 상위 N개의 정확률은 마찬가지로 40%이지만, MAP값은 100* ((1/7)+(2/8)+(3/9)+(4/10))/4 = 28.15가 되어 이전보다 낮게 평가됨
• 특정 쿼리 또는 쿼리 집합에 대해 평균적으로 얼마나 관련 있는 결과가 높은 순위에 배치되는지를 측정.

-> MAP을 계산하려면 일단 정답 아이템별로 정확률을 추출해야 함 (이전 쿼리에서 correct=1인 레코드만 추출)
1. 정답 아이템별로 정확률 추출하기

-- search_result_with_precision까지 동일
SELECT
keyword,
`rank`,
`precision`
FROM search_result_with_precision
WHERE correct=1;

2. 검색 키워드별 정확률 평균 계산

-- search_result_with_precision까지 동일
,
average_precision_for_keywords AS(
SELECT
keyword,
AVG(`precision`) AS average_precision
FROM search_result_with_precision
WHERE correct=1
GROUP BY keyword)
SELECT *
FROM average_precision_for_keywords;

 
3. 검색 키워드별 정확률의 평균을 추출해서 검색 엔진 자체의 MAP 계산하기

SELECT
AVG(average_precision) AS mean_average_precision
FROM average_precision_for_keywords;

 
검색 평가와 관련한 다른 지표들

---

22강. 데이터 마이닝

 
데이터 마이닝
: 대량의 데이터에서 특정 패턴 또는 규칙 등 유용한 지식을 추출하는 방법을 전반적으로 나타내는 용어
종류
1. 상관 규칙 추출
2. 클러스터링
3. 상관 분석
* 데이터 마이닝 방법의 대부분은 재귀 처리와 휴리스틱 처리가 필요해 단순히 sql로는 처리가 어렵다.
 
데이터마이닝의 대표적인 방법 상관 규칙 추출 방법 중 하나인 '어소시에이션 분석'의 로직을 sql로 구현해보자
 
사용 테이블

table purchase_detail_log

 

22-1. 어소시에이션 분석

COL, 지지도

 

여기서 상관 규칙이란 '상품 A와 B가 동시에 구매되는 경향이 있다'처럼 단순 동시 시점의 상황이 아니라, '상품 A를 구매했다면, 상품B도 구매한다'처럼 시간적 차이와 인과관계를 가지는 규칙을 의미
(따라서 상품 A를 구매했다면 B도 구매한다가 참이어도, B를 구매했다면 A도 구매한다가 반드시 성립하지 않음)
 
어소시에이션 분석에 사용되는 지표
1. 지지도(Support) -  전체 거래 중 해당 규칙이 관찰되는 비율
ex. 100개 구매 로그에서 '상품X와 Y를 구매한 로그'가 20개라면, 규칙에 대한 지지도는 20%
 

 

2. 확신도,신뢰도(Confidence) - 한 상품을 구매한 거래 중에서 다른 상품도 함께 구매된 비율
ex. 100개 구매 로그에서 상품X를 구매하는 레코드가 50개 있고, 내부에 상품 Y도 함께 구매하는 레코드가 20개 있다면, 확신도는 20/50 = 40%

3.리프트(Lift) - 어떤 조건을 만족하는 경우의 확률 (=신뢰도)을 사전 조건 없이 해당 결과가 일어날 확률로 나눈 값
ex. 100개 구매로그에서 상품X를 구매하는 로그가 50개, X와 Y를 모두 구매하는 로그가 20개, Y만 구매하는 로그가 20개라면, 신뢰도는 20/50 = 40%, Y의 구매확률은 20/100 = 20%, 리프트는 40%/20% = 2.0이 된다.
-> 즉, X를 구매한 경우 Y를 구매할 확률이 2배가 된다는 것을 알 수 있음
-> 향상도가 1 이상이면 두 상품이 함께 구매될 확률이 독립적으로 구매될 확률보다 높다는 것을 의미함

두 상품의 연관성을 어소시에이션 분석으로 찾기
구매 로그 수와 삼풍별 구매 수를 세는 쿼리
: 필요한 정보는

1. 구매 로그 총 수
2. 상품 A의 구매 수
3. 상품 B의 구매 수
4. 상품 A와 B의 동시 구매 수

purchase_detail_log

1. 구매 로그 총 수

WITH
purchase_id_count AS(
SELECT COUNT(DISTINCT purchase_id) AS purchase_count
FROM purchase_detail_log),
purchase_detail_log_with_counts AS(
SELECT
d.purchase_id,
p.purchase_count,
d.product_id,
COUNT(*) OVER(PARTITION BY d.product_id) AS product_count
FROM purchase_detail_log AS d
CROSS JOIN
purchase_id_count AS p)
SELECT * 
FROM purchase_detail_log_with_counts
ORDER BY product_id, purchase_id;

 
2. 상품 조합별 구매 수 (A,B,동시 구매 수)

-- purchase_detail_log_with_counts까지 동일
,
product_pair_with_stat AS(
SELECT
l1.product_id AS p1,
l2.product_id AS p2,
l1.product_count AS p1_count,
l2.product_count AS p2_count,
COUNT(*) AS p1_p2_count,
l1.purchase_count AS purchase_count
FROM purchase_detail_log_with_counts AS l1
JOIN
purchase_detail_log_with_counts AS l2
ON l1.purchase_id = l2.purchase_id
WHERE
-- 같은 상품 조합 제외
l1.product_id <> l2.product_id
GROUP BY
l1.product_id,
l2.product_id,
l1.product_count,
l2.product_count,
l1.purchase_count)
SELECT * FROM product_pair_with_stat
ORDER BY p1,p2;

-> 4개의 키페어(구매 로그 총 수, A 구매 수, B 구매 수, 동시 구매 수)를 구한 테이블
 
지표 정의에 다라 어소시에이션 분석의 지표 계산하기 - 지지도, 확신도, 리프트

-- product_pair_with_stat까지 동일
SELECT
p1,
p2,
100.0 * p1_p2_count / purchase_count AS support,
100.0 * p1_p2_count / p1_count AS confidence,
(100.0 * p1_p2_count / p1_count)
/ (100.0 * p2_count / purchase_count) AS lift
FROM product_pair_with_stat
ORDER BY p1,p2;

 
-> <상품 p1을 구매한 사람이 상품 p2도 구매한다.> 라는 상관 규칙에 관한 지지도, 확신도, 리프트 확인
-> 세 가지 지표를 복합적으로 고려하여 유용한 규칙 선택
-> 통상적으로 지지도가 충분히 높으면서 신뢰도와 향상도가 1보다 큰 규칙들을 '유용한 규칙'으로 간주
-> 특히 향상도가 매우 중요한데, 이는 두 상품이 서로 얼마나 강한 양의 상관관계를 가지고 있는지를 나타냄
-> A002 → A004: 향상도가 2.33으로 높으며, 신뢰도는 50%이므로 유용한 규칙으로 간주할 수 있다. A002 상품을 구매할 때 A004 상품도 함께 구매될 확률이 높음을 나타냄

---