[SUM, MAX, MIN] 연도별 대장균 크기의 편차 구하기 (MySQL)

프로그래머스 링크텍스트

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

풀이

• 서브쿼리를 사용해 연도별 최대 대장균 크기를 계산
  2019 : 10
  2020 : 100
  2021 : 101
• 메인쿼리에서 서브쿼와 조인하여 각 개체의 크기 편차를 계산
• WITH절을 사용해서 서브쿼리 정리

WITH MAX_SIZE_BY_YEAR AS(
        SELECT YEAR(DIFFERENTIATION_DATE) AS YEAR, MAX(SIZE_OF_COLONY) AS MAX_SIZE
        FROM ECOLI_DATA
        GROUP BY DIFFERENTIATION_DATE
)

SELECT YEAR(DIFFERENTIATION_DATE) AS YEAR, (M.MAX_SIZE - E.SIZE_OF_COLONY) AS YEAR_DEV, ID
FROM ECOLI_DATA E
JOIN MAX_SIZE_BY_YEAR M
ON YEAR(E.DIFFERENTIATION_DATE) = M.YEAR
ORDER BY YEAR ASC, YEAR_DEV ASC;