[Programmers Lv.4] 식품분류별 가장 비싼 식품의 정보 조회하기 - 131116

간단한 문제라고 생각했는데 GROUP BY 에서의 문제점이 발생하기 때문에 JOIN을 통해 잡아준다.

 

Question

 

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

 

 

Point

 

• JOIN 에서 SELECT 한 값들을 main table과 연결.
• -> group by시 PRODUCT_NAME을 랜덤으로 가져오는 것을 방지.

 

Code (After 25.06.) 좋음

 

SELECT CATEGORY
        , 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 RLIKE '과자|국|김치|식용유') A
WHERE rn = 1
ORDER BY 2 DESC

 

Code (After 25.04.) 좋음

 

WITH FOOD_WITH_MAX AS(
    SELECT *, MAX(PRICE) OVER(PARTITION BY CATEGORY) AS MAX_PRICE
        FROM FOOD_PRODUCT
        WHERE CATEGORY IN ('과자','국','김치','식용유')
)   
SELECT 
        CATEGORY
        , PRICE
        , PRODUCT_NAME
FROM FOOD_WITH_MAX
WHERE PRICE = MAX_PRICE
ORDER BY 2 DESC

 

 

Code (After)

 

SELECT CATEGORY, PRICE AS MAX_PRICE, PRODUCT_NAME
    FROM (SELECT *
            FROM FOOD_PRODUCT
            WHERE CATEGORY IN ('과자','국','김치','식용유')) P1
    WHERE PRICE = (SELECT MAX(PRICE)
                    FROM FOOD_PRODUCT P2
                    WHERE P1.CATEGORY = P2.CATEGORY)
    ORDER BY 2 DESC

 

Code (Before)

 

SELECT F2.CATEGORY, F2.MAX_PRICE, PRODUCT_NAME
    FROM FOOD_PRODUCT F1
    JOIN (SELECT CATEGORY, MAX(PRICE) AS MAX_PRICE
            FROM FOOD_PRODUCT
            WHERE CATEGORY REGEXP '과자|국|김치|식용유'
            GROUP BY CATEGORY) AS F2
      ON F1.CATEGORY = F2.CATEGORY AND F1.PRICE = F2.MAX_PRICE
    ORDER BY MAX_PRICE DESC

 

Wrong Code
: Group by 할 때 집계되지 않은 열 (PRODUCT_NAME)을 함께 사용하려고 할 때 정확한 값 가져오지 못 함.

 

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

 

now me

On my github