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

문제 설명

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

Column name	Type	Nullable
ID	INTEGER	FALSE
PARENT_ID	INTEGER	TRUE
SIZE_OF_COLONY	INTEGER	FALSE
DIFFERENTIATION_DATE	DATE	FALSE
GENOTYPE	INTEGER	FALSE

 

문제

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

 

정답

    WITH MAX_TABLE AS(
        SELECT
        MAX(SIZE_OF_COLONY) AS BEST,
        YEAR(DIFFERENTIATION_DATE) AS YEAR
        FROM ECOLI_DATA
        GROUP BY YEAR(DIFFERENTIATION_DATE)
        )
    SELECT
        YEAR(A.DIFFERENTIATION_DATE) AS YEAR,
        (M.BEST-A.SIZE_OF_COLONY) AS YEAR_DEV,
        A.ID
    FROM ECOLI_DATA AS A
    JOIN MAX_TABLE AS M
    ON YEAR(A.DIFFERENTIATION_DATE) = M.YEAR
    ORDER BY
        YEAR(A.DIFFERENTIATION_DATE) ASC,
        (M.BEST-A.SIZE_OF_COLONY) ASC;

풀이

WITH을 이용해 서브쿼리 없이 작성할 수 있었다. 사전에 WITH MAX_TABLE 으로 MAX SIZE_OF_COLONY값을 GROUP BY YEAR을 사용하여 연도별 최대 크기를 구하는 것으로 마무리 한 뒤, 이렇게 임시로 생성해놓은 MAX_TABLE과 ECOLI_DATA를 JOIN할 때 YEAR을 기준으로 삼아 같은 연도에서 MAX_TABLE의 BEST(최대 형질 크기) - SIZE_OF_COLONY(ECOLI_DATA)의 형질 크기를 빼서 문제에서 요구한 크기의 편차를 구할 수 있었다.

 

두번째로 정렬은 더 간단했다. 첫번째 정렬 기준은 연도별이였고, 연도별 같은 크기의 형질이 존재할 수 있으니 두번째 정렬 조건인 형칠 크기의 편차로 다시 오름차순 정렬하여 연도별 가장 큰 대장균의 크기 편차를 구할 수 있었다.