[프로그래머스 MySQL] 연도별 대장균 크기의 편차 구하기 - SUM, MAX, MIN

프로그래머스 SQL 고득점 Kit "연도별 대장균 크기의 편차 구하기" 문제 풀이입니다. 서브쿼리로 연도별 최댓값을 먼저 구하고 원본 테이블과 JOIN해서 편차를 계산하는 방법과, 다중 정렬(ORDER BY)까지 정리했습니다.

---

문제 설명

대장균 배양 정보를 담은 ECOLI_DATA 테이블에서, 분화된 연도별 대장균 크기의 편차를 구하는 문제입니다. 편차는 "해당 연도에 분화된 대장균 중 가장 큰 크기 - 각 대장균의 크기"로 정의됩니다.

출력해야 하는 항목은 분화된 연도(YEAR), 연도별 편차(YEAR_DEV), 대장균 ID(ID)이고, 결과는 연도 오름차순으로 정렬하되 연도가 같으면 편차 오름차순으로 정렬해야 합니다.

테이블 구조

ECOLI_DATA: ID(개체 ID), PARENT_ID(부모 개체 ID, 최초 개체는 NULL), SIZE_OF_COLONY(개체 크기), DIFFERENTIATION_DATE(분화된 날짜), GENOTYPE(형질)

예시 데이터

IDPARENT_IDSIZE_OF_COLONYDIFFERENTIATION_DATEGENOTYPE

1	NULL	10	2019/01/01	5
2	NULL	2	2019/01/01	3
3	1	100	2020/01/01	4
4	2	10	2020/01/01	4
5	2	17	2020/01/01	6
6	4	101	2021/01/01	22

연도별 최댓값은 2019년 10, 2020년 100, 2021년 101이므로, 각 개체의 편차를 구해 정렬하면 결과는 다음과 같습니다.

YEARYEAR_DEVID

2019	0	1
2019	8	2
2020	0	3
2020	83	5
2020	90	4
2021	0	6

풀이 아이디어

이 문제의 핵심은 "연도별 최댓값"이라는 그룹 단위의 값과, 원본 테이블의 개별 행(개체) 정보를 동시에 다뤄야 한다는 점입니다. GROUP BY만으로는 그룹별 대표값(최댓값)만 나올 뿐, 그룹에 속한 개별 개체의 ID까지 함께 출력할 수 없습니다. 그래서 다음과 같은 순서로 접근합니다.

1. 먼저 서브쿼리로 연도별 최댓값만 따로 구합니다(GROUP BY YEAR(DIFFERENTIATION_DATE) + MAX(SIZE_OF_COLONY)).
2. 이 서브쿼리 결과를 원본 ECOLI_DATA 테이블과 연도를 기준으로 JOIN합니다.
3. JOIN된 결과에서 "연도별 최댓값 - 각 개체의 크기"를 계산해 편차를 구합니다.
4. 연도 오름차순, 같으면 편차 오름차순으로 정렬합니다.

코드

 

sql

SELECT 
    YEAR(E.DIFFERENTIATION_DATE) AS YEAR,
    M.MAX_SIZE - 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_SIZE
    FROM ECOLI_DATA
    GROUP BY YEAR(DIFFERENTIATION_DATE)
) M
ON YEAR(E.DIFFERENTIATION_DATE) = M.YEAR
ORDER BY YEAR ASC, YEAR_DEV ASC;

코드 동작 설명

• 서브쿼리 M: ECOLI_DATA를 분화 연도(YEAR(DIFFERENTIATION_DATE))로 그룹화해서, 연도마다 가장 큰 SIZE_OF_COLONY 값을 MAX_SIZE라는 이름으로 뽑아냅니다. 이 시점에는 개체별 ID는 사라지고 "연도-최댓값" 쌍만 남습니다.
• JOIN ... ON YEAR(E.DIFFERENTIATION_DATE) = M.YEAR: 원본 테이블 E의 각 행에, 그 행이 속한 연도의 최댓값(M.MAX_SIZE)을 다시 붙여줍니다. 이 JOIN 덕분에 개체 ID(E.ID)를 잃지 않으면서도 "내가 속한 연도의 최댓값"을 알 수 있게 됩니다.
• M.MAX_SIZE - E.SIZE_OF_COLONY AS YEAR_DEV: 연도별 최댓값에서 각 개체 자신의 크기를 빼서 편차를 계산합니다. 자기 자신이 그 해 최댓값이라면 편차는 0이 됩니다.
• ORDER BY YEAR ASC, YEAR_DEV ASC: 연도로 먼저 정렬하고, 같은 연도 안에서는 편차가 작은 순서(0에 가까운 순서)로 정렬합니다.

예시 데이터로 검증해보면, 2020년 최댓값은 100(ID 3)입니다. 서브쿼리 M에서 (2020, 100)이 나오고, 이 값이 JOIN을 통해 ID 3, 4, 5(모두 2020년 분화)에 각각 붙습니다. 그 결과 ID 3은 100-100=0, ID 4는 100-10=90, ID 5는 100-17=83이 되고, 편차 오름차순으로 정렬하면 3(0) → 5(83) → 4(90) 순서가 되어 문제에서 제시한 결과와 정확히 일치합니다.

 

클로드 AI가 만들어준 sql

  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 ASC, YEAR_DEV ASC;

 

PARTITION BY는 연도별로 그룹을 나누되 행을 뭉치지 않고, MAX() OVER(...)가 그 그룹의 최댓값을 각 행에 그대로 붙여주면서 서브쿼리와 JOIN 없이도 한 번에 같은 결과를 얻을 수 있습니다.

 

마무리

이 문제는 "그룹별 집계값(최댓값)"과 "개별 행 정보(ID)"를 동시에 출력해야 할 때 서브쿼리로 집계를 먼저 구하고, 원본 테이블과 다시 JOIN하는 패턴을 연습하기 좋은 문제였습니다.