[SQL] 연도별 대장균 크기의 편차 구하기

허다람·2024년 5월 22일

대장균들은 일정 주기로 분화하며, 분화를 시작한 개체를 부모 개체, 분화가 되어 나온 개체를 자식 개체라고 합니다.
다음은 실험실에서 배양한 대장균들의 정보를 담은 ECOLI_DATA 테이블입니다. ECOLI_DATA 테이블의 구조는 다음과 같으며, ID, PARENT_ID, SIZE_OF_COLONY, DIFFERENTIATION_DATE, GENOTYPE 은 각각 대장균 개체의 ID, 부모 개체의 ID, 개체의 크기, 분화되어 나온 날짜, 개체의 형질을 나타냅니다.

최초의 대장균 개체의 PARENT_ID 는 NULL 값입니다.

💡 문제

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

🎲 예시

예를 들어 ECOLI_DATA 테이블이 다음과 같다면

분화된 연도별 가장 큰 대장균의 크기는 다음과 같습니다.

2019 : 10
2020 : 100
2021 : 101

따라서 각 대장균의 분화된 연도별 대장균 크기의 편차는 다음과 같습니다.

ID 1 : 10 - 10 = 0
ID 2 : 10 -2 = 8
ID 3 : 100 - 100 = 0

ID 4 : 100 - 10 = 90
ID 5 : 100 - 17 = 83
ID 6 : 101 -101 - 0

이를 분화된 연도에 대해 오름차순으로 정렬하고 같은 연도에 대해서는 대장균 크기의 편차에 대해 오름차순으로 정렬하면 결과는 다음과 같아야 합니다.

📟 소스 코드

📝 MySQL

SELECT 
    YEAR(DIFFERENTIATION_DATE) YEAR
    , MAX(SIZE_OF_COLONY) OVER(PARTITION BY YEAR(DIFFERENTIATION_DATE)) - SIZE_OF_COLONY YEAR_DEV
    , ID
FROM ECOLI_DATA 
ORDER BY YEAR, YEAR_DEV 
-- 각 연도 'YEAR(DIFFERENTIATION_DATE)' 별로 'SIZE_OF_COLONY'의 최대 값을 계산한다.
-- SIZE_OF_COLONY 값을 연도별 최대 SIZE_OF_COLONY 값에서 뺀다.
-- YEAR 함수를 사용해 DIFFERENTIATION_DATE 의 연도만 가져와 출력한다.

DIFFERENTIATION_DATE에 들어있는 데이터 형식이 YY-MM-DD일 경우 DATE_FORMAT을 사용하면 처리과정이 달라 YEAR을 사용해야 한다. 참고

OVER 구문 : 윈도우 함수를 정의하고 어떤 행에 대해 계산할지를 결정
PARTITION BY : 데이터를 특정 기준으로 나누어 각 파티션에 대해 별도로 분석 함수를 적용
OVER와 PARTITION BY를 함께 사용하면, 데이터를 특정 기준으로 나눈 다음 각 파티션에 대해 원하는 계산을 수행할 수 있다.