SQL 고득점 Kit - 8.12

김동현·2024년 8월 13일

SQL 고득점 Kit

목록 보기

26/56

SUM, MAX, MIN - 연도별 대장균 크기의 편차 구하기

문제

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

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

1차 풀이

SELECT s.YEAR, (s.max_size - e.SIZE_OF_COLONY) AS YEAR_DEV, e.ID
FROM ECOLI_DATA e
INNER JOIN (
    ## 연도별 최고 대장균 크기
    SELECT MAX(SIZE_OF_COLONY) AS max_size, YEAR(DIFFERENTIATION_DATE) AS YEAR
    FROM ECOLI_DATA
    GROUP BY YEAR(DIFFERENTIATION_DATE)
) s
ON YEAR(e.DIFFERENTIATION_DATE) = s.YEAR
ORDER BY YEAR, YEAR_DEV

리뷰

바로 조인을 해서 풀려고 했는데 오류가 계속 발생했다... (알고보니 ON YEAR(e.DIFFERENTIATION_DATE) = s.YEAR 이 부분에서 ON e.YEAR(DIFFERENTIATION_DATE) = s.YEAR 으로 YEAR에 e.을 붙여 말도 안되는 함수를 사용했었음... 이것만 아니면 저걸로 풀텐데...)
2차 풀이를 통해 해결하고 다시 확인한 결과 찾아서 완성한 코드.

2차 풀이

WITH MaxColonyByYear AS (
    SELECT 
        MAX(SIZE_OF_COLONY) AS MAX_SIZE,
        YEAR(DIFFERENTIATION_DATE) AS YEAR
    FROM ECOLI_DATA
    GROUP BY YEAR(DIFFERENTIATION_DATE)
)
SELECT 
    YEAR(e.DIFFERENTIATION_DATE) AS YEAR, 
    (s.MAX_SIZE - e.SIZE_OF_COLONY) AS YEAR_DEV,
    e.ID
FROM ECOLI_DATA e
INNER JOIN MaxColonyByYear s
ON YEAR(e.DIFFERENTIATION_DATE) = s.YEAR
ORDER BY YEAR, YEAR_DEV

리뷰

WITH 을 이용해서 결합하자는 형식으로 하는데 결국 여기서 동일한 방식의 ON이 있어서 사실상 CTE vs 서브쿼리 대결이다.
찾아보니 CTE는 한 번 수행된 결과로 연산이 되는데 서브쿼리는 실행할 때마다 계속 연산이 되어 효율이 안 좋다고 한다. ㅎㅎ
사실 GROUP BY 를 하지 않고 했는데 그 이유는 ON에서 e.YEAR가 안 되는 것을 보고 그것만 해결하면 되는데 어떻게 해결하지? 생각했다. 해결하기 위해 빼고 했는데 그럼 연도별 최대를 구해야하는데 날짜별이잖아... 바보...