[SQL] 식품분류별 가장 비싼 식품의 정보 조회하기

문제링크

https://school.programmers.co.kr/learn/courses/30/lessons/131116

프로그래머스

1. 핵심

• WITH AS (서브 쿼리절)
• 왜 틀려? GROUP BY에 정의된 컬럼만 그대로 사용할 수 있다. PRODUCT_NAME은 GROUP BY에 정의되지 않았기 때문에 그대로 사용할 수 없다. 따라서 PRODUCT_NAME을 쓰기위해서는 GROUP_CONCAT, SUBSTRING_INDEX를 사용하자!

SELECT CATEGORY, MAX(PRICE) AS MAX_PRICE, PRODUCT_NAME
  FROM FOOD_PRODUCT 
GROUP BY CATEGORY
HAVING CATEGORY IN ('과자','국','김치','식용유') 
ORDER BY PRICE DESC

• GROUP_CONCAT(): 그룹화된 각 행의 값을 결합하여 하나의 문자열로 만듭니다.
  • 일반적으로 GROUP BY 절과 함께 사용되며, 그룹화된 각 행의 값을 연결하여 결과 집합에 단일 행을 반환
  • 기본적으로 쉼표로 구분된 문자열을 생성
  • GROUP_CONCAT(

id	category	item
1	Appetizer	Salad
2	Appetizer	Soup
3	Main	Steak
4	Main	Pasta
5	Dessert	Cake
6	Dessert	Ice Cream

SELECT category, GROUP_CONCAT(item SEPARATOR ', ') AS menu_items
FROM menu
GROUP BY category;

category	menu_items
Appetizer	Salad, Soup
Main	Steak, Pasta
Dessert	Cake, Ice Cream

 

• SUBSTRING_INDEX() 함수는 주어진 문자열에서 지정된 구분자를 기준으로 지정된 수의 토큰을 반환
  • 일반적으로 문자열에서 특정 부분을 추출할 때 사용됩니다.
  • 첫 번째 인수로는 대상 문자열을, 두 번째 인수로는 구분자를, 세 번째 인수로는 반환할 토큰의 개수를 받음.

SELECT SUBSTRING_INDEX('apple, banana, cherry, date', ', ', 2) AS result;
-- 출력 apple, banana

2. 코드(MySQL)

1. WITH AS (서브쿼리절) 사용하기

with temp as (
    select CATEGORY, max(PRICE) as PRICE
    from FOOD_PRODUCT
    group by CATEGORY
)

select P.CATEGORY, P.PRICE, P.PRODUCT_NAME
from FOOD_PRODUCT P
inner join temp T 
	on (P.CATEGORY = T.CATEGORY and P.PRICE = T.PRICE)
where P.CATEGORY in ('과자', '국', '김치', '식용유')
order by P.PRICE desc;

 

2. 카데고리별 최대값 찾을 때! - GROUP_CONCAT & SUBSTRING_INDEX 사용

•  category로 group by된 상태
• → group_concat을 통해서 각 product_name이 price를 기준 내림차순으로 정렬된 상태로 병합됨.
• → substring_index를 통해서 구분자 ',' 기준 가장 첫 번째 product_name을 가져옴.

select P.CATEGORY, MAX(P.PRICE) as MAX_PRICE, 
       substring_index(group_concat(P.PRODUCT_NAME order by P.PRICE desc), ',', 1)
       AS PRODUCT_NAME
from FOOD_PRODUCT P
where P.CATEGORY in ('과자', '국', '김치', '식용유')
group by P.CATEGORY
order by P.PRICE desc;

 

3. where절 서브쿼리 - 카테고리별 최대가격 맵핑

• 각 카테고리별로 가장 비싼 제품 선택 - 서브쿼리
• 최대 가격을 가진 제품의 정보 선택

select CATEGORY, 
       PRICE as MAX_PRICE, 
       PRODUCT_NAME
from FOOD_PRODUCT 
where (CATEGORY, PRICE) in (
    select CATEGORY, max(PRICE)
    from FOOD_PRODUCT 
    where CATEGORY in ('과자', '국', '김치', '식용유')
    group by CATEGORY
)
order by PRICE desc;

3. 활용예제

• 각 부서별로 근무하는 직원의 이름을 쉼표로 구분하여 결합

SELECT department, GROUP_CONCAT(name SEPARATOR ', ') AS employees_list
FROM employees
GROUP BY department;

• 각 제품의 이름에서 첫 번째 단어 추출 (ex)공백기준 - Apple iPhone 12 - Apple반환

SELECT name,
       SUBSTRING_INDEX(name, ' ', 1) AS first_word
FROM products;