728x90
목차
실습에 필요한 테이블 및 자료
검색 기능 평가하기
- 검색하는 사용자의 행동
- 사용자가 무엇을 검색하고 검색 결과에 대해 어떤 행동을 취하는지 구분할 수 있으면, 엔지니어에게 기능 개선등을 요청 가능하다.
- 검색 기능 개선 방법
- 검색 키워드의 흔들림을 흡수할 수 있게 동의어 사전 추가하는 방법이 있다.
- 검색 키워드를 검색 엔진이 이해할 수 있게 사용자 사전 추가하는 방법이 있다.
- Ex.) 위스키 검색 시 위, 스키 단어로 분해 → 스키 검색 시 위스키로 결과나오는 경우
- 이런 경우를 방지하기 위해 사용자 사전을 추가해야 한다.
- 검색 결과가 사용자가 원하는 순서로 나오게 순서 조정
- 검색 쿼리와의 연관성을 수치화해서 점수가 높은 순서로 정렬해주는 순위 기능이 필요하다.
- 문장의 출현 위치 및 빈도, 아이템 갱신 일자, 접속 수 등 다양한 요소와 조합해야 한다.
💡 흔들림 : 검색 엔진의 키워드를 설명할 때 단어가 정확하지 않고 조금 떨리는 현상
- NoMatch 비율과 키워드 집계하기
- NoMatch : 검색 총 수 중에서 검색 결과를 0으로 리턴하는 검색 결과 비율
- NoMatch 비율 = 검색 결과 0인 수 / 검색 총 수
- NoMatch 비율 집계 쿼리
SELECT
substring(stamp, 1, 10) AS dt
, COUNT(1) 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
dt;- NoMatch 비율과 키워드 집계하기
WITH
search_keyword_stat AS (
SELECT
keyword
, result_num
, COUNT(1) AS search_count
, 100.0 * COUNT(1) / COUNT(1) 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;- 재검색 비율과 키워드 집계하기
- 사용자가 검색 결과의 출력과 관계 없이, 어떤 결과도 클릭하지 않고 새로 검색을 실행한 비율이다.
- 검색 화면과 상세 화면의 접근 로그에 다음 줄의 액션을 기록하는 쿼리
WITH
access_log_with_next_action AS (
SELECT
stamp
, session
, action
, LEAD(action)
OVER(PARTITION BY session ORDER BY stamp ASC) AS next_action
FROM
access_log
)
SELECT *
FROM access_log_with_next_action
ORDER BY
session, stamp;검색 결과의 포괄성을 지표화하기
- 재현율을 사용해 검색의 포괄성 평가하는 방법이 있다.
- 재현율(recall) : 어떤 키워드의 검색 결과에서 미리 준비한 정답 아이템이 얼마나 나왔는지에 대한 척도
- 검색 결과와 정답 아이템을 결합하는 쿼리
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
FULL OUTER JOIN
correct_result AS c
ON r.keyword = c.keyword
AND r.item = c.item
)
SELECT *
FROM
search_result_with_correct_items
ORDER BY
keyword, rank;- 재현율의 값을 집약해서 비교하기 쉽게 만드는 쿼리
- 검색 엔진의 일반적인 인터페이스는 상위 n개 순위 형식으로 출력한다(Ex.) 네이버 실시간 검색어)
- 따라서 재현율은 사용자에게 출력되는 아이템 개수로 한정해서 구한다.
- 해당 쿼리에서는 출력 default를 5개로 가정한다.
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
FULL OUTER JOIN
correct_result AS c
ON r.keyword = c.keyword
AND r.item = c.item
)
, search_result_with_recall AS (
SELECT
*
-- 검색 결과 상위에서 정답 데이터에 포함되는 아이템 수의 누계 구하기
, SUM(correct)
-- rank가 NULL이면 정렬 순서의 마지막 위치, 편의상 큰 값으로 변환해서 넣기
OVER(PARTITION BY keyword ORDER BY COALESCE(rank, 100000) ASC
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS cum_correct
, CASE
-- 검색 결과에 포함되지 않은 아이템은 편의상 적합률을 0으로 다루기
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
)
, recall_over_rank_5 AS (
SELECT
keyword
, rank
, recall
-- 검색 결과 순위가 높은 순서로 번호 붙이기
-- 검색 결과에 안나오는 아이템은 0으로 다루기
, ROW_NUMBER()
OVER(PARTITION BY keyword ORDER BY COALESCE(rank, 0) DESC) AS desc_number
FROM
search_result_with_recall
WHERE
-- 검색 결과 상위 5개 이하, 없는 아이템만 출력
COALESCE(rank, 0) <= 5
)
SELECT
keyword
, recall AS recall_at_5
FROM
recall_over_rank_5
WHERE
desc_number = 1;- 검색 결과의 타당성 지표화하기
- 정밀도(precision) : 검색 결과중 정답 아이템의 비율
- 검색 결과 상위 n개의 정밀도 계산 쿼리
- 100000의 경우 임의의 큰 값
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
FULL OUTER JOIN
correct_result AS c
ON r.keyword = c.keyword
AND r.item = c.item
)
, 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(1) 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;- 검색 엔진 전체의 평균 정확률 계산 쿼리
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
FULL OUTER JOIN
correct_result AS c
ON r.keyword = c.keyword
AND r.item = c.item
)
, 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(1) 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
)
, 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
, AVG(precision) AS average_precision_at_5
FROM
precision_over_rank_5
WHERE
desc_number = 1;검색 결과 순위와 관련된 지표 계산하기
- 재현률과 정확률의 부족한 부분
- 검색 결과의 순위 고려 X( P@10)
- (Ex. 적합률이 40% 일 때 , 전체 100개중 1,4 번째에 있는거랑 2, 7번째 있는거랑 첫번쨰께 더 좋은 검색 엔진임)
- 이를 위해 검색 순위를 고려한 지표 MAP(Mean Average Precision), MRR(Mean Reciprocal Rank)가 있다.
- 0과 1이라는 두가지 값으로만 표현
- Ex.) 사용자의 별점 리뷰 데이터, 점수를 정답 데이터로 사용시 → 사용자로부터 입력받은 별점 활용 X
- 단계적인 점수를 고려하는 지표 DCG(Discounted Cumulated Gain)와 NDCG(Normalized DCG)가 있다.
- 모든 아이템에 대한 정답을 미리 준비 X
- 검색 대상 아이템 수에서 정답 아이템 수가 한정될 때 BPREF(Binary Preference)를 활용한다.
- MAP으로 검색 결과의 순위를 고려해 평가하기
- Ex.) 10개 중 정답 아이템 수가 4개일때 P@10 = 40%, 만약 상위 1~4번째까지 모두 정답 아이템인 경우 MAP = 100 x ((1/1) + (2/2) + (3/3) + (4/4)) / 4 = 100이 된다.
- 반면, 상위 7~10번째가 정답 아이템이면, MAP = 100 x (1/7) + (2/8) + (3/9) + (4/10)) / 4 = 28.15가 되어 이전보다 낮게 평가된다.
- 정답 아이템별로 적합률을 추출하는 쿼리
- 검색 결과의 순위 고려 X( P@10)
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
FULL OUTER JOIN
correct_result AS c
ON r.keyword = c.keyword
AND r.item = c.item
)
, 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(1) 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
keyword
, rank
, precision
FROM
search_result_with_precision
WHERE
correct = 1;
데이터 마이닝
- 데이터 마이닝의 대표적 방법 상관 규칙 추출 방법 중 하나인 **어소시에이션 분석(연관분석)**을 다룸
- 상품 조합(A,B,C..)을 구매한 사람의 n%는 상품 조합(X,Y..)도 구매한다.. 라는 규칙이다.
- 사용되는 지표 : 지지도(Support), 확신도,신뢰도(Confidence), 리프트(Lift)
- 두 상품의 연관성을 연관분석으로 찾기
- 구매 로그 수와 상품별 구매 수를 세는 쿼리
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(1) 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;- 지지도, 확신도, 향상도를 계산하는 쿼리
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(1) OVER(PARTITION BY d.product_id) AS product_count
FROM
purchase_detail_log AS d
CROSS JOIN
-- 구매 로그 수를 모든 레코드와 결합
purchase_id_count AS p
)
, 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(1) AS p1_p2_count
, l1.purchase_count AS purchase_count
FROM
purchase_detail_log_with_counts AS l1
JOIN
purchase_detail_log_with_counts SA 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
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;
추천 시스템 구성하기
- 추천 시스템은 두 종류
- 열람/구매한 아이템을 기반으로 다른 아아템을 추천해주는 Item to Item
- 과거의 행동 또는 데모그래픽 정보를 기반으로 흥미를 유추하고 아이템을 추천하는 User to Item
- EC 사이트의 모듈의 종류
| 모듈 | 설명 | 예시 |
| 리마인드 | 사용자의 과거 행동을 기반으로 아이템을 다시 제안 | 최근 보았던 상품, 한 번 더 구매하기 |
| 순위 | 열람 수, 구매 수 등을 기반으로 인기 있는 아이템 제안 | 인기 순위, 급상승 순위 |
| 콘텐츠베이스 | 아이템의 추가 정보를 기반으로 다른 아이템 추천 | 해당 배우가 출연한 다른 작품 |
| 추천 | 서비스를 사용하는 사용자의 전체의 행동 이력 기반으로, 다음에 볼만한 아이템 또는 관련된 아이템을 추측해 기반 | 이 상품을 보았던 사람들은 이러한 상품들도 함께 보았습니다 |
| 개별 추천 | 사용자 개인의 행동 이력을 기반으로 흥미 기호 추측, 아이템 제안 | 당신만을 위한 추천 |
- 추천의 효과
| 효과 | 설명 | 예시 |
| 다운셀 | 가격이 높아 구매를 고민하는 사용자에게 더 저렴한 아이템을 제안해서 구매 수를 올리는 것 | 이전 모델 가격 할인 |
| 크로스셀 | 관련 상품을 함께 구매하게 해서 구매 단가 올리기 | 함께 구매되는 상품 |
| 업셀 | 상위 모델 또는 고성능의 아이템을 제안해 구매 단가를 올리는 것 | 이 상품의 최신 모델 |
- 데이터의 명시적 획득과 암묵적 획득
- 추천 시스템 구축 시 '어떤 데이터를 기반으로 추천 시스템을 구축할 것 인가?'를 중요시 해야 한다.
| 데이터 기반 | 설명 | 예시 |
| 암묵적 데이터 획득 | 사용자 행동 기반으로 기호 추측 데이터 양은 많지만 정확도가 떨어진다 |
구매 로그, 열람 로그 |
| 명시적 데이터 획득 | 사용자에게 직접 기호를 물어본다 | 별점 주기 |
- 열람 로그, 구매 로그 차이
| 데이터 소스 | 내용 | 데이터 예시 |
| 열람 로그 | 특정 아이템과 유사한 아이템을 추천해서 사용자의 선택지 늘리기 | 여행용 가방을 구매한다고 할 때 크기, 형태, 색이 다른 것을 추천하는 경우 |
| 구매 로그 | 함께 구매 가능한 상품을 추천해서 구매 단가 끌어 올리기 | 여행용 가방을 구매한다고 할 때 함께 구매하면 좋은 여권 케이스 등을 추천하는 경우 |
- 접근 로그를 사용해 아아템의 상관도 계산하기
- Item to Item, 사용자 유동성 > 아이템 유동성 이므로 데이터 축적이 쉽다.
- 열람 수와 구매 수를 조합한 점수를 계산하는 쿼리
WITH
ratings AS (
SELECT
user_id
, product
-- 상품 열람 수
, SUM(CASE WHEN action = 'view' THEN 1 ELSE 0 END) AS view_count
-- 상품 구매 수
, SUM(CASE WHEN action = 'purchase' THEN 1 ELSE 0 END) AS purchase_count
-- 열람수, 구매수 3:7 비율의 가중치를 주어 평균 구하기
, 0.3 * SUM(CASE WHEN action = 'view' THEN 1 ELSE 0 END)
+ 0.7 * SUM(CASE WHEN action = 'purchase' THEN 1 ELSE 0 END) AS score
FROM
action_log
GROUP BY
user_id, product
)
SELECT *
FROM
ratings
ORDER BY
user_id, score DESC;- 아이템 사이의 유사도를 계산하고 순위 생성하는 쿼리
WITH
ratings AS (
SELECT
user_id
, product
-- 상품 열람 수
, SUM(CASE WHEN action = 'view' THEN 1 ELSE 0 END) AS view_count
-- 상품 구매 수
, SUM(CASE WHEN action = 'purchase' THEN 1 ELSE 0 END) AS purchase_count
-- 열람수, 구매수 3:7 비율의 가중치를 주어 평균 구하기
, 0.3 * SUM(CASE WHEN action = 'view' THEN 1 ELSE 0 END)
+ 0.7 * SUM(CASE WHEN action = 'purchase' THEN 1 ELSE 0 END) AS score
FROM
action_log
GROUP BY
user_id, product
)
SELECT
r1.product AS target
, r2.product AS related
-- 모든 아이템을 열람 / 구매한 사용자 수
, COUNT(r1.user_id) AS users
-- 점수들을 곱하고 합계를 구해 유사도 구하기
, SUM(r1.score * r2.score) AS score
-- 상품 유사도 순위 구하기
, ROW_NUMBER() OVER(PARTITION BY r1.product ORDER BY SUM(r1.score * r2.score) DESC) AS rank
FROM
ratings AS r1
JOIN
ratings AS r2
ON
r1.user_id = r2.user_id
WHERE
-- 같은 아이템의 경우 페어 제외
r1.product <> r2.product
GROUP BY
r1.product, r2.product
ORDER BY
target, rank;- 이러한 방식을 사용했을 때 문제점이 발생할 수 있다.
- 첫 번째는, 접근 수가 많은 아이템의 유사도가 상대적으로 높게 나온다
- 점수의 값이 어느 정도의 유사도를 나타내느지 점수만으로 알기 어렵다
- 이러한 문제 해결을 위해 벡터 정규화를 사용한다
- 벡터의 크기를 노름(norm)이라고 부르는데, 노름으로 벡터의 각 수치를 나누면 벡터의 노름을 1로 만들 수 있음
- 이러한 과정을 L2 정규화라고 한다.
- 다음 쿼리는 아이템 벡터를 L2 정규화하는 쿼리
WITH
ratings AS (
SELECT
user_id
, product
-- 상품 열람 수
, SUM(CASE WHEN action = 'view' THEN 1 ELSE 0 END) AS view_count
-- 상품 구매 수
, SUM(CASE WHEN action = 'purchase' THEN 1 ELSE 0 END) AS purchase_count
-- 열람수, 구매수 3:7 비율의 가중치를 주어 평균 구하기
, 0.3 * SUM(CASE WHEN action = 'view' THEN 1 ELSE 0 END)
+ 0.7 * SUM(CASE WHEN action = 'purchase' THEN 1 ELSE 0 END) AS score
FROM
action_log
GROUP BY
user_id, product
)
, product_base_normalized_ratings AS (
-- 아이템 벡터 정규화하기
SELECT
user_id
, product
, score
, SQRT(SUM(score * score) OVER(PARTITION BY product)) AS norm
, score / SQRT(SUM(score * score) OVER(PARTITION BY product)) AS norm_score
FROM
ratings
)
SELECT *
FROM
product_base_normalized_ratings- 정규화된 점수로 아이템의 유사도를 계산하는 쿼리(코사인 유사도)
WITH
ratings AS (
SELECT
user_id
, product
-- 상품 열람 수
, SUM(CASE WHEN action = 'view' THEN 1 ELSE 0 END) AS view_count
-- 상품 구매 수
, SUM(CASE WHEN action = 'purchase' THEN 1 ELSE 0 END) AS purchase_count
-- 열람수, 구매수 3:7 비율의 가중치를 주어 평균 구하기
, 0.3 * SUM(CASE WHEN action = 'view' THEN 1 ELSE 0 END)
+ 0.7 * SUM(CASE WHEN action = 'purchase' THEN 1 ELSE 0 END) AS score
FROM
action_log
GROUP BY
user_id, product
)
, product_base_normalized_ratings AS (
-- 아이템 벡터 정규화하기
SELECT
user_id
, product
, score
, SQRT(SUM(score * score) OVER(PARTITION BY product)) AS norm
, score / SQRT(SUM(score * score) OVER(PARTITION BY product)) AS norm_score
FROM
ratings
)
SELECT
r1.product AS target
, r2.product AS related
, COUNT(r1.user_id) AS users
, SUM(r1.score * r2.score) AS score
, SUM(r1.norm_score * r2.norm_score) AS norm_score
, ROW_NUMBER() OVER(PARTITION BY r1.product ORDER BY SUM(r1.norm_score * r2.norm_score) DESC)
AS rank
FROM
product_base_normalizd_ratings AS r1
JOIN
product_base_normalized_ratings AS r2
ON r1.user_id = r2.user_id
GROUP BY
r1.product, r2.product
ORDER BY
target, rank;- 당신을 위한 추천 상품(User to Item)
- 사용자와 관련된 추천이므로 웹사이트 최상위 페이지, 사용자의 마이 페이지등 다양한 상황에 활용된다.
- 사용자와 사용자의 유사도 계산후 유사 사용자가 흥미를 가진 아이템을 구해야 한다.
- 사용자끼리의 유사도를 계산하는 쿼리
WITH
ratings AS (
SELECT
user_id
, product
-- 상품 열람 수
, SUM(CASE WHEN action = 'view' THEN 1 ELSE 0 END) AS view_count
-- 상품 구매 수
, SUM(CASE WHEN action = 'purchase' THEN 1 ELSE 0 END) AS purchase_count
-- 열람수, 구매수 3:7 비율의 가중치를 주어 평균 구하기
, 0.3 * SUM(CASE WHEN action = 'view' THEN 1 ELSE 0 END)
+ 0.7 * SUM(CASE WHEN action = 'purchase' THEN 1 ELSE 0 END) AS score
FROM
action_log
GROUP BY
user_id, product
)
, user_base_normalized_ratings AS (
-- 사용자 벡터 정규화하기
SELECT
user_id
, product
, score
-- PARTITION BY user_id으로 사용자별 벡터 노름 계산하기
, SQRT(SUM(score * score) OVER(PARTITION BY user_id)) AS norm
, score / SQRT(SUM(score * score) OVER(PARTITION BY user_id)) AS norm_score
FROM
ratings
)
, related_users AS (
-- 경향이 비슷한 사용자 찾기
SELECT
r1.user_id
, r2.user_id AS related_user
, COUNT(r1.product) AS products
, SUM(r1.norm_score * r2.norm_score) AS score
, ROW_NUMBER() OVER(PARTITION BY r1.user_id ORDER BY SUM(r1.norm_score * r2.norm_score) DESC) AS rank
FROM
user_based_normalized_ratings AS r1
JOIN
user_based_normalized_ratings AS r2
ON r1.product = r2.product
WHERE
r1.user_id <> r2.user_id
GROUP BY
r1.user_id, r2.user_id
)
SELECT *
FROM
related_users
ORDER BY
user_id, rank;점수 계산하기
- 여러 값을 균형있게 조합해서 점수 계산하기
- 세 종류의 평균(산술 평균, 기하 평균, 조화 평균)
- 산술 평균 - 일반적인 평균, 각각의 값을 모두 더한 뒤 전체 개수로 나눈 것
- 기하 평균 - 각각의 값을 곱한뒤 개수만큼 제곱근(성장률, 이율)
- 조화 평균 - 각 값의 역수의 산술 평균에 다시 역수를 취한 값(평균 속도 계산)
- 평균 계산하기
- 세로 데이터의 평균을 구하는 쿼리
SELECT
id
, COUNT(*) AS record_num
-- 데이터를 배열로 집약하고, 쉼표로 구분된 문자열로 변환하기
, string_agg(name, ',') AS name_list
, string_agg(stamp, ',') AS stamp_list
FROM
mst_categories
GROUP BY
id
HAVING COUNT(*) > 1 -- 중복된 ID 확인- 가로 기반 데이터의 평균을 계산하는 쿼리
SELECT
path
-- 산술 평균
, AVG(value) AS arithmetic_mean
-- 기하 평균(상용 로그로 log 함수 사용)
, POWER(10, AVG(log(value))) AS geometric_mean
-- 조화 평균
, 1.0 / (AVG(1.0 / value)) AS harmonic_mean
FROM
search_evaluation_by_row
WHERE
value > 0
GROUP BY
path
-- 빠진 데이터가 없게 path로 한정
HAVING
COUNT(*) = 2
ORDER BY
path;- 가중 평균
- 검색 결과의 포괄성을 고려하면서 검색 결과 상위에 있는 요소들의 타당성을 중요시하면서 검색 엔진을 평가할 때(재현율과 적합률의 비율을 둘 때)
- 산술 평균 - 각각의 값에 가중치 곱하고 가중치 합계로 나누기
- 기하 평균 - 가중치만큼 제곱, 합계만큼 제곱근
- 조화 평균 - 각각의 값에 역수에 가중치 곱하고 다시 역수
- 세로 기반 데이터의 가중 평균을 계산하는 쿼리
SELECT
*
-- 가중치 산술 평균
, 0.3 * recall + 0.7 * precision AS weighted_a_mean
-- 가중치 기하 평균
, POWER(recall, 0.3) * POWER(recall, 0.7) AS weighted_g_mean
-- 가중치 조화 평균
, 1.0 / ((0.3 / recall) + (0.7 / precision)) AS weighted_h_mean
FROM
searched_evaluation_by_col
WHERE
recall * precision > 0
ORDER BY
path;- 가로 기반 테이블의 가중 평균을 계산하는 쿼리
WITH
weights AS (
-- 가중치 마스터 테이블(가중치 합계 1.0)
SELECT 'recall' AS index, 0.3 AS weight
UNION ALL SELECT 'precision' AS index, 0.7 AS weight
)
SELECT
e.path
-- 가중치 산술 평균
, SUM(w.weight * e.value) AS weighted_a_mean
-- 가중치 기하 평균
, POWER(10, SUM(w.weight * log10(e.value))) AS weighted_g_mean
-- 가중치 조화 평균
, 1.0 / SUM(w.weight / e.value)) AS weighted_h_mean
FROM
search_evaluation_by_row AS e
JOIN
weights AS w
ON e.index = w.index
WHERE
e.value > 0
GROUP BY
e.path
HAVING
COUNT(*) = 2
ORDER BY
path;- Min-Max 정규화
- 서로 다른 값의 폭을 가지는 각 지표를 0~1로 스케일로 정규화하는 방법
- 열람 수와 구매 수에 Min-Max 정규화를 적용하는 쿼리
SELECT
user_id
, product
, view_count AS v_count
, purchase_count AS p_count
, 1.0 * (view_count - MIN(view_count) OVER()) / NULLIF((MAX(view_count) OVER() - MIN(view_count) OVER()), 0) AS norm_p_count
FROM
action_counts
ORDER BY
user_id, product;
거대한 숫자 지표 쉽게 가공하기
- 원래 값이 작은 경우와 원래 값이 큰 경우 있다고 할 때 숫자 차이 절댓값은 1이지만, 사람은 숫자가 작을 때 더 큰 차이라고 느낀다.
- 사용자 액션 수, 경과일 수처럼 값이 크면 클수록 값이 차이가 큰 영향을 주지 않는 경우, 로그를 사용해 데이터 변환 시 쉽게 점수 가공할 수 있다.
- 최종 액션일과의 차이가 크면 클수록 가중치를 작게하는 방법 : 날짜 차이에 역수를 취하는 방법이 있다.
- 날짜 차이에 로그를 취하는 방정식 : y = 1/ log2(ax+2)
- 다음 쿼리는 사용자들의 최종 접근일과 각 레코드와의 날짜 차이를 계산하는 쿼리
WITH
action_counts_with_diff_date AS (
SELECT *
-- 사용자별로 최종 접근일과 각 레코드의 날짜 차이 계산
, MAX(dt::date) OVER(PARTITION BY user_id) AS last_access
, MAX(dt::date) OVER(PARTITION BY user_id) - dt::date AS diff_date
FROM
actions_count_with_date
)
SELECT *
FROM
action_counts_with_diff_date;- 날짜 차이에 따른 가중치를 계산하는 쿼리
WITH
action_counts_with_diff_date AS (
SELECT *
-- 사용자별로 최종 접근일과 각 레코드의 날짜 차이 계산
, MAX(dt::date) OVER(PARTITION BY user_id) AS last_access
, MAX(dt::date) OVER(PARTITION BY user_id) - dt::date AS diff_date
FROM
action_counts_with_date
)
, action_counts_with_weight AS (
SELECT *
-- 날짜 차이에 따른 가중치 계산(매개 변수 a = 0.1)
, 1.0 / log(2, 0.1 * diff_date + 2) AS weight
FROM
action_counts_with_diff_date
)
SELECT *
FROM
action_counts_with_weight
ORDER BY
user_id, product, diff_date DESC;'데이터분석 > PostgreSQL' 카테고리의 다른 글
| 데이터 활용의 정밀도를 높이는 분석 기술 (0) | 2023.07.30 |
|---|---|
| 웹사이트에서의 행동을 파악하는 데이터 추출하기 (0) | 2023.07.17 |
| 사용자를 파악하기 위한 데이터 추출 - 2 (2) | 2023.07.05 |
| 사용자를 파악하기 위한 데이터 추출 - 1 (0) | 2023.07.05 |
| 시계열 기반으로 데이터 집계하기 (0) | 2023.07.04 |