ECOLI_DATA 테이블의 구조는 다음과 같으며, ID, PARENT_ID, SIZE_OF_COLONY, DIFFERENTIATION_DATE, GENOTYPE 은 각각 대장균 개체의 ID, 부모 개체의 ID, 개체의 크기, 분화되어 나온 날짜, 개체의 형질을 나타냅니다.
분화된 연도(YEAR), 분화된 연도별 대장균 크기의 편차(YEAR_DEV), 대장균 개체의 ID(ID) 를 출력하는 SQL 문을 작성해주세요. 분화된 연도별 대장균 크기의 편차는 분화된 연도별 가장 큰 대장균의 크기 - 각 대장균의 크기로 구하며 결과는 연도에 대해 오름차순으로 정렬하고 같은 연도에 대해서는 대장균 크기의 편차에 대해 오름차순으로 정렬해주세요.
생각한 풀이
1. 연도별 SIZE_OF_COLONY의 최대값을 먼저 구하자
2. 해당 연도별 SIZE_OF_COLONY 최대값 - 해당 SIZE_OF_COLONY 값으로 YEAR_DEV를 구하자
3. 연도는 YEAR()로 구분하자
SELECT YEAR(E.DIFFERENTIATION_DATE) AS YEAR,(SELECT MAX(SIZE_OF_COLONY)
FROM ECOLI_DATA
WHERE YEAR(DIFFERENTIATION_DATE)=YEAR(E.DIFFERENTIATION_DATE)
)-E.SIZE_OF_COLONY AS YEAR_DEV, E.ID
FROM ECOLI_DATA E
ORDER BY YEAR, YEAR_DEV🐴 풀이
1. 현재 테이블 ECOLI_DATA E 에서 연도를 YEAR()로 구분하고 AS로 재명명한다
2. 이때 처음에는 SUBSTR()로 잘라서 문제를 풀었는데 결과랑 같은데 틀렸다고 나왔다. 확인해 보니 테이블에서 타입이 DATE라 SUBSTR()로 잘라버리면 문자열로 변경되서 틀린거 같았다
3. 연도별로 구분하기 위해 YEAR(DIFFERENTIATION_DATE)=YEAR(E.DIFFERENTIATION_DATE)로 필터링하고 해당 연도에서 최댓값인 MAX(SIZE_OF_COLONY)를 구한다
4. 구한 값을 E 테이블의 SIZE_OF_COLONY로 빼주고 YEAR_DEV로 재명명한다
5. ORDER BY로 정렬 기준을 잡아준다
윈도우 함수(파티션)을 이용하면 더 간단하게 작성할 수 있다
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, YEAR_DEV
🐴 풀이
1. SELECT절에서 MAX(SIZE_OF_COLONY)의 값을 연도별로 구분한 파티션 별로 추출하고 SIZE_OF_COLONY로 빼준다
2. 파티션을 사용한 방식 이외에는 동일하다
