[SQL] exercise 1) 상품 리뷰 분석 _ 그룹별 평균, 랭킹, 조건 카운팅

use ex_01;

show tables;
select * from dataset2;
# ==> 간단하게 기능 중심으로 체크!!!! 1개 테이블에서 처리를 하려고 함!!!
#     배민의 리뷰 데이터
# 레코드 : 1건 리뷰
# clothin ID : 의류 상품에 대한 코드값
# age : 리뷰 작성자 나이
# title : 리뷰 제목
# review_text: 실제 리뷰 내용
# rating : 만족도 평점( 리뷰 작성자 ) 
# Recom~~ : 이 상품을 추천하냐
# Positive ~~ : 이 리뷰가 도움 되요 숫자.
# Division ~ : 상품이 속한 카테고리 (대분류)
#    Department (중분류)
#         Class ( 소분류)
# ==> 약 2만개 정로 리뷰 데이터를 기반으로 진행!!!

# Q1) Division Name의 종류별로 평점의 평균!!!!!! + 평점 내림차순으로 정렬..
# ==> 개별 2만개의 리뷰 다 보기에 애매해서,,,큰 대분류 기준으로 묶어서 보자!!
select `Division Name`, Rating from dataset2;
#
select `Division Name`, avg(Rating) `Avg_Rating` 
from dataset2
group by 1
order by 2 desc;

# Q.2) Department Name의 종류별로 평점의 평균!!!! + 평점 내림차순으로 정렬..
select `Department Name`, avg(Rating) `Avg_Rating` 
from dataset2
group by 1
order by 2 desc;
 
 
# Q.3) Department Name의 값이 Trend인 항목에 대해서,,,
# ==> 평점이 3점 이하만 필터링 ( 악평인 이유들을 좀 보자!!) 
select * from dataset2 where `Department Name`="Trend";
#
select * from dataset2 
where `Department Name`="Trend" and Rating <= 3;

 
# Q.4) 3번의 데이터를 나이대별로 처리!!! 10대, 20~~ case when
# --> Trend 항목에 대해서 "나이"기준으로 좀 보자!!!!
select case when Age<10 then"0대"
		        when Age<20 then "10대"
            when Age<30 then"30대"
            when Age<40 then"40대"
            when Age<50 then"50대"
            when Age<60 then"60대"
            when Age<70 then"70대"
            else "기타세대" end `AgeBand`
from dataset2 
where `Department Name`="Trend" and Rating <= 3;
# ++ 이런거 귀찮으면,,,, 나눗셈 활용!!
select floor( 11/10)*10; ###floor()--> 내림하는 함수 
#
select floor(Age/10)*10 `AgeBand` 
from dataset2 
where `Department Name`="Trend" and Rating <= 3;

 
# Q5) Trend 항목에 대한 리뷰 평점에 대해서 나이대별로 몇 건인지 확인!!!
# --> 34건의 불만 리뷰들을 세대별로 카운팅을 해서 보자!!!
select floor(Age/10)*10 `AgeBand` 
from dataset2 
where `Department Name`="Trend" and Rating <= 3;
#
select floor(Age/10)*10 `AgeBand`, count(1) `Counts`
from dataset2 
where `Department Name`="Trend" and Rating <= 3
group by 1;
#
select floor(Age/10)*10 `AgeBand`, count(1) `Counts`
from dataset2 
where `Department Name`="Trend" and Rating <= 3
group by 1
order by 2 desc;
# ---> 어...이 Trend 항목에 대한 상품에서는 40~50대 60대 등에서
#      평점이 안 좋네;;;;
# --> 왜 이 분들은 뭐 땜시 안좋은 리뷰를 남겼느다...구체적인 리뷰들 체크!!!
#    +++ gpt걸로 요약/정리/ 내용 파악이 용이함!!!

# Q6) Trend 항목에 대한 리뷰 중에서 50대들의 3점 이하의 리뷰들을 출력( 10개만)
select * from dataset2 
where `Department Name`="Trend" 
       and Rating<=3
			 and Age between 50 and 59
limit 10;
#
select `Review Text` from dataset2 
where `Department Name`="Trend" 
			 and Rating<=3
			 and Age between 50 and 59
limit 10;
# ==> 해당하는 구체적인 리뷰들을 더 탐색해서,,,이유들을 찾아가야 함!!!!!~

# Q7) (Deparment and ClothID)의 항목을 기준으로 평점을 계산을 해서..
# 	--> 출력 부분은 Deparment, clothID, 평점의 평균
# ==> 개별 상품에 대한 만족도를 보자!!!
select `Department Name`, `Clothing ID`, Rating from dataset2;
#
select `Department Name`, `Clothing ID`, avg(Rating )
from dataset2
group by 1, 2;
#
select `Department Name`, `Clothing ID`, avg(Rating )
from dataset2
group by 1, 2
order by 3 desc;
# --> 리뷰 : 2건 --> 5점, 3000건 --> 4.8점

# Q8) 랭킹을 하기는 하는데, Department별로 랭킹을 독립적으로 부여를 하고자 함!!
# 	랭킹의 산정 기준은 7번에서 했던 평점의 평균-->내림차순,,,
# 	--> 출력 : Deparment, clothid, 평점의평균, 랭킹!!!!!!
# ==> 랭킹!!! + 대분류별로 랭킹 부여!!!
select `Department Name`, `Clothing ID`, avg(Rating )
from dataset2
group by 1, 2
order by 3 desc;
# --> Department별로 평점 평균에 의한 순위를 정해보자!!!
select `Department Name`, `Clothing ID`, avg(Rating ) `AVG_Rate`,
		    rank() over(partition by `Department Name` 
		                order by `AVG_Rate` desc) `Rnk`
from dataset2
group by 1, 2
order by `Rnk`;
# ==> 에러 발생 이유 : 랭킹 산정 기준이 1차 가공물 기준....에러!!!
#     1차 가공물을 서브 쿼리로 포장!!!!!
#-------------------------다시 설명----------------------
#**AVG_Rate**는 1차 가공(Group by + avg) 결과인데, 
#SQL에서는 같은 레벨에서 바로 계산된 별칭을 윈도우 함수에서 바로 쓸 수 없음.
#MySQL 같은 DB에서는 SELECT에서 만든 별칭(AVG_Rate)은 
#같은 SELECT 내에서는 윈도우 함수 계산 기준으로 사용 불가.
#즉, rank() over(...)는 아직 계산 전에 
#참조하려고 하기 때문에 에러 발생.
select `Department Name`, `Clothing ID`, avg(Rating ) `AVG_Rate`
from dataset2
group by 1, 2;
# --> 위의 쿼리문의 결과가 A 라는 테이블있다고 생각하고,,무엇을 할지!!!
#     AVG_rate 컬럼의 값을 기반으로 Rank 산정!!
select *, rank() over(partition by `Department Name`
					            order by `AVG_Rate` desc) `Rnk`
from ( 
			select `Department Name`, `Clothing ID`, avg(Rating ) `AVG_Rate`
			from dataset2
			group by 1, 2
        )A;
# ==> *** 주어진 정보들을 바탕으로 1차, 2차, 3차,,,,가공할 때
#     에러가 나면,,,,서브쿼리 활용해서 진행하면 됨!!!! ( ++ 쿼리 효율성!! )

에러 원인:

AVG_Rate는 1차 가공(Group by + avg) 결과인데, SQL에서는 같은 레벨에서 바로 계산된 별칭을 윈도우 함수에서 바로 쓸 수 없음.
MySQL 같은 DB에서는 SELECT에서 만든 별칭(AVG_Rate)은 같은 SELECT 내에서는 윈도우 함수 계산 기준으로 사용 불가.
즉, rank() over(...)는 아직 계산 전에 참조하려고 하기 때문에 에러 발생.

# Q9) 8번 문제에서 너무 많은 항목들이 있어서...
# 	Department 별로 평점 평균 순위가 Top 10만 출력!!!!!
select *, rank() over(partition by `Department Name`
					            order by `AVG_Rate` desc) `Rnk`
from ( 
			select `Department Name`, `Clothing ID`, avg(Rating ) `AVG_Rate`
			from dataset2
			group by 1, 2
        )A
where `Rnk`<=10; -- 에러 발생의 이유가 Rnk???????

#에러 해결_ 서브쿼리로 감싸기
select * 
from (
			select *, rank() over(partition by `Department Name`
					                  order by `AVG_Rate` desc) `Rnk`
		  from ( 
			      select `Department Name`, `Clothing ID`, avg(Rating ) `AVG_Rate`
			      from dataset2
			      group by 1, 2
            )A
	    )B 
where Rnk<=10;
# ==> 에러 문구에서,,,분명 있는 컬럼인거 같은데,,없거나 이상해요..
#     다시 감싸서 처리하는 구조를 한 번 생각해보세요!!!!

에러 발생 원인

where `Rnk` <= 10

여기서 Rnk는 윈도우 함수(rank())로 계산한 값
문제: SQL에서 WHERE 절은 SELECT 이후 계산된 열을 참조할 수 없음
- SQL 실행 순서:
  1. FROM
  2. WHERE
  3. GROUP BY
  4. SELECT
  5. 윈도우 함수 계산
  6. ORDER BY
- 즉, Rnk는 WHERE가 실행될 때 아직 존재하지 않음
그래서 바로 where Rnk <= 10 하면 에러 발생

에러 해결_ 서브쿼리로 감싸기

Step 1: 1차 서브쿼리 – 평균 평점 계산

dataset2 테이블에서 부서별 상품별로 데이터를 묶음
avg(Rating) → 상품의 평균 평점 계산
결과 예시:

Department Name Clothing ID AVG_Rate

Trend	101	4.8
Trend	102	4.5
Classic	201	4.9
Classic	202	4.7

별칭 t1: 1차 가공물을 임시 테이블처럼 쓰겠다는 의미

Step 2: 2차 서브쿼리 – 윈도우 함수 적용핵심 포인트: rank() over(...)

rank() → 순위 계산 함수
partition by 'Department Name' → 부서별로 순위를 따로 매김
order by 'AVG_Rate' desc → 평점 높은 순서로 순위 매김

예시 결과:Department Name Clothing ID AVG_Rate Rnk

Trend	101	4.8	1
Trend	102	4.5	2
Trend	103	4.5	2
Trend	104	4.2	4
Classic	201	4.9	1
Classic	202	4.7	2

주의: 같은 평점이면 rank()는 동점 처리 → 동일 순위 부여, 다음 순위 건너뜀

Step 3: 최종 쿼리 – 상위 10개만 추출

이제 2차 서브쿼리에서 계산된 Rnk를 기준으로 부서별 상위 10개만 선택
여기서 서브쿼리가 필요한 이유:
- SQL 실행 순서:
  1. FROM → t2를 읽음
  2. WHERE → 조건 Rnk <= 10 적용
  3. SELECT → * 출력
- Rnk는 윈도우 함수에서 계산된 값이므로, 바로 WHERE에서 필터링하려면 서브쿼리로 감싸야 함

select * from t2 where Rnk <= 10;
select *, rank() over(partition by `Department Name` order by `AVG_Rate` desc) as Rnk from t1
select `Department Name`, `Clothing ID`, avg(Rating) as AVG_Rate from dataset2 group by `Department Name`, `Clothing ID`
SQL 처리 순서와 단계별 분석

# Q10) Department & 연령대를 기준으로 그룹을 만들어서,, 평점의 평균
# 	---> 출력 : Deparment, 연령대(저는 나눗셈), 평점평균
select `Department Name`, Age, Rating 
from dataset2;
#
select `Department Name`, Age, floor(Age/10)*10, Rating 
from dataset2; -- 특정 기준으로 더 하려거나 조정하려면 casw when~~
#
select `Department Name`, Age, floor(Age/10)*10 `Ageband`, Rating 
from dataset2;
#
select `Department Name`, floor(Age/10)*10 `Ageband`, avg(Rating) 
from dataset2
group by 1, 2; -- 주어진 문제의 묶음의 기준과 내가 나열한 상황에 따라서
 
 
# Q11) 연령대별로 생성한 평점평균에 대한 점수를 기준으로 랭킹을 부여!!!!!
# ==> 의도 : 10번 결과를 바탕으로 랭킹을 부여해보자!!!!
select `Department Name`, floor(Age/10)*10 `Ageband`, avg(Rating) `AVG` 
from dataset2
group by 1, 2;
#
select *, rank() over(partition by `Ageband` order by `AVG` desc) `Rnk`
from (
			select `Department Name`, floor(Age/10)*10 `Ageband`, avg(Rating) `AVG` 
			from dataset2
			group by 1, 2
     )A;

# Q12) 리뷰중에서 size 관련된 언급이 있는 리뷰인지 아닌지 체크용 필드 생성.
#  	-> 리뷰 필드에 size라는 단어가 있으면 1, 없으면 0으로 출력!!!
#  	-> 출력 : 리뷰, size언급유무
# : 참고) 요즘은 이런 것을 내가 유사검색보다는 LLM 잘 함!!!
# 기능 : 유사 검색 + 카운팅!!!---> 내가 포커스한 것 기준
#     : 내가 원하는 size 단어가 리뷰에 있으면, 1로 변경,,아니면/없으면 0으로 변경 
#       sum + case /when~~
select * from dataset2;
#
select `Review Text` from dataset2;
#
select `Review Text`,
		   case when `Review Text` like "%size%" then 1 else 0 end `Size`
from dataset2;
#
select count(1),
		   sum(case when `Review Text` like "%size%" then 1 else 0 end )`SizeCnt`
from dataset2;

# Q13) 전체 리뷰 데이터 수하고, size가 언급된 리뷰데이터 수 하고 출력!!!!
## 똑같아서 안 함 

# Q14) 리뷰 중에서 size언급된 리뷰수, large언급된 리뷰수,
#            	    loose언급된 리뷰수, small언급된 리뷰수,
#            	    tight언급된 리뷰수, 전체 리뷰수
select count(1) `TotCnt`,
		    sum(case when `Review Text` like "%size%" then 1 else 0 end )`SizeCnt`,
        sum(case when `Review Text` like "%large%" then 1 else 0 end )`LargeCnt`,
        sum(case when `Review Text` like "%loose%" then 1 else 0 end )`looseCnt`,
        sum(case when `Review Text` like "%small%" then 1 else 0 end )`SmallCnt`,
        sum(case when `Review Text` like "%tight%" then 1 else 0 end )`tightCnt`
from dataset2;

# Q15) 14번의 해당한는 항목들을 Department 별로 보자!!!!!!
select `Department Name`,count(1) `TotCnt`,
		    sum(case when `Review Text` like "%size%" then 1 else 0 end )`SizeCnt`,
        sum(case when `Review Text` like "%large%" then 1 else 0 end )`LargeCnt`,
        sum(case when `Review Text` like "%loose%" then 1 else 0 end )`looseCnt`,
        sum(case when `Review Text` like "%small%" then 1 else 0 end )`SmallCnt`,
        sum(case when `Review Text` like "%tight%" then 1 else 0 end )`tightCnt`
from dataset2
group by 1;

# ==> 기본적인 탐색을 하는 쿼리 중심으로 알아봤습니다!!!
# --> 본인이 찾아야 할 정보/처리할 정보들이 무엇인지 정확히 파악!!!
# --> 필터링 + 기존값 vs 새롭게 변경(case when ~~~~ etc)
#     + 카운팅( count, sum ~ 1/0 etc )
# --> 1차적 --> 2차 --> 3차 ---> 점진적으로 일단은 할 수 있다!!
#     (from 자리에 ()A 이런식의 참조 상요해서 진행하면 됨!!!)
#     시험에서 하나씩 처리하는 방식으로 일단은 접근해 보세요!!!!!

'SQL' 카테고리의 다른 글

[SQL] 집계와 랭킹 _CASE WHEN, GROUP BY, RANK (0)	2025.08.18
[SQL] Netflix 데이터 분석하기 (2)	2025.08.18
[SQL] SELECT 문 확장 – 다중 테이블 JOIN 활용 (6)	2025.08.14
[SQL] SELECT 문 확장 – JOIN, 집계, CASE WHEN (4)	2025.08.14
[SQL] SELECT 문 활용 – JOIN (INNER/LEFT/RIGHT/CROSS) (2)	2025.08.14

nemonemonemo.log

[SQL] exercise 1) 상품 리뷰 분석 _ 그룹별 평균, 랭킹, 조건 카운팅

에러 발생 원인

'SQL' 카테고리의 다른 글

티스토리툴바

[SQL] exercise 1) 상품 리뷰 분석 _ 그룹별 평균, 랭킹, 조건 카운팅

에러 발생 원인

'SQL' 카테고리의 다른 글

관련글

티스토리툴바