MySQL - 상품 리뷰 데이터를 이용한 리포트 작성

본 글은 SQL을 공부하기 위해

<SQL로 맛보는 데이터 전처리 분석> 속 예제를 연습하고 기록한 글이다.

매일 배운 내용을 잘 기록해서 제대로 소화하는 게 이 글의 최종 목표이다.

 

막간 도서 소개

출처 YES 24 (노수영 저)

---

 

5장

상품 리뷰 데이터를 이용한 리포트 작성

 

1. 데이터 생성하기

https://www.kaggle.com/datasets/nicapotato/womens-ecommerce-clothing-reviews

 

위 URL로 접속 후 Download 버튼을 클릭해 CSV 파일을 다운로드한다.

다운로드한 파일은 MySQL Workbench에서 실행한다.

필자는 해당 데이터셋을 dataset2로 명명하였다.

 

2. 데이터 구조 이해하기

dataset2의 기본 정보는 아래와 같다.

 

dataset2

이중 상품 평점을 통해 상품의 문제점과 개선방향을 찾는 게 이번 분석의 주요 목적이라고 할 때,

Size와 관련된 리뷰에 대해 집중적으로 분석하고자 한다.

 

3. Size Complain

먼저 전체 리뷰의 수와 Size가 언급된 리뷰의 수가 몇 개인지부터 살펴보고

사이즈를 Large, Loose, Small, Tight로 상세히 나누어서 살펴본다.

그러기 위해서는 SUM 함수, CASE WHEN, LIKE 연산자를 사용해야 한다.

-- Size가 포함된 리뷰 수 Large, Loose, Small, Tight로 나누어서 보기
SELECT SUM(CASE WHEN `Review Text` LIKE '%SIZE%' THEN 1 ELSE 0 END) AS N_SIZE
,SUM(CASE WHEN `Review Text` LIKE '%LARGE%' THEN 1 ELSE 0 END) AS N_LARGE
,SUM(CASE WHEN `Review Text` LIKE '%LOOSE%' THEN 1 ELSE 0 END) AS N_LOOSE
,SUM(CASE WHEN `Review Text` LIKE '%SMALL%' THEN 1 ELSE 0 END) AS N_SMALL
,SUM(CASE WHEN `Review Text` LIKE '%TIGHT%' THEN 1 ELSE 0 END) AS N_TIGHT
,SUM(1) AS N_TOTAL
FROM dataset2;

여기에서 총 리뷰수는 SUM(1)과 같은 방법으로 계산했는데

이는 데이터 행의 개수를 구하는 COUNT(*)와 동일한 결과를 출력한다.

SUM(1)이 행마다 1이라는 수를 생성하고 이를 합한 결과이기 때문에 동일한 것이다.

 

N_SIZE	N_LARGE	N_LOOSE	N_SMALL	N_TIGHT	N_TOTAL
6133	2508	1105	3432	1445	20603

결과를 살펴봤을 때 Large와 Loose와 비교해 Small, Tight와 관련된 리뷰가 더 많은 것으로 보인다.

이를 카테고리별로 비교해 볼 수 있다.

 

-- Size가 포함된 리뷰 수 Large, Loose, Small, Tight로 나누고 카테고리별로 수치 확인
SELECT `Department Name`
,SUM(CASE WHEN `Review Text` LIKE '%SIZE%' THEN 1 ELSE 0 END) AS N_SIZE
,SUM(CASE WHEN `Review Text` LIKE '%LARGE%' THEN 1 ELSE 0 END) AS N_LARGE
,SUM(CASE WHEN `Review Text` LIKE '%LOOSE%' THEN 1 ELSE 0 END) AS N_LOOSE
,SUM(CASE WHEN `Review Text` LIKE '%SMALL%' THEN 1 ELSE 0 END) AS N_SMALL
,SUM(CASE WHEN `Review Text` LIKE '%TIGHT%' THEN 1 ELSE 0 END) AS N_TIGHT
,SUM(1) AS N_TOTAL
FROM dataset2
GROUP BY 1;

 

SELECT 문에 `Department Name`을 추가해 주고 GROUP BY를 해당 값 1로 묶어준다.

 

*여기에서 주의할 것*

테이블의 열 데이터 타입이 TEXT일 경우,

해당 칼럼은 '' 이 아닌 ``(키보드 상 물결 위치)으로  잡아주어야 에러가 뜨지 않는다. ^ㅁ^

 

Department Name	N_SIZE	N_LARGE	N_LOOSE	N_SMALL	N_TIGHT	N_TOTAL
Intimate	412	171	58	296	120	1558
Dresses	1669	697	294	900	503	5353
Bottoms	1145	296	143	484	265	3230
Tops	2576	1200	570	1527	494	9427
Jackets	288	126	35	199	52	922
Trend	40	17	5	25	9	100
	3	1	0	1	2	13

 

Dresses, Bottoms, Tops에서 사이즈와 관련된 리뷰가 많은 것으로 보이고,

Dresses, Bottoms는 Small, Tight 리뷰가 상대적으로 더 많았다.

이를 연령별로 나누어서 보도록 하자.

 

-- Size가 포함된 리뷰 수 Large, Loose, Small, Tight로 나누고 연령대, 카테고리별로 수치 확인
SELECT FLOOR(AGE/10)*10 AGEBAND
,`Department Name`
,SUM(CASE WHEN `Review Text` LIKE '%SIZE%' THEN 1 ELSE 0 END) AS N_SIZE
,SUM(CASE WHEN `Review Text` LIKE '%LARGE%' THEN 1 ELSE 0 END) AS N_LARGE
,SUM(CASE WHEN `Review Text` LIKE '%LOOSE%' THEN 1 ELSE 0 END) AS N_LOOSE
,SUM(CASE WHEN `Review Text` LIKE '%SMALL%' THEN 1 ELSE 0 END) AS N_SMALL
,SUM(CASE WHEN `Review Text` LIKE '%TIGHT%' THEN 1 ELSE 0 END) AS N_TIGHT
,SUM(1) AS N_TOTAL
FROM dataset2
GROUP BY 1, 2
ORDER BY 1, 2;

 

AGEBAND	Department Name	N_SIZE	N_LARGE	N_LOOSE	N_SMALL	N_TIGHT	N_TOTAL
10	Bottoms	3	2	1	1	1	8
10	Dresses	2	0	0	4	2	13
10	Intimate	2	0	0	1	1	6
10	Jackets	0	0	0	0	0	1
10	Tops	5	1	1	3	1	12
20		1	0	0	1	1	2
20	Bottoms	126	38	17	58	33	365
20	Dresses	232	101	40	136	66	748
20	Intimate	60	26	12	45	23	251

 

위와 같이 단순히 리뷰 수를 확인하다 보면 문제는 Size와 관련된 주된 Complain 내용이 무엇인지 파악하기 힘든 것이다.

따라서 총 리뷰 수로 각 칼럼을 나누어서 각 그룹에서 size 세부 그룹의 비중을 구하는 게 좋다.

총 리뷰 수 N_TOTAL(여기에서는 SUM(1))로 각 컬럼을 나누면 아래와 같다.

 

-- 단순한 리뷰 수 확인 X, 총 리뷰수로 각 컬럼을 나누어서 각 그룹에서 size 세부 그룹의 비중 구하기
SELECT FLOOR(AGE/10)*10 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
ORDER BY 1, 2;

 

AGEBAND	Department Name	N_SIZE	N_LARGE	N_LOOSE	N_SMALL	N_TIGHT	N_TOTAL
10	Bottoms	0.3750	0.2500	0.1250	0.1250	0.1250	8
10	Dresses	0.1538	0.0000	0.0000	0.3077	0.1538	13
10	Intimate	0.3333	0.0000	0.0000	0.1667	0.1667	6
10	Jackets	0.0000	0.0000	0.0000	0.0000	0.0000	1
10	Tops	0.4167	0.0833	0.0833	0.2500	0.0833	12
20		0.5000	0.0000	0.0000	0.5000	0.5000	2
20	Bottoms	0.3452	0.1041	0.0466	0.1589	0.0904	365
20	Dresses	0.3102	0.1350	0.0535	0.1818	0.0882	748
20	Intimate	0.2390	0.1036	0.0478	0.1793	0.0916	251

 

연령대가 10대 중에서는 Bottoms에서 Large, Loose 하다는 내용의 비중이 더 많고

Dresses에서 Small, Tight 하다는 내용의 비중이 더 많은 편이다.

 

4. Clothing ID별 Size Review (제품별 분석)

지금까지는 연령, Department Name  별로 사이즈와 관련된 리뷰를 살펴보았다면

이제는 어떤 상품에 Size와 관련된 리뷰가 많은지 살펴보도록 하자.

 

SELECT `Clothing ID`
, SUM(CASE WHEN `Review Text` LIKE '%SIZE%' THEN 1 ELSE 0 END) AS N_SIZE
FROM dataset2
GROUP BY 1;

 

Clothing ID	N_SIZE
767	0
1080	58
1077	83
1049	11
847	1
858	9

 

이를 사이즈 타입별로 나누어서 보기 위해 아까와 동일하게 CASE WHEN을 추가하고

전체 N_TOTAL로 나누어 각 그룹별로 차지하는 비중을 구해보도록 하자.

 

SELECT `Clothing ID`
,SUM(CASE WHEN `Review Text` LIKE '%SIZE%' THEN 1 ELSE 0 END) AS N_SIZE_T
,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;

 

Clothing ID	N_SIZE_T	N_SIZE	N_LARGE	N_LOOSE	N_SMALL	N_TIGHT	N_TOTAL
767	0	0.0000	0.0000	0.0000	0.0000	0.0000	2
1080	58	0.2358	0.0854	0.0285	0.1667	0.0650	246
1077	83	0.3255	0.1569	0.0549	0.1686	0.0745	255
1049	11	0.4400	0.0000	0.0000	0.1600	0.0400	25
847	1	0.2500	0.2500	0.0000	0.0000	0.0000	4
858	9	0.4500	0.1000	0.1000	0.3500	0.1000	20

 

위와 같이 하면 이제 어떤 옷이 사이즈와 관련된 Complain이 많고, 어떤 타입의 Complain이 많은지 알 수 있다.

case when, like 연산자 등을 활용해서 앞으로 다양한 텍스트 데이터를 어렵지 않게 다룰 수 있을 것 같다!