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

🔎 문제설명

• 출처: 프로그래머스
• 사용언어(RDBMS): SQL(MySQL)
• 문제:

  FOOD_PRODUCT 테이블에서 식품분류별로 가격이 제일 비싼 식품의 분류, 가격, 이름을 조회하는 SQL문을 작성해주세요. 이때 식품분류가 '과자', '국', '김치', '식용유'인 경우만 출력시켜 주시고 결과는 식품 가격을 기준으로 내림차순 정렬해주세요.

✅ 제출답안

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

🚀 풀이기록

이 쿼리문을 작성할 때 주의할 점이 where문을 거치지 않고 바로 SELECT문에서 MAX()함수를 써서 가장 비싼 가격과 식품 이름을 추출하려는 실수를 범하는 것이다. 만약 아래 코드처럼

WHERE CATEGORY IN ('과자', '국', '김치', '식용유')
      AND (CATEGORY, PRICE) IN (SELECT CATEGORY, MAX(PRICE)
                               FROM FOOD_PRODUCT
                               GROUP BY CATEGORY)

where문에 (CATEGORY, PRICE)로 묶어서 식품 분류와 가장 비싼 가격을 쌍을 지어 조건을 넣어주지 않으면, SELECT문에서 PRODUCT_NAME에는 어떤 행의 PRODUCT_NAME이 올지 정의되지 않거나 예측 불가능한 결과가 나올 수 있다.Gemini에 따르면 대부분의 데이터베이스에서는 해당 그룹의 '첫 번째' 또는 '임의의' PRODUCT_NAME을 반환하게 된다고 한다.

• Gemini가 제안해준 코드
  • 대규모 데이터셋에서는 JOIN을 사용하여 GROUP BY또는 윈도우 함수를 사용하는 것이 더 효율적이라는 제안을 해주었다.
  • 이 쿼리문은 각 카테고리별로 가격이 높은 순서대로 순위를 매기고, 순위가 1인 (가장 비싼) 제품만 선택해서 추출한다.

-- Window Function (ROW_NUMBER() 또는 RANK() 사용)
SELECT CATEGORY, PRICE AS MAX_PRICE, PRODUCT_NAME
FROM (
    SELECT
        CATEGORY,
        PRICE,
        PRODUCT_NAME,
        ROW_NUMBER() OVER (PARTITION BY CATEGORY ORDER BY PRICE DESC) as rn
    FROM FOOD_PRODUCT
    WHERE CATEGORY IN ('과자', '국', '김치', '식용유')
) AS SubQuery
WHERE rn = 1
ORDER BY MAX_PRICE DESC;