Programmers SQL 고득점 Kit 풀기 - SUM,MAX,MIN

 

이전 글

https://school.programmers.co.kr/learn/courses/30/parts/17043

 

 

서론

이전에 SELECT를 풀었으니, 이제는 SUM,MAX,MIN을 풀어보려고 한다. 문제 수가 얼마 없어서 빠르게 풀 수 있을 것 같다. 문제사이트는 다음과 같다.

https://school.programmers.co.kr/learn/courses/30/parts/17043

 

 

1. 가격이 제일 비싼 식품의 정보 출력하기 - 레벨1

SELECT PRODUCT_ID, PRODUCT_NAME, PRODUCT_CD, CATEGORY, PRICE
FROM FOOD_PRODUCT
ORDER BY PRICE DESC
LIMIT 1;

ORDER BY와 LIMIT을 쓰면 이렇게 풀 수 있다. MAX를 이용한 풀이는 아래와 같다.

 

SELECT PRODUCT_ID, PRODUCT_NAME, PRODUCT_CD, CATEGORY, PRICE
FROM FOOD_PRODUCT
WHERE PRICE = (SELECT MAX(PRICE) FROM FOOD_PRODUCT)

첫 번째 쿼리는 테이블은 한 번만 스캔하지만, 정렬작업이 필요하다. 물론 LIMIT 1로 인해 전체 정렬이 필요하지 않을 수 있다. 

두 번째 쿼리는 MAX를 구하기 위해 전체 스캔과정과, 만족하는 행을 찾기 위한 스캔 총 2번의 스캔이 필요하다. 최대 가격이 동일한 상품이 여러 개인 경우 빠르게 찾아올 수 있다. 

 

두 쿼리 모두  PRICE 컬럼에 인덱스가 있는 경우 이를 활용해 빠르게 조회가 가능하다.

 

 

2. 가장 비싼 상품 구하기 - 레벨1

SELECT MAX(PRICE) as MAX_PRICE
FROM PRODUCT;

설명이 별로 필요없다

 

 

3. 최댓값 구하기 - 레벨1

SELECT MAX(DATETIME) as 시간
FROM ANIMAL_INS

 

 

4. 최솟값 구하기 - 레벨2

SELECT MIN(DATETIME) as 시간
FROM ANIMAL_INS

너무 쉽다

 

 

5. 동물 수 구하기  - 레벨2

SELECT COUNT(*)
FROM ANIMAL_INS

 

 

6. 중복 제거하기 - 레벨2

SELECT COUNT(DISTINCT NAME) as count
FROM ANIMAL_INS

DISTINCT를 사용하는 문제이다. NAME이 중복되지 않도록 SELECT를 하고, 해당 NAME들을 count 집계함수로 씌워주면 된다. 

 

 

7. 조건에 맞는 아이템들의 가격의 총합 구하기 - 레벨2

SELECT SUM(PRICE) as TOTAL_PRICE 
FROM ITEM_INFO 
WHERE RARITY = "LEGEND"

SUM 함수만 알면 되는 문제다.

 

 

8. 물고기 종류 별 대어 찾기 - 레벨3

SELECT fi.ID, fni.FISH_NAME, fi.LENGTH
FROM fish_info as fi
JOIN fish_name_info as fni
ON fi.fish_type = fni.fish_type
WHERE (fi.fish_type, fi.length) IN ( 
    SELECT fish_type, MAX(length)
    FROM fish_info
    GROUP BY fish_type
)
ORDER BY fi.ID;

group by를 잘 몰라서 잘하는 친구의 코드를 참고했다.

1. FISH_TYPE 별로 GROUP BY를 수행하여 최대 길이를 가지는 (FISH_TYPE, LENGTH)의 쌍을 구한다
2. 해당 FISH_TYPE과 LENGTH를 가지는 애를 fish_info 테이블에서 찾아낸다. 어차피 가장 큰 물고기는 한 마리밖에 없기 때문에 그냥 찾아내면 된다.
3. fish_name_info에 JOIN한다.

 

 

9. 잡은 물고기 중 가장 큰 물고기의 길이 구하기 - 레벨1

SELECT CONCAT(MAX(LENGTH), "cm") as MAX_LENGTH
FROM FISH_INFO

함수명을 대충 때려맞췄는데 맞았다. 

 

 

10. 연도별 대장균 크기의 편차 구하기  - 레벨2

SELECT YEAR(DIFFERENTIATION_DATE) AS YEAR,
(
    SELECT MAX(SIZE_OF_COLONY) FROM ECOLI_DATA
    WHERE YEAR(DIFFERENTIATION_DATE) = YEAR
) - SIZE_OF_COLONY AS YEAR_DEV,
ID
FROM ECOLI_DATA
ORDER BY YEAR, YEAR_DEV

문제를 제대로 읽지 않아서 틀렸다. 연도 별 가장 큰 값을조회해서 현재 값을 뺀 값을 YEAR_DEV로 넣어주면 된다.

이 방식은 모든 튜플마다 해당 YEAR에 맞는 최대 SIZE를 찾아내기 때문에 비효율적일 수 있다. 윈도우 함수를 사용한다면 PARTITION BY를 통해 지정된 그룹별로 계산을 한 번에 수행이 가능하다.

 

SELECT 
    YEAR(DIFFERENTIATION_DATE) AS 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

1. 데이터를 YEAR(DIFFERENTIATION_DATE) 별로 파티션을 나눈다
2. 각 파티션 내에서 MAX(SIZE_OF_COLONY) 값을 한 번에 계산한다
3. 계산된 MAX 값을 해당 파티션의 모든 행에 적용한다

 

서브쿼리를 써도 시간 초과가 나지 않고 통과하지만, 실제 서비스를 운영한다면 윈도우 함수를 쓰는 게 효율적이겠다.