연도별 대장균 크기의 편차 구하기

https://school.programmers.co.kr/learn/courses/30/lessons/299310

프로그래머스

 

📌 문제 핵심 요약

각 대장균 개체에 대해 다음 정보를 출력해야 한다.

• 분화된 연도
• 해당 연도에서 가장 큰 대장균과 크기 차 : (해당 연도의 최대 SIZE_OF_COLONY) - (해당 개체의 SIZE_OF_COLONY)
• 대장균 개체의 ID

결과는 연도 오름차순, 같은 연도 내에서는 크기 편차 오름차순으로 정렬한다.

 

🔑 핵심 아이디어 : 윈도우 함수로 “연도별 최대값”을 행 단위로 붙이기

이 문제의 포인트는 연도별 최대 크기를 구하면서도 각 행을 유지해야 한다는 점이다. GROUP BY를 사용하면 연도별 최대값은 구할 수 있지만, 개별 대장균의 ID와 함께 출력할 수 없다. 때문에 윈도우 함수를 사용한다.

 

1. 연도별 최대 크기 계산

MAX(SIZE_OF_COLONY) OVER (
    PARTITION BY YEAR(DIFFERENTIATION_DATE)
)

• PARTITION BY로 연도별 그룹을 나눈다.
• 각 연도 그룹 안에서 MAX(SIZE_OF_COLONY)를 계산
• 계산된 최대값은 같은 연도의 모든 행에 붙는다.

2. 크기 편차(YEAR_DEV) 계산

 

이 문제는 연도별 집계 결과를 기준으로 각 행의 값을 비교해야 하는 전형적인 윈도우 함수 문제이다. GROUP BY가 아니라 OVER(PARTITION BY...)를 사용해야 집계와 개별 행을 동시에 처리할 수 있다.

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;