[프로그래머스] 연도별 대장균 크기의 편차 구하기

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

프로그래머스

 

join을 이용한 복잡한 형편없는 풀이

SELECT YEAR(ECO.DIFFERENTIATION_DATE) AS YEAR
     , MAX.MAX_SIZE - ECO.SIZE_OF_COLONY AS YEAR_DEV
     , ECO.ID
FROM ECOLI_DATA ECO
INNER JOIN 
 (
SELECT YEAR(DIFFERENTIATION_DATE) AS YEAR
     , MAX(SIZE_OF_COLONY) AS MAX_SIZE
     
FROM ECOLI_DATA
GROUP BY YEAR(DIFFERENTIATION_DATE)
     ) MAX
ON YEAR(ECO.DIFFERENTIATION_DATE) = MAX.YEAR
ORDER BY 1, 2

 

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 1, 2

WINDOW 함수를 이용하면 이렇게 간단하게 해결!