[SQL] 프로그래머스 SUM, MAX, MIN String, Date : 연도별 대장균 크기의 편차 구하기(level 3)

💡문제 링크

프로그래머스

 

 

💡SQL 코드 설계

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

1. 서브쿼리를 이용해서 연도별 가장 큰 대장균의 크기를 구해야 한다

일단 각 연도별 가장 큰 대장균의 크기를 나타내는 MAX_DATA 테이블을 만들고,

ECOLI_DATA에 MAX_DATA를 LEFT JOIN한다.

ON절에는 당연히 YEAR을 써준다!

LEFT JOIN
    (
    select year(differentiation_date) as year, max(size_of_colony) as max_size
	from ecoli_data
	group by year(differentiation_date)
    ) AS MAX_DATA
ON
    YEAR(ECOLI_DATA.DIFFERENTIATION_DATE) = MAX_DATA.YEAR

2. 분화된 연도별 대장균 크기의 편차는 분화된 연도별 가장 큰 대장균의 크기 - 각 대장균의 크기로 구한다.

LEFT JOIN을 통해 얻은 연도별 가장 큰 대장균 크기 MAX_DATA.MAX_SIZE를 SIZE_OF_COLONY에서 빼주고, ABS()를 통해 절댓값으로 바꾼다.

SELECT 
    YEAR(DIFFERENTIATION_DATE) AS YEAR, 
    ABS(SIZE_OF_COLONY - MAX_DATA.MAX_SIZE) AS YEAR_DEV,
    ID
FROM
    ECOLI_DATA

3. 정렬  ORDER BY YEAR , YEAR_DEV 

 

💡SQL 코드

-- 코드를 입력해주세요.
SELECT 
    YEAR(DIFFERENTIATION_DATE) AS YEAR, 
    ABS(SIZE_OF_COLONY - MAX_DATA.MAX_SIZE) AS YEAR_DEV,
    ID
FROM
    ECOLI_DATA
LEFT JOIN
    (
    select year(differentiation_date) as year, max(size_of_colony) as max_size
	from ecoli_data
	group by year(differentiation_date)
    ) AS MAX_DATA
ON
    YEAR(ECOLI_DATA.DIFFERENTIATION_DATE) = MAX_DATA.YEAR
ORDER BY
    YEAR, YEAR_DEV

 

💡기억할 내용

1. 서브쿼리를 적재적소에 활용하는 게 중요하다!

2. ABS()로 절댓값 표현!