[MySQL] (Medium) Reviews of Categories

문제: https://platform.stratascratch.com/coding/10049-reviews-of-categories?code_type=3

StrataScratch - Reviews of Categories

 

 

나의 풀이:

select categories
     , SUM(review_count) as total_reviews_count
from yelp_business
group by categories
order by 1;

 

단순하게 생각하고 이지~~ 했는데 틀렸음!! ㅋㅋ

 

이 문제의 포인트는 categories안에 카테고리가 ; 으로 구분되어 여러개가 묶여있어서  (Asian Fusion;Japanese;Restaurants;Salad)

이걸 분리해줘야한다! 

 

정답 쿼리:

with recursive num (n) as ( -- 반복문 만드는 방법
    select 1
    union all
    select n+1 
    from num
    where n < 12) -- 반복을 멈춰주는 조건

select substring_index(substring_index(categories, ';', n), ';', -1) as category
     , sum(review_count) as review_cnt
from yelp_business
     inner join num on n <= char_length(categories) - char_length(replace(categories, ';', '')) + 1
group by category
order by review_cnt desc

 

0. with recursive num (n) as (~)

재귀쿼리를 사용해서 1 부터 12 까지 숫자를 만든다. 

num이라는 테이블 안에 n이라는 컬럼명으로..

 

1. inner join num on n <= char_length(categories) - char_length(replace(categories, ';', '')) + 1

여기서 n은 해당 카테고리즈에 속해있는 카테고리 수를 가리킨다.

(apple;banana;orange 를 예시로 든다면 여기서 n은 3)

 

여기서 세미콜론을 '' 으로 대체한다 = 삭제 

그래서 대체 전 글자 수에서 대체 후 글자수를 빼면 세미콜론의 수가 남음! 

근데 카테고리 수는 세미콜론보다 한 가 더 많아서 1을 더해준다!

(apple;banana;orange 에서 세미콜론은 2개이고, 카테고리는 3개인 것 처럼)

 

2. inner join num on n <= char_length(categories) - char_length(replace(categories, ';', '')) + 1

카테고리즈 안에 카테고리 수를 찾고 (n), n 이하의 모든 수를 조인한다!

 

apple;banana;orange  3

apple;banana;orange  2

apple;banana;orange  1

 

이렇게!

 

3. select substring_index(substring_index(categories, ';', n), ';', -1) as category

 

SUBSTRING(문자열, 시작 위치)
SUBSTRING(문자열, 시작 위치, 시작 위치부터 가져올 문자수)

 

SUBSTRING_INDEX(문자열, 구분자, 구분자 Index)

 

그러니까,,

substring_index(categories, ';', n) = 문자열에서 n번째 세미콜론까지 (앞) 까지만 가져와라! 

(예시에서 n이 3이면 apple;banana;orange / n이 2면 apple;banana / n이 1이면 apple 을 가져옴)

 

substring_index(substring_index(categories, ';', n), ';', -1)

여기서 한번더 substring_index를 씌워주고, 구분자 인덱스로 -1를 써주면, 

문자열에서 뒤에서 첫번째 세미콜론 까지 문자를 가져온다

(apple;banana;orange -> orange,  apple;banana -> banana /  apple -> apple)

 

4. 그렇게 카테고리를 하나씩,, 구분하고 카테고리로 그룹바이해서 리뷰 수를 썸해주면 됨!

 

몇번 손에 익으면 다음에 잘 쓸 수 있을 것 같다!!!