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

문제 설명

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

문제

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

해설

🎈 MySQL

-- 윈도우 함수
SELECT EXTRACT(YEAR FROM DIFFERENTIATION_DATE) YEAR,
       (MAX(SIZE_OF_COLONY) 
        OVER (PARTITION BY YEAR(DIFFERENTIATION_DATE)) - SIZE_OF_COLONY) YEAR_DEV,
       id ID
FROM ecoli_data
ORDER BY 1, 2;

집계 함수 vs 윈도우 함수

• 집계 방법
  • 집계 함수 : 여러 행을 하나의 값으로 집계해 그룹별로 결과를 요약
  • 윈도우 함수 : 각 행에 대해 계산을 수행해 그 결과를 반환
• 사용 위치
  • 집계 함수 : SELECT, HAVING, ORDER BY절에서 사용되며 주로 GROUP BY 절과 함께 사용됨
  • 윈도우 함수 : SELECT, ORDER BY절에서 사용되며 주로 OVER 절을 통해 윈도우 범위를 지정함
• 결과 행 수
  • 집계 함수 : 그룹화된 각 그룹에 대해 하나의 결과 행 반환
  • 윈도우 함수 : 원래 행 수와 동일한 결과 행 반환 (각 행에 추가적인 집계 결과 포함)