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

테이블 정보

Column name	Type	Nullable
ID	INTEGER	FALSE
PARENT_ID	INTEGER	TRUE
SIZE_OF_COLONY	INTEGER	FALSE
DIFFERENTIATION_DATE	DATE	FALSE
GENOTYPE	INTEGER	FALSE

 

 

예시 테이블

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

 

 

문제

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

 

 

아이디어

1. 분화된 연도 = EXTRACT 필요
2. 분화된 연도별 대장균 크기의 편차 = 사칙연산이 필요하다. (분화된 연도별 가장 큰 대장균의 크기 - 각 대장균의 크기)
3. 연도에 대해 오름차순 정렬, 같은 연도에 대해서는 대장균 크기의 편차에 대해 오름차순 정렬 = ORDER BY
4. MAX_VALUE를 알아야 하기에 서브쿼리를 사용해 주자

 

정답

-- 코드를 작성해주세요
SELECT EXTRACT(YEAR FROM E.DIFFERENTIATION_DATE) AS YEAR,
S.MAX_VALUE - E.SIZE_OF_COLONY AS YEAR_DEV,
E.ID
FROM (
    SELECT EXTRACT(YEAR FROM DIFFERENTIATION_DATE) AS YEAR,
    MAX(SIZE_OF_COLONY) AS MAX_VALUE
    FROM ECOLI_DATA
    GROUP BY YEAR
) S, ECOLI_DATA E
WHERE 1=1
AND EXTRACT(YEAR FROM E.DIFFERENTIATION_DATE) = S.YEAR
ORDER BY 1, 2

 

 

참고사항

YEAR을 기준으로 그룹으로 묶어 각 년도별 대장균의 MAX_VALUE를 구해 서브쿼리로 만들었다.

해당 서브쿼리를 참조하여 년도별 대장균 크기의 편차를 출력해 주었다.