SQL - 상품 리뷰 데이터 분석

이번 글에서는 의류 상품 리뷰 데이터를 이용한 분석 실습 방법에 대해 알아보자.

이전 글과 이어지는 내용이니, 데이터 준비를 위해 아래 글을 참조하기 바란다.

SQL - MySQL 데이터 준비

 

 

 

 

 

0. 사용 Schema 지정

USE mydata;

• mydata 스키마를 사용

 

 

 

 

 

1. Department별 평균 평점

부서별로 평균 평점을 계산하고, 평균 평점을 내림차순으로 정렬하여 조회해보자.

SELECT
	`DEPARTMENT NAME`
    , AVG(RATING) AVG_RATE
FROM dataset2
GROUP BY 1
ORDER BY 2 DESC;

• "DEPARTMENT NAME" 컬럼을 선택한다.
• 각 부서의 평균 평점을 계산하여 "AVG_RATE"로 표시한다.
• "dataset2" 테이블에서 데이터를 가져옴
• "GROUP BY 1": "DEPARTMENT NAME" 컬럼을 기준으로 그룹화하여 표시한다.
• "ORDER BY 2 DESC": 평균 평점("AVG_RATE")을 내림차순으로 정렬하여 결과를 표시한다.

 

 

 

 

 

2. Department별 제품별 평균 평점

각 부서별 제품별로 평균 평점을 파악하려고 한다.

부서별로 제품별 Top10 순위를 매겨 한눈에 보기 쉽게 만들어보자.

CREATE TEMPORARY TABLE stat AS
SELECT *
FROM (
	SELECT 
		*
		, ROW_NUMBER() OVER(
			PARTITION BY `department name`
			ORDER BY AVG_RATE
			) RNK
	FROM (
		SELECT
			`department name`
			, `clothing id`
			, AVG(rating) AS AVG_RATE
		FROM dataset2
		GROUP BY 1, 2
	) B
) A
WHERE RNK <= 10
;

• Top10 테이블을 이후에도 편하게 쓰기위해 “stat”으로 임시 테이블을 생성해준다
• “WHERE” 절을 만족하는 서브쿼리 내용 전부를 쿼리한다.
• 서브쿼리 "A"
  • "ROW_NUMBER() OVER(PARTITION BY department name ORDER BY AVG_RATE)" 를 통해 부서별로 나누어 평균 평점이 낮은순으로 정렬 후 순위를 매기고 RNK 컬럼으로 표시한다.
• 서브쿼리 "B"
  • "department name"과 "clothing id" 컬럼을 표시하고 평균 평점을 계산해 "AVG_RATE”로 표시해 가져옴
  • "dataset2" 테이블에서 전체 리뷰 데이터를 가져옴
  • 집계 함수를 사용했으니 GROUP BY 1,2를 통해 그룹화함
• “WHERE” 절을 사용해 RNK가 10위 이하인 것들만 필터링한다.

 

 

 

 

 

3. 평점이 낮은 제품들 살펴보기

앞에서 만든 임시 테이블로 ‘Bottoms’ 부서에서 평점이 낮은 Top10 제품을 살펴보자.

SELECT 
	`clothing id`
FROM stat
WHERE `department name` = 'Bottoms';

• **clothing id**컬럼 선택
• “stat" 임시 테이블에서 데이터를 가져옴
• "WHERE” 절을 사용해 “department name = 'Bottoms'"인 경우만 필터링한다.

 

제품 ID를 확인 했으니 이를 바탕으로 리뷰 텍스트를 살펴본다.

SELECT
	 `department name`
     , `clothing id`
     , `Review Text`
FROM dataset2
WHERE `clothing id` IN(
	SELECT `clothing id`
	FROM stat
	WHERE `department name` = 'Bottoms'
	)
ORDER BY `clothing id`
;

• "department name, clothing id, Review Text" 컬럼을 표시
• "dataset2" 테이블에서 데이터를 가져옴
• "WHERE” 절을 사용해 clothing id 가 서브쿼리에서 선택한 값과 일치하는 행들로 필터링한다.
• 서브쿼리: "stat" 임시 테이블에서 "department name"이 'Bottoms'인 행들의 "clothing id"를 선택한다.
• "ORDER BY를 사용해 clothing id를 기준으로 결과를 정렬한다.

 

 

 

 

 

4. 트렌드 부서에서 연령대별 평점이 3점이하인 리뷰 집계

트렌드 부서에서 평점이 3점이하인 리뷰들을 분석하여 개선점을 찾고자 요청을 했다고 치자.

분석을 위해 우선 연령대별로 집계를 해보자.

SELECT
	FLOOR(AGE/10) * 10 AS AGEBAND
    , COUNT(*) CNT
FROM dataset2
WHERE `department name` = 'trend'
	AND RATING <=3
GROUP BY 1
ORDER BY 2 DESC
;

• "FLOOR(AGE/10) * 10 를 사용하여 고객의 나이에서 연령대를 구해주고 “AGEBAND" 컬럼으로 표시한다.
• "COUNT(*)를 사용해 연령대 별로 리뷰수를 세어주고 “CNT" 컬럼으로 표시한다.
• "dataset2" 테이블에서 데이터를 가져옴
• “WHERE” 절을 사용해 평점이 trend 부서 제품에서 평점이 3점이하인 것으로 필터링한다.
• "GROUP BY 1": "AGEBAND" 컬럼을 기준으로 연령대별로 그룹화한다.
• "ORDER BY 2 DESC": 연령대별 리뷰 개수를 내림차순으로 정렬하여 결과를 표시한다.

평점이 3점이하인 리뷰는 50대에서 가장 많다

 

 

 

 

 

5. 50대 3점 이하 Trend 리뷰를 살펴보기

50대에서 평점 3점이하 리뷰가 가장 많은 것을 확인하고 구체적인 리뷰내용을 살펴보고자 한다.

대략적인 내용을 먼저 살펴보기 위해 상위 10개 제품만 먼저 조회해보자.

SELECT `Review Text`
FROM dataset2
WHERE `department name` = 'trend'
	AND rating <= 3
    AND AGE BETWEEN 50 AND 59
LIMIT 10
;

• Review Text  컬럼을 표시
• "dataset2" 테이블에서 리뷰 데이터를 가져온다
• "WHERE” 절을 사용해 트렌드 부서 제품이면서 평점이 3점 이하이고, 구매자가 50대인 경우로 추려준다.
• "LIMIT 10": 결과에서 상위 10개의 행만 조회함

옷의 사이즈와 관련한 내용이 리뷰에서 자주 등장하는 것으로 보인다.

 

 

 

 

 

6. 전체 리뷰 중 Size 가 등장하는 비율 계산

실제로 옷의 사이즈가 리뷰에서 등장하는 지 알기 위해 비율을 알아보자.

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

• FROM 서브쿼리 “A”에서 모든 데이터 쿼리한다.
• 'SIZE'를 포함하는 리뷰 텍스트의 수("N_SIZE")를 전체 리뷰 텍스트의 수("N_TOTAL")로 나눈 결과로, 'SIZE'를 포함하는 리뷰의 비율을 계산하고 "N_SIZE / N_TOTAL AS SIZE_REV_RATIO"로 표시함
• 서브쿼리 "A": "dataset2" 테이블에서 'SIZE'를 포함하는 리뷰 텍스트의 수를 "N_SIZE" 컬럼으로 표시, 전체 리뷰 텍스트의 수를 카운트하여 "N_TOTAL" 컬럼으로 표시함

실제로는 약 30 % 밖에 안된다. 그래도 리뷰의 30 % 정도가 사이즈 관련 불만이 있다는 점에서 추가적으로 분석하여 개선점을 찾는 것이 좋아보인다.

 

 

 

 

7. 제품에 따라 리뷰에서 Size와 관련된 키워드별 비율 살펴보기

각 제품의 리뷰에서 Size와 관련된 키워드('SIZE', 'LARGE', 'LOOSE', 'SMALL', 'TIGHT')를 찾아 해당 키워드가 나온 비율을 계산해서 보여주는 쿼리를 작성해보자

SELECT 
	`clothing id`
    , SUM(CASE WHEN `REVIEW TEXT` LIKE '%SIZE%' THEN 1 ELSE 0 END) N_SIZE_T
	, SUM(CASE WHEN `REVIEW TEXT` LIKE '%SIZE%' THEN 1 ELSE 0 END) / SUM(1) N_SIZE_RATIO
	, SUM(CASE WHEN `REVIEW TEXT` LIKE '%LARGE%' THEN 1 ELSE 0 END) / SUM(1) N_LARGE_RATIO
	, SUM(CASE WHEN `REVIEW TEXT` LIKE '%LOOSE%' THEN 1 ELSE 0 END) / SUM(1) N_LOOSE_RATIO
	, SUM(CASE WHEN `REVIEW TEXT` LIKE '%SMALL%' THEN 1 ELSE 0 END) / SUM(1) N_SMALL_RATIO
	, SUM(CASE WHEN `REVIEW TEXT` LIKE '%TIGHT%' THEN 1 ELSE 0 END) / SUM(1) N_TIGHT_RATIO
FROM dataset2
GROUP BY 1
;

• clothing id 컬럼을 표시함
• "SUM(CASE WHEN REVIEW TEXT LIKE '%SIZE%' THEN 1 ELSE 0 END)"을 통해 'SIZE'를 포함하는 리뷰 텍스트의 수를 계산 후 N_SIZE_T 컬럼으로 표시한다.
• "SUM(CASE WHEN REVIEW TEXT LIKE '%SIZE%' THEN 1 ELSE 0 END) / SUM(1) N_SIZE_RATIO"을 통해 'SIZE'를 포함하는 리뷰 텍스트의 수의 전체 중 비율을 계산하여, N_SIZE_RATIO 컬럼으로 표시한다.
• "SUM(CASE WHEN REVIEW TEXT LIKE '%LARGE%' THEN 1 ELSE 0 END) / SUM(1) N_LARGE_RATIO"을 통해 'LARGE'를 포함하는 리뷰 텍스트의 수의 전체 중 비율을 계산하여, N_LARGE_RATIO 컬럼으로 표시한다.
• "SUM(CASE WHEN REVIEW TEXT LIKE '%LOOSE%' THEN 1 ELSE 0 END) / SUM(1) N_LOOSE_RATIO"을 통해 'LOOSE'를 포함하는 리뷰 텍스트의 수의 전체 중 비율을 계산하여, N_LOOSE_RATIO 컬럼으로 표시한다.
• "SUM(CASE WHEN REVIEW TEXT LIKE '%SMALL%' THEN 1 ELSE 0 END) / SUM(1) N_SMALL_RATIO"을 통해 'SMALL'를 포함하는 리뷰 텍스트의 수의 전체 중 비율을 계산하여, N_SMALL_RATIO 컬럼으로 표시한다.
• "SUM(CASE WHEN REVIEW TEXT LIKE '%TIGHT%' THEN 1 ELSE 0 END) / SUM(1) N_TIGHT_RATIO"을 통해 'TIGHT'를 포함하는 리뷰 텍스트의 수의 전체 중 비율을 계산하여, N_TIGHT_RATIO 컬럼으로 표시한다.
• "dataset2" 테이블에서 데이터를 가져옴
• "GROUP BY 1": "clothing id" 별로 그룹화하여 테이블을 정리해서 보여준다.

위 테이블을 통해 제품별로 사이즈에 관한 이슈가 있는 지 확인해볼 수 있다.