Lv.2 연도별 대장균 크기의 편차 구하기

문제

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

예제

나의 풀이

연도별 대장균 크기의 편차를 구하는 건데 "연도별"을 빼먹어서 몇 번 더 풀었다... 문제 잘 읽어봐야지

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

다른 풀이

찾아보니까 윈도우 함수를 사용하면 성능면에서도 더 낫고, 코드도 아주 간단해서 참고하기 좋을 것 같다.

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 ASC, YEAR_DEV ASC;