[SQL] 상품리뷰 데이터 분석 - 1 with MySQL

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

 

 

상품리뷰 데이터 분석

상품리뷰 데이터 분석을 실시하기에 앞서 mydata 스키마에 dataset2로 상품리뷰 데이터를 불러왔다.

데이터는 저자의 깃허브에서 확인할 수 있다.

코드 구현은 VScode에 Mysql을 연동하여 진행했다.

---

데이터세트 컬럼 구조

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	상품의 타입

---

Division 별 평점 분포

---

1. 평균 평점 구하기(단순)

 

가장 쉬운 단계부터 진행해 보자. 

 

• Division Name 별 평균 평점

USE mydata; --mydata 스키마 사용(이후 생략할 것)
SELECT 
    `DIVISION NAME`
    , AVG(rating) AVG_RATE -- 평점 평균 컬럼
FROM dataset2 -- 테이블명
GROUP BY 1 -- DIVISION NAME 으로 그룹화
ORDER BY 2 DESC  -- AVG_RATE 기준으로 정렬(내림차순)
;

 

• Department Name 별 평균 평점

Division Name과 동일하게 코드를 작성해 준다(코드설명 생략)

SELECT 
    `Department Name`
    , AVG(Rating) AVG_RATE
FROM dataset2
GROUP BY 1
ORDER BY 2 DESC
;

 

 

• Trend 평점 3점 이하 리뷰

조건 1. Department Name 컬럼 값이 Trend 

조건 2. Rating 컬럼 값이 3 이하

SELECT 
	* -- 모든 컬럼
FROM dataset2 -- 테이블
WHERE `department name` = 'trend' --- 조건1. trend
	AND RATING <= 3  --- 조건2. 평점 3점이하
;

---

2. CASE WHEN , FlOOR 그룹화

• 연령별 그룹화 > CASE WHEN 사용

CASE WHEN 은 if 문 같은 개념이다. CASE WHEN을 사용하여 연령별로 그룹화를 진행해 보자

SELECT 
	CASE WHEN AGE BETWEEN 0 AND 9 THEN '0009'
    	WHEN AGE BETWEEN 10 AND 19 THEN '1019'
    	WHEN AGE BETWEEN 20 AND 29 THEN '2029'
    	WHEN AGE BETWEEN 30 AND 39 THEN '3039'
    	WHEN AGE BETWEEN 40 AND 49 THEN '4049' END AGEBAND
    , AGE
FROM dataset2
;

 

• 연령별 그룹화 > FLOOR 사용

CASE WHEN을 그룹화에 사용하게 되면 너무 오래 걸리고 복잡하다. FLOOR를 사용하면 한 줄로 간편한 그룹화가 가능하다.

SELECT 
	FLOOR(AGE/10) * 10 AS AGEBAND 
    , AGE
FROM dataset2
WHERE `department name` = 'trend'
	AND rating <= 3
;

 

 

• Trend의 3점 이하 연령별 분포

위에서 구현한 코드를 바탕으로 Trend의 3점 이하 연령별 분포를 구현해 보자

조건 2(`department `= 'Trend'와 rating <= 3)와 개수 칼럼이 필요하다.

 

SELECT 
	FLOOR(AGE/10) * 10 AS AGEBAND -- 연령별 그룹화
    , COUNT(*) CNT - 행 개수 세기
FROM dataset2
WHERE `department name` = 'trend' -- 조건1
	AND rating <= 3 -- 조건2
GROUP BY 1
ORDER BY 2 DESC
;

 

 

 

•  Trend 별 연령별 리뷰 수

SELECT
    FLOOR(AGE/10)*10 AGEBAND
    , COUNT(*) CNT
FROM dataset2
WHERE `department name` = 'Trend'
GROUP BY 1;

 

• 50대 3점 이하 Trend 리뷰

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

 

 

---

평점이 낮은 상품 분석

---

1. Department 별로 평점이 낮은 주요 10개 상품 조회 하기

 

• Department, Clothing Name 별 평균 평점 계산

USE mydata;

SELECT
    `department name`
    , `clothing id`
    , AVG(rating) AS AVG_RATE
FROM dataset2
GROUP BY 1,2
;

 

• Department 별 순위 생성

(1) 위의 쿼리를 서브쿼리로 만들어 메인쿼리의 FROM() 안에 삽입한다.

(2) 메인쿼리에서 ROW_NUMBER로 순위를 매긴다.

 

SELECT -- 순위 매기기
    *
    , ROW_NUMBER() OVER(PARTITION BY `Department Name` ORDER BY `AVG_RATE` ) 순위
FROM( -- 서브쿼리
    SELECT 
    `Department Name`
    , `clothing id`
    , AVG(rating) AS AVG_RATE
    FROM dataset2
    GROUP BY 1,2) A
;

 

 

• 임시 테이블을 생성 후, bottom 1~10위 데이터의 clothing id 조회

위의 쿼리를 메인 쿼리의 서브 쿼리로 삽입한 후, 임시 테이블을 생성한다.

-- clothing ID 만 추출한 뒤, 각 부서별 리뷰 텍스트를 추출
--임시 테이블 생성
CREATE TEMPORARY TABLE stat AS
SELECT *
FROM(
    SELECT
        *
        , ROW_NUMBER() OVER(PARTITION BY `Department Name` ORDER BY `AVG_RATE` ) 순위
    FROM(
        SELECT
        `Department Name`
        , `clothing id`
        , AVG(rating) AS AVG_RATE
        FROM dataset2
        GROUP BY 1,2) A)A
WHERE 순위 <= 10
;

임시테이블인 stat를 가지고 Department Name 이 bottoms 인 clothing id를 조회한다.

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