Pori_IT

문제

분화된 연도(YEAR), 분화된 연도별 대장균 크기의 편차(YEAR_DEV), 대장균 개체의 ID(ID) 을 출력한다.
분화된 연도별 대장균 크기의 편차는 분화된 연도별 가장 큰 대장균의 크기 - 각 대장균의 크기

결과는 연도에 대해 오름차순으로 정렬하고 같은 연도에 대해서는 대장균 크기의 편차에 대해 오름차순으로 정렬

테이블 & 예제

[ECOLI_DATA]

[Example]

풀이

우선 각 연도별 최대값을 찾아야한다. 이에 딱 맞는 함수가 있으니 바로 OVER이다.

DIFFERENTIATION_DATE의 연도를 찾기위해서 YEAR함수를 사용해준다.

그리고 SIZE_OF_COLONY의 MAX를 찾기위해 OVER와 PARTITION BY를 조합해야한다.

연도별 최대값이므로 PARTITION BY YEAR(DIFFERENTIATION_DATE) 이렇게 작성해준다.

SELECT ID,SIZE_OF_COLONY,
        YEAR(DIFFERENTIATION_DATE) AS YEAR,
        MAX(SIZE_OF_COLONY) OVER (PARTITION BY YEAR(DIFFERENTIATION_DATE) ORDER BY SIZE_OF_COLONY DESC) AS max_size
    FROM ECOLI_DATA

이렇게 CTE를 만들어주면 이제 연도별 최대값인 열이 생기게 된다. 실행결과는 이렇다.

그럼 이제 편차를 구하기위해서 max_size - size_of_colony를 수행해주면 결과를 정상적으로 얻을 수 있다.

전체 코드는 이렇다.

WITH CTE AS (
    SELECT ID,SIZE_OF_COLONY,
        YEAR(DIFFERENTIATION_DATE) AS YEAR,
        MAX(SIZE_OF_COLONY) OVER (PARTITION BY YEAR(DIFFERENTIATION_DATE) ORDER BY SIZE_OF_COLONY DESC) AS max_size
    FROM ECOLI_DATA
)
SELECT YEAR,
    (max_size - size_of_colony) AS YEAR_DEV,
    ID
FROM CTE
ORDER BY YEAR, YEAR_DEV