프로그래머스 연도별 대장균 크기의 편차 구하기 [Lv. 2] (MySQL)

프로그래머스

ECOLI_DATA 테이블에서 대장균 개체가 분화되어 나온 연도와, 연도별 크기 편차, 그리고 ID값을 출력해주세요

 

SELECT YEAR(A.DIFFERENTIATION_DATE) AS YEAR,
    (B.MAX_SIZE - A.SIZE_OF_COLONY) AS YEAR_DEV,
    A.ID
FROM ECOLI_DATA A

LEFT JOIN
(
    SELECT YEAR(A.DIFFERENTIATION_DATE) AS YEAR,
        MAX(A.SIZE_OF_COLONY) AS MAX_SIZE
    FROM ECOLI_DATA A
    GROUP BY YEAR(A.DIFFERENTIATION_DATE)
) B 
ON YEAR(A.DIFFERENTIATION_DATE) = B.YEAR
ORDER BY YEAR ASC, YEAR_DEV ASC

 

 

SELECT YEAR(A.DIFFERENTIATION_DATE) AS YEAR,

A 테이블의 DIFFERENTIATION_DATE에서 연도 부분만 추출해서 YEAR라는 별칭을 붙입니다.

• 예) 2024-05-14 → 2024

 

(B.MAX_SIZE - A.SIZE_OF_COLONY) AS YEAR_DEV,

연도별 최대 크기(MAX_SIZE)에서 현재 행의 세포 크기(SIZE_OF_COLONY)를 뺀 값을 YEAR_DEV라는 별칭으로 저장합니다.

• 이 값은 각 연도에서 해당 세포 크기가 최대 크기와 얼마나 차이 나는지를 보여줍니다.

 

FROM ECOLI_DATA A

이 쿼리의 기본 테이블로 ECOLI_DATA 테이블을 A라는 별칭으로 지정합니다.

 

LEFT JOIN
(
    SELECT YEAR(A.DIFFERENTIATION_DATE) AS YEAR,
        MAX(A.SIZE_OF_COLONY) AS MAX_SIZE
    FROM ECOLI_DATA A
    GROUP BY YEAR(A.DIFFERENTIATION_DATE)
) B

서브쿼리를 작성합니다. 이 서브쿼리는 각 연도별로 세포 크기(SIZE_OF_COLONY)의 최대값을 찾습니다.

• GROUP BY로 연도별로 묶어 최대값을 계산하고, 그 결과를 B라는 별칭으로 사용합니다.
• 서브쿼리 결과 예시

 

ON YEAR(A.DIFFERENTIATION_DATE) = B.YEAR

• 원본 테이블 A의 연도와 서브쿼리 B의 연도를 기준으로 LEFT JOIN합니다.
  • LEFT JOIN이므로, A의 모든 행을 유지하면서 매칭되지 않는 연도는 NULL로 채워집니다.

ORDER BY YEAR ASC, YEAR_DEV ASC;

 

• 결과를 연도(YEAR) 기준으로 오름차순 정렬합니다.
• 만약 같은 연도에 여러 개의 행이 있다면, YEAR_DEV 기준으로 오름차순 정렬합니다