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

문제

문제설명

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

 

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

 

 

 

 입출력 예

 

 

풀이

 

 이 문제를 쉽게 이해하면 년도별로 대장균들을 나눕니다. 이후 각 년도에서 대장균의 최대 크기를 구한 다음, 최대 크기에서 각 대장균 크기의 편차를 구하여 년도, 편차, ID 순으로 출력하면 되는 문제입니다. 정리하면

 

1. 대장균들을 년도별로 구분한다.

2. 각 년도에서 대장균의 최대 크기를 구해준다.

3. 최대 크기에서 각 대장균 크기만큼의 편차를 구한다.

 

와 같이 수행하면 됩니다. SQL문으로 작성하면

SELECT YEAR(DIFFERENTIATION_DATE) AS YEAR, 
(SELECT MAX(SIZE_OF_COLONY) 
FROM ECOLI_DATA
WHERE YEAR(DIFFERENTIATION_DATE) = YEAR) - SIZE_OF_COLONY AS YEAR_DEV, ID
FROM ECOLI_DATA
ORDER BY YEAR, YEAR_DEV

 

와 같이 스칼라 서브쿼리를 이용하여 작성해주면 쉽게 해결할 수 있습니다.

 

여기 문제에서는 스칼라 서브쿼리를 이용하여 작성하였으나, 다른 방법을 찾다보니 JOIN을 이용하는 방법도 있었고, 이 방법이 데이터의 양이 많아질 경우 더 나은 방법이 될 수 있을 것 같아 해당 방법으로도 풀어보면

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

 

와 같이 작성할 수 있습니다.