10. [SQL] 연습문제 - EDA(1)

분석 목표

의류 쇼핑몰 리뷰 데이터를 분석하여 상품 분류별, 연령대별 평점과 리뷰 양상을 파악하고 분석 결과를 바탕으로 쇼핑몰 매출 향상에 대한 방향을 설정하려고 함.
 

컬럼 설명

- 데이터 자료는 포스팅 아래 쪽에 첨부 -

컬럼 명	설명
Clothing ID	상품번호(uique value)
Age	리뷰 작성자의 연령
Title	리뷰 제목
Review Text	리뷰 내용
Rating	리뷰 작성자가 제출한 평점(1~5)
Recommended IND	리뷰어에 의한 상품 추천 여부
Positive Feedback Count	긍정적 피드백 수
Division Name	상품 분류
Department Name	상품 분류(세부)
Class Name	상품 타입

 

연습문제

-- Q1) Division Name 별로 평점의 평균을 구하라. 단, 평균 평점의 내림차순으로 정렬하라.
select `Division Name`, avg(Rating) `Avg Rate` from dataset2 group by `Division Name` order by `Avg Rate` desc; 

-- Q2) Department Name별로 평점의 평균을 구하라. 단, 평균 평점의 내림차순으로 정렬하라.
select `Department Name`, avg(Rating) `Avg Rate` from dataset2 group by `Department Name` order by `Avg Rate` desc; 

-- Q3) Department Name의 값이 Trend인 항목에 대해서 평점이 3점 이하인 데이터만 출력하라.
select * from dataset2 where `Department Name` = 'Trend' and c <= 3;

-- Q4) 위에서 얻은 결과에 연령대 정보를 추가하라.
select *, case
	when age < 20 then '10대 이하'
    when age < 30 then '20대'
    when age < 40 then '30대'
    when age < 50 then '40대'
    when age < 60 then '50대'
    when age < 70 then '60대'
    else '기타'
end `Age Band`
from dataset2 where `Department Name` = 'Trend' and Rating <= 3;

select *, floor(age/10)*10 `Age Band`
from dataset2 where `Department Name` = 'Trend' and Rating <= 3;

-- Q5) Trend 항목에 3점 이하의 평점을 매긴 건수를 연령대별로 구하라.
select floor(age/10)*10 `Age Band`, count(1) `CNT`
from dataset2
where `Department Name` = 'Trend' and Rating <= 3
group by `Age Band`
order by `CNT` desc;

-- Q6) Trend 항목에 대한 3점 이하의 리뷰 중에서 50대가 남긴 리뷰 10개만 출력하라.
select `Review Text`
from dataset2
where `Department Name` = 'Trend' and Rating <= 3 and age between 50 and 59
limit 10;

-- Q7) Deparment Name 과 ClothingID의 항목별로 평점의 평균을 출력하라. 단, 평균 평점이 높은 것부터 순서대로 정렬하라.
select `Department Name`, `Clothing ID`, avg(Rating) `Avg Rate`
	from dataset2
group by 1, 2
order by 3 desc;

-- Q8) 위의 결과에서 평균 평점이 높은 것부터 순위를 매겨라. 단, 순위는 Department별로 독립적으로 부여하라.
select *, rank() over(partition by `Department Name` order by `Avg Rate` desc) `Rank`
	from (select `Department Name`, `Clothing ID`, avg(Rating) `Avg Rate`
	from dataset2
group by 1, 2)A;

-- Q9) 위의 결과에서 Department 별로 평균 평점 순위가 10위 이내인 데이터만 출력하라.
select *
from (select *, rank() over(partition by `Department Name` order by `Avg Rate` desc) `Rank`
	from (select `Department Name`, `Clothing ID`, avg(Rating) `Avg Rate`
	from dataset2
group by 1, 2)A)B
where `Rank` <= 10
order by `Department Name`;

-- Q10) Department 와 연령대 항목 별로 평점의 평균을 구하라.
select `Department Name`, floor(age/10)*10 `Age Band`, avg(Rating) `Avg Rate`
from dataset2
group by 1, 2
order by 3 desc;

-- Q11) 위의 결과에서 연령대별로 평점 평균이 높은 순서대로 순위를 매겨라.
select *, rank() over(partition by `Age Band` order by `Avg Rate` desc) `Rank`
from(select `Department Name`, floor(age/10)*10 `Age Band`, avg(Rating) `Avg Rate`
from dataset2
group by 1, 2)A
order by `Department Name`;

-- Q12) 리뷰에 size라는 단어가 있으면 1, 없으면 0을 출력하는 필드를 생성하라.
select `Review Text`,
case when `Review Text` like '%size%' then 1
else 0
end
from dataset2;

-- Q13) 전체 리뷰 데이터 수와 'size'가 언급된 리뷰 데이터 수를 구하라.
select count(1) `Total`, sum(case when `Review Text` like '%size%' then 1
else 0
end) `Size_Included`
from dataset2;

-- Q14) 'size', 'large', 'loose', 'small', 'tight'가 각각 언급된 리뷰수와 전체 리뷰수를 구하라.
select 
	sum(case when `Review Text` like "%size%" then 1 else 0 end) as `Size_Included`,
    sum(case when `Review Text` like "%large%" then 1 else 0 end) as `Large_Included`,
    sum(case when `Review Text` like "%loose%" then 1 else 0 end) as `Loose_Included`,
    sum(case when `Review Text` like "%small%" then 1 else 0 end) as `Small_Included`,
    sum(case when `Review Text` like "%tight%" then 1 else 0 end) as `Tight_Included`,
    sum(1) `Total`
from dataset2;

-- Q15) 위의 결과를 Department 별로 출력하라.
select  `Department Name`,
	sum(case when `Review Text` like "%size%" then 1 else 0 end) as `Size_Included`,
    sum(case when `Review Text` like "%large%" then 1 else 0 end) as `Large_Included`,
    sum(case when `Review Text` like "%loose%" then 1 else 0 end) as `Loose_Included`,
    sum(case when `Review Text` like "%small%" then 1 else 0 end) as `Small_Included`,
    sum(case when `Review Text` like "%tight%" then 1 else 0 end) as `Tight_Included`,
    sum(1) `Total`
from dataset2
group by `Department Name`;

 
 

실제 데이터를 가지고 EDA를 전개한다면 추출할 정보들을 스스로 결정해야 한다.
보유하고 있는 도메인 지식을 바탕으로 주어진 데이터에서 의미있게 활용할 수 있는 부분이
어떤 것인지 고민하고 SQL 쿼리문을 작성하여 결과 도출까지 해내야 한다.

 

---

데이터 자료

0.00MB

 
데이터 출처 : https://www.kaggle.com/datasets/nicapotato/womens-ecommerce-clothing-reviews