[SQL] 상품리뷰 데이터 분석 3. 텍스트 분석

이 포스팅은 <SQL로 맛보는 데이터 전처리 분석(https://www.yes24.com/Product/Goods/86544423)> 책의 5장을 학습하며 정리한 내용이다.

 

 

상품리뷰 데이터 분석 - 3

이전 포스팅에서는 가장 낮은 평점을 Department별, 연령별로 다양하게 조회해보았다.

이번 포스팅에서는 리뷰 내용 중 size 관련한 내용이 얼마나 되는지 알아보고 다양하게 분류하여 조회해본다.

---

데이터세트 컬럼 구조

Clothing ID	상품번호(Unique Value)
Age	리뷰 작성자의 연령
Title	리뷰 제목
Review Text	리뷰 내용
Rating	사용자가 제출한 평점
Recommend IND	상품 추천 여부
Positive Feedback Count	긍정적 피드백 수
Division Name	상품이 속한 Division (Initmates, General ...)
Department Name	상품이 속한 Department(Bottoms , jackets...)
Class Name	상품의 타입

 

REVIEW 텍스트 조회

---

• size가 포함된 리뷰 1로 표시하기

USE mydata; -- 이용할 스키마 불러오기(이하 생략)

SELECT
	`REVIEW TEXT`
	, CASE WHEN `REVIEW TEXT`LIKE '%SIZE%' THEN 1 ELSE 0 END SIZE_YN -- size 라는 단어가 존재하면 1, 아니면 0
FROM dataset2
;

size 라는 단어가 존재하면 1, 아니면 0을 부여해서 SIZE_YN 이라는 컬럼을 생성한 후, SELECT문으로 조회한다.

---

• size가 포함된 리뷰 개수 표시하기

SELECT
    SUM(CASE WHEN `REVIEW TEXT` LIKE '%SIZE%' THEN 1 ELSE 0 END) AS N_SIZE,
    COUNT(*) AS N_TOTAL
FROM dataset2;

---

• size가 포함된 리뷰 개수 표시하기  + N_RATE 구하기

SELECT
	A.*
	, N_SIZE / N_TOTAL
FROM (
	SELECT
		SUM(CASE WHEN `REVIEW TEXT` LIKE '%SIZE%' THEN 1 ELSE 0 END) AS N_SIZE,
		COUNT(*) AS N_TOTAL
	FROM dataset2
)A;

서브쿼리를 이용해서 N_SIZE와 N_TOTAL 을 생성한 쿼리를 서브쿼리로 삽입하고, 서브쿼리를 엘리야스로 불러온 다음 N_SIZE / N_TOTAL 을  추가해서 조회한다.

---

• size별 리뷰 개수 조회

SELECT
	SUM(CASE WHEN `REVIEW TEXT`LIKE '%SIZE%' THEN 1 ELSE 0 END) N_SIZE
	, SUM(CASE WHEN `REVIEW TEXT`LIKE '%LARGE%' THEN 1 ELSE 0 END ) N_LARGE
	, SUM(CASE WHEN `REVIEW TEXT`LIKE '%LOOSE%' THEN 1 ELSE 0 END ) N_LOOSE
	, SUM(CASE WHEN `REVIEW TEXT`LIKE '%SMALL%' THEN 1 ELSE 0 END ) N_SMALL
	, SUM(CASE WHEN `REVIEW TEXT`LIKE '%TIGHT%' THEN 1 ELSE 0 END ) N_TIGHT
	, SUM(1) N_TOTAL
FROM dataset2
;

case when 을 이용하여 각각의 사이즈가 포함되면 1, 아니면 0으로 처리하여 개수를 구한다.

---

 

• 연령대별, Department Name 별로 사이즈별 리뷰 개수 조회

SELECT
	floor(AGE/10)*10 AGEBAND
	, `Department Name`
	, SUM(CASE WHEN `REVIEW TEXT`LIKE '%SIZE%' THEN 1 ELSE 0 END) N_SIZE
	, SUM(CASE WHEN `REVIEW TEXT`LIKE '%LARGE%' THEN 1 ELSE 0 END ) N_LARGE
	, SUM(CASE WHEN `REVIEW TEXT`LIKE '%LOOSE%' THEN 1 ELSE 0 END ) N_LOOSE
	, SUM(CASE WHEN `REVIEW TEXT`LIKE '%SMALL%' THEN 1 ELSE 0 END ) N_SMALL
	, SUM(CASE WHEN `REVIEW TEXT`LIKE '%TIGHT%' THEN 1 ELSE 0 END ) N_TIGHT
	, SUM(1) N_TOTAL
FROM dataset2
GROUP BY 1,2
;

 

• 연령대별, Department Name 별로 사이즈별 리뷰 개수 조회

SELECT
    FLOOR(AGE/10)*10 AS AGEBAND,
    `Department Name`,
    SUM(CASE WHEN `REVIEW TEXT` LIKE '%SIZE%' THEN 1 ELSE 0 END) / SUM(1) AS N_SIZE,
    SUM(CASE WHEN `REVIEW TEXT` LIKE '%LARGE%' THEN 1 ELSE 0 END) / SUM(1) AS N_LARGE,
    SUM(CASE WHEN `REVIEW TEXT` LIKE '%LOOSE%' THEN 1 ELSE 0 END) / SUM(1) AS N_LOOSE,
    SUM(CASE WHEN `REVIEW TEXT` LIKE '%SMALL%' THEN 1 ELSE 0 END) / SUM(1) AS N_SMALL,
    SUM(CASE WHEN `REVIEW TEXT` LIKE '%TIGHT%' THEN 1 ELSE 0 END) / SUM(1) AS N_TIGHT,
    SUM(1) AS N_TOTAL
FROM dataset2
GROUP BY 1, 2;

이 전에 작성한 쿼리에 SUM(1) 을 나눠줌으로써 비율을 나타낼 수 있다.

 

• clothing id 별 리뷰 개수 조회

SELECT
	`clothing id`
	, SUM(CASE WHEN `REVIEW TEXT` LIKE '%SIZE%' THEN 1 ELSE 0 END) AS N_SIZE_T
FROM dataset2
GROUP BY 1;

• clothing id 별, 사이즈 별 비율 조회

SELECT
	`clothing ID`
    ,SUM(CASE WHEN `REVIEW TEXT` LIKE '%SIZE%' THEN 1 ELSE 0 END) / SUM(1) AS N_SIZE_T
    ,SUM(CASE WHEN `REVIEW TEXT` LIKE '%LARGE%' THEN 1 ELSE 0 END) / SUM(1) AS N_LARGE
    ,SUM(CASE WHEN `REVIEW TEXT` LIKE '%LOOSE%' THEN 1 ELSE 0 END) / SUM(1) AS N_LOOSE
    ,SUM(CASE WHEN `REVIEW TEXT` LIKE '%SMALL%' THEN 1 ELSE 0 END) / SUM(1) AS N_SMALL
    ,SUM(CASE WHEN `REVIEW TEXT` LIKE '%TIGHT%' THEN 1 ELSE 0 END) / SUM(1) AS N_TIGHT
    ,SUM(1) AS N_TOTAL
FROM dataset2
GROUP BY 1;