[Programmers / MySQL] 연도별 대장균 크기의 편차 구하기 (Level 2)

📌 문제

문제 설명

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

 

최초의 대장균 개체의 PARENT_ID 는 NULL 값입니다.

 

문제

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

 

예시

예를 들어 ECOLI_DATA 테이블이 다음과 같다면

 

분화된 연도별 가장 큰 대장균의 크기는 다음과 같습니다.

2019 : 10
2020 : 100
2021 : 101

따라서 각 대장균의 분화된 연도별 대장균 크기의 편차는 다음과 같습니다.

ID 1 : 10 - 10 = 0
ID 2 : 10 -2 = 8
ID 3 : 100 - 100 = 0

ID 4 : 100 - 10 = 90
ID 5 : 100 - 17 = 83
ID 6 : 101 -101 - 0

이를 분화된 연도에 대해 오름차순으로 정렬하고 같은 연도에 대해서는 대장균 크기의 편차에 대해 오름차순으로 정렬하면 결과는 다음과 같아야 합니다.

---

📌 나의 풀이

Code

SELECT E2.YEAR, (E2.MAX_COLONY - E1.SIZE_OF_COLONY) AS YEAR_DEV, E1.ID
FROM ECOLI_DATA AS E1
JOIN (
    SELECT YEAR(DIFFERENTIATION_DATE) AS YEAR, MAX(SIZE_OF_COLONY) AS MAX_COLONY
    FROM ECOLI_DATA
    GROUP BY YEAR(DIFFERENTIATION_DATE)) AS E2
ON YEAR(E1.DIFFERENTIATION_DATE) = E2.YEAR
ORDER BY YEAR(DIFFERENTIATION_DATE), YEAR_DEV;

Solution

1. JOIN할 서브쿼리 테이블 만들기

• ECOLI_DATA 테이블에서 DIFFERENTIATION_DATA 필드의 YEAR 부분에 대해 그룹화
• YEAR, 그룹 별 SIZE_OF_COLONY 최대값만 가져오기

2. ECOLI_DATA 테이블과 위에서 만든 서브쿼리 테이블을 INNER JOIN하되, YEAR(DIFFERENTIATION_DATE)를 기준으로 JOIN

 

3. YEAR과, 그룹 별 SIZE_OF_COLONY 최대값에서 각 SIZE_OF_COLONY 값을 뺀 값(YEAR_DEV)과, ID만 가져오기

 

4. YEAR에 대해 오름차순 정렬, 같다면 YEAR_DEV에 대해 오름차순 정렬

 

5. ID,  FISH_NAME, LENGTH 컬럼만 출력

---

📌 다른 사람의 풀이

Code

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(DIFFERENTIATION_DATE), YEAR_DEV;

 

Solution

1. YEAR, YEAR_DEV, ID만 ECOLI_DATA 테이블에서 가져오기

• YEAR_DEV는 PARTITION BY를 활용
  1. PARTITION BY YEAR(DIFFERENTIATION_DATE)로 연도 기준 그룹화
  2. 그룹 별로 SIZE_OF_COLONY의 최대값 구하기
  3. 구한 최대값에서 SIZE_OF_COLONY 빼기
  4. 최종 값이 담긴 필드를 YEAR_DEV로 명명

2. YEAR에 대해 오름차순 정렬, 같다면 YEAR_DEV에 대해 오름차순 정렬

---

배운 내용

1. JOIN할 때 기준이 되는 필드가 YEAR(~~)와 같은 형태여도 가능하다.

 

2. 윈도우_함수() OVER (PARTITION BY 컬럼명 ORDER BY 컬럼명) AS 별칭 형태는 GROUP BY된 서브쿼리와 같은 역할을 하는 유용한 표현이다!

• PARTITION BY는 그룹 내에서 별도의 계산이나 집계를 수행할 때 유용합니다.