연도별 최대 대장균 크기 편차 구하기 (MySQL 서브쿼리)

🎯 실무에서도 자주 나오는 "기준값을 비교해서 차이를 구하는" 유형의 SQL 문제이다. 이번 문제를 통해 서브쿼리와 JOIN 구조를 정확히 익혀보자.

---

🔹 문제 개요

ECOLI_DATA라는 테이블에는 실험실에서 배양한 대장균들의 정보가 담겨 있다.
각 대장균에는 분화 일자, 크기, 개체 ID 등이 존재하며, 우리는 아래 조건을 만족하는 결과를 구해야 한다.

✅ 목표:

• 각 대장균의 분화 연도를 추출한다.
• 해당 연도에서 가장 큰 대장균 크기를 구한다.
• 각 개체의 크기를 그 연도 최대 크기에서 빼서 편차(YEAR_DEV)를 계산한다.
• 최종적으로 YEAR, YEAR_DEV, ID를 출력하고,
• YEAR 오름차순, YEAR_DEV 오름차순으로 정렬한다.

---

🔸 예시 데이터

ID	DIFFERENTIATION_DATE	SIZE_OF_COLONY
1	2019-01-01	10
2	2019-01-01	2
3	2020-01-01	100
4	2020-01-01	10
5	2020-01-01	17
6	2021-01-01	101

📌 위 데이터를 기준으로 계산하면:

• 2019년 최대 크기: 10
• 2020년 최대 크기: 100
• 2021년 최대 크기: 101

→ 따라서 ID 2의 YEAR_DEV는 10 - 2 = 8, ID 5는 100 - 17 = 83이 된다.

---

💡 핵심 개념: 서브쿼리 (인라인 뷰)

이 문제의 핵심은 기준값(연도별 MAX)을 먼저 구한 뒤, 각 행과 비교해 편차를 구하는 것이다.
이를 위해 서브쿼리 (인라인 뷰)를 활용한다.

🧩 서브쿼리란?
메인 쿼리 내부에 들어가는 SELECT문으로, 마치 임시 테이블처럼 사용된다.

---

🔸 Step 1: 연도별 최대 크기 구하기

SELECT 
  YEAR(DIFFERENTIATION_DATE) AS YEAR,
  MAX(SIZE_OF_COLONY) AS MAX_SIZE
FROM ECOLI_DATA
GROUP BY YEAR(DIFFERENTIATION_DATE);

📊 결과 예시:

YEAR	MAX_SIZE
2019	10
2020	100
2021	101

이 결과를 기준 테이블로 보고, 원본 테이블과 JOIN할 수 있다.

---

🛠️ 최종 쿼리 해설

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;

---

🧱 쿼리 구조 요약

구문	역할
YEAR(e.DIFFERENTIATION_DATE)	분화 연도 추출
JOIN (...) m	연도별 최대 크기 서브쿼리를 테이블처럼 사용
m.MAX_SIZE - e.SIZE_OF_COLONY	편차 계산 (YEAR_DEV)
ORDER BY YEAR, YEAR_DEV	정렬 조건 지정

---

✅ 마무리: 이번 문제에서 얻을 수 있는 것

• 서브쿼리(인라인 뷰)로 기준값을 만들고 JOIN하는 흐름을 익힐 수 있다.
• 실무에서도 자주 나오는 그룹별 비교/편차 구하기 문제에 응용 가능하다.
• YEAR(), GROUP BY, JOIN, 산술연산, 정렬까지 실전 요소가 다 들어간 문제이다.

📌 앞으로도 기준 데이터를 구해서 각 행과 비교해야 하는 문제가 나오면,
서브쿼리로 기준 테이블을 먼저 만들고, 원본과 JOIN하는 패턴을 떠올리면 된다.

🧠 익숙해지면 SQL이 훨씬 직관적으로 다가오게 된다.