25/04/23 프로그래머스 SQL 고득점 Kit

1. 물고기 종류 별 대어 찾기(Level 3)

프로그래머스

 

물고기 종류 별로 가장 큰 물고기의 ID, 물고기 이름, 길이를 출력하는 SQL 문을 작성해주세요.
물고기의 ID 컬럼명은 ID, 이름 컬럼명은 FISH_NAME, 길이 컬럼명은 LENGTH로 해주세요.결과는 물고기의 ID에 대해 오름차순 정렬해주세요.단, 물고기 종류별 가장 큰 물고기는 1마리만 있으며 10cm 이하의 물고기가 가장 큰 경우는 없습니다.

 

-- S: ID, FISH_NAME, LENGTH
-- F: FISH_INFO & FISH_NAME_INFO
-- O: ID ASC
SELECT FI.ID, FN.FISH_NAME, FI.LENGTH
FROM FISH_INFO FI INNER JOIN FISH_NAME_INFO FN ON FI.FISH_TYPE = FN.FISH_TYPE
WHERE (FI.FISH_TYPE, FI.LENGTH) IN (SELECT FISH_TYPE, MAX(LENGTH) AS LENGTH FROM FISH_INFO GROUP BY FISH_TYPE)
ORDER BY ID

 

NOTE: WHERE절에서 서브쿼리 사용할 때 두 개 이상의 컬럼을 동시에 만족하는 조건 설정하는 방법

 

---

2. 연도별 대장균 크기의 편차 구하기(Level 2)

프로그래머스

 

분화된 연도(YEAR), 분화된 연도별 대장균 크기의 편차(YEAR_DEV), 대장균 개체의 ID(ID)를 출력하는 SQL 문을 작성해주세요. 분화된 연도별 대장균 크기의 편차는 분화된 연도별 가장 큰 대장균의 크기 - 각 대장균의 크기로 구하며 결과는 연도에 대해 오름차순으로 정렬하고 같은 연도에 대해서는 대장균 크기의 편차에 대해 오름차순으로 정렬해주세요.

 

1) 서브쿼리 + 조인

-- S: 분화된 연도 YEAR, 연도별 크기의 편차 YEAR_DEV, ID
-- F: ECOLI_DATA
-- O: YEAR ASC, YEAR_DEV ASC
SELECT ED.YEAR, EM.MAX_SIZE - ED.SIZE_OF_COLONY AS YEAR_DEV, ED.ID
FROM 
    (SELECT ID, SIZE_OF_COLONY, YEAR(DIFFERENTIATION_DATE) AS YEAR
    FROM ECOLI_DATA) ED 
LEFT JOIN 
    (SELECT YEAR(DIFFERENTIATION_DATE) AS YEAR, MAX(SIZE_OF_COLONY) AS MAX_SIZE
    FROM ECOLI_DATA
    GROUP BY YEAR(DIFFERENTIATION_DATE)) EM ON ED.YEAR = EM.YEAR
ORDER BY YEAR, YEAR_DEV

 

2) MAX() OVER (PARTITION BY)

-- S: 분화된 연도 YEAR, 연도별 크기의 편차 YEAR_DEV, ID
-- F: ECOLI_DATA
-- O: YEAR ASC, YEAR_DEV ASC
SELECT YEAR(DIFFERENTIATION_DATE) YEAR, 
       MAX(SIZE_OF_COLONY) OVER (PARTITION BY YEAR(DIFFERENTIATION_DATE)) 
       - SIZE_OF_COLONY AS YEAR_DEV, ID
FROM ECOLI_DATA
ORDER BY YEAR, YEAR_DEV

 

NOTE: MAX() OVER (PARTITION BY) 기억!

---

3. 식품분류별 가장 비싼 식품의 정보 조회하기(Level 4)

프로그래머스

 

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

 

-- S: CATEGORY, PRICE, PRODUCT_NAME
-- F: FOOD_PRODUCT
-- W: 식품분류별 가격이 제일 비싼 식품 & 식품분류가 '과자', '국', '김치', '식용유'
-- O: PRICE DESC
SELECT CATEGORY, PRICE AS MAX_PRICE, PRODUCT_NAME
FROM FOOD_PRODUCT
WHERE (CATEGORY, PRICE) IN (
    SELECT CATEGORY, MAX(PRICE) AS MAX_PRICE
    FROM FOOD_PRODUCT
    WHERE CATEGORY IN ('과자','국','김치','식용유')
    GROUP BY CATEGORY)
ORDER BY MAX_PRICE DESC

 

NOTE: RANK(), ROW_NUMBER() 등 윈도우 함수 사용하는 방법도 있지만 어차피 서브쿼리 써야해서 PASS