[MYSQL] 평점으로 상품 분류하기 - 실전 예제

이번 예제 역시 교재 SQL로 맛보는 데이터 전처리 분석(노수영 저) 를 참고했다.

목표는 Department 별로 평점이 낮은 주요 상품 10개를 조회하고, 해당 상품들의 리뷰를 볼 것이다.

예제를 다루는 만큼 서브쿼리 사용이 필수라는 것을 알고 바로 시작해보자.

 

 

1. 컬럼 명에 따른 평균 평점 계산하기

일단 제품마다 고객들이 부여한 평점들을 계산해보는 과정을 거쳐야 한다.

 

-- mydata라는 데이터 베이스를 선택함.
USE mydata;

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

 

USE 구문은 여러개 데이터 베이스를 사용할 때, 작업하려는 데이터 베이스를 명시적으로 지정해서 현재 작업중인 데이터 베이스를 설정한다.

즉, 데이터 베이스를 전환할 때 유용하다.

USE구문은 현재 세션에만 반영되는 것이 특징으로, SQL에서 데이터 베이스를 테이블과 액세스 하도록 해준다.

 

 

2. Department 별 순위 구하기

Department와 Clothing_id 두 컬럼의 평균 평점을 계산하고, Department 내에서 평균 평점으로 순위를 매겨준다.

이 때, 우리가 사용하는 함수는 RANK() 함수가 아닌 사용자 지정함수인 ROW_NUMBER()를 사용할 것이다.

 

-- ROW_NUMBER() 
SELECT *
	, ROW_NUMBER() OVER(
    -- DEPARTMENT NAME 열을 기준으로 결과를 그룹화함
		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)
A
;

 

 

3. 상위 10위까지의 데이터 조회

RNK 값이 10까지인 데이터들을 모두 조회해본다.

이 과정을 거치는 이유는 평균평점이 낮은 데이터를 추출하기 위해서 한다.

 

SELECT *
FROM (
   SELECT *
      -- department name을 기준으로 그룹화 + AVG_RATE를 기준으로 순위 측정해 RNK 열에 삽입.
      , 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)
     A) A
WHERE RNK <=10
;

 

 

4. Department 별 평균 평점이 낮은 10개 상품 구하기

이 과정을 위해서 테이블을 하나 생성해야 한다.

왜냐하면 생성한 테이블을 사용해서 Department 별로 평점이 낮은 상품들의 리뷰를 조회하려한다.

 

테이블 생성하기

 

-- 테이블 생성하기
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)
     A) A
-- RNK 값이 10 이하인 행만 선택함.
WHERE RNK <= 10;

SELECT * FROM stat;

 

 

Bottoms의 평점이 낮은 10개 품목 조회하기

서브쿼리를 사용해서 Clothing id에 해당하는 리뷰 내용을 조회하는 방법을 넣어준다.

 

SELECT * FROM dataset2;
SELECT
	`department name`
	, `clothing ID`
	, `Review Text`
FROM dataset2
WHERE `Clothing ID` IN (SELECT `clothing id`
                        FROM stat
                        -- stat 테이블에서 department name이 bottoms인 clothing id
                        WHERE `department name` = 'bottoms')
ORDER BY `clothing id`
;

 

이 부분까지 하면 원 목표에는 도달했다!

그치만 교재에 연령대 별로 낮은 점수를 준 방법까지 함께 다뤘기 때문에 나 역시 함께 작성한다.

 

 

5. 연령별 worst department 구하기

각 연령대별로 가장 낮은 점수를 준  Department를 구하고, 해당 Department의 할인 쿠폰을 발송한다.

연령별로 가장 낮은 점수를 준 department가 구하며, 연령별로 가장 낮은 점수를 준 department에 혜택을 준다.

 

먼저 연령대별로 그룹을 만들고 10대 그룹에 한해, 각 품목에 평균 평점을 구한다.

이렇게 하는 이유는 이 부분이 구현이 되면 서브쿼리로 사용할 수 있고, 확인도 할 겸 해본다.

 

SELECT
	-- 데이터를 그룹화할 부서를 선택함.
	`DEPARTMENT NAME`
    -- 연령을 10으로 나눈후 소수점을 버리고 10을 곱해 연령대를 계산함.
    , FLOOR(AGE/10) * 10 AGEBAND
    -- 각 그룹별로 평균 평점을 계산하며 이름을 재설정함.
    , AVG(RATING) AS AVG_RATING
FROM dataset2
-- 동일한 그룹내 연령대별로 데이터가 그룹화함.
GROUP BY 1, 2
-- 연령대가 10대인 값만 선택함.
HAVING AGEBAND = 10
-- DEPARTMENT NAME과 AGEBAND로 정렬함.
ORDER BY 1, 2
;

 

 

최종 구문

위에서 사용한 서브쿼리를 이용해서 메인쿼리로 각 연령대 내에서 평균 평점을 순위로 매긴다.

그리고 최종적으로는 메인쿼리에서 RNK가 1인 값을 선택해 각 연령대에서 가장 높은 평균 평점을 가진 행을 의미한다.

 

SELECT *
FROM (
	SELECT *
    -- 각 연령대 내에서 평균 평점을 순위로 매김.
	, ROW_NUMBER() OVER(
    -- 연령대를 기준으로 그룹화함.
    PARTITION BY AGEBAND
    -- 각 그룹 내에서 평균 평점을 오름차순으로 정렬함.
    ORDER BY AVG_RATING) RNK
    FROM(
		SELECT `DEPARTMENT NAME`
		, FLOOR(AGE/10) * 10 AGEBAND
		, AVG(RATING) AS AVG_RATING
	FROM dataset2
GROUP BY 1, 2) A
) A
-- 메인쿼리에 RNK가 1인 행을 선택함.
-- 각 연령대에서 가장 높은 평균 평점을 가진 행을 의미함.
WHERE RNK = 1;