[프로그래머스][MySQL] Lv. 2 연도별 대장균 크기의 편차 구하기

문제 설명

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

프로그래머스

 

주어진 문제의 조건은 다음과 같다. 

- 분화된 연도(YEAR) 출력

- 분화된 연도별 대장균 크기의 편차(YEAR_DEV) = 분화된 연도별 가장 큰 대장균의 크기 - 각 대장균의 크기
- 대장균 개체의 ID(ID)
- YEAR에 대해 오름차순, YEAR_DEV에 대해 오름차순

 

 

정답 풀이

WITH yearMax AS (
    SELECT YEAR(DIFFERENTIATION_DATE) AS YEAR, 
           MAX(SIZE_OF_COLONY) AS Max_Size
    FROM ECOLI_DATA
    GROUP BY YEAR(DIFFERENTIATION_DATE)
)

SELECT 
    yearMax.YEAR,
    (yearMax.Max_Size - ECOLI_DATA.SIZE_OF_COLONY) AS YEAR_DEV, 
    ECOLI_DATA.ID
FROM ECOLI_DATA
INNER JOIN yearMax
ON YEAR(ECOLI_DATA.DIFFERENTIATION_DATE) = yearMax.YEAR
ORDER BY YEAR ASC, YEAR_DEV ASC;

 

 

틀린 풀이

WITH yearMax AS (
    SELECT DATE_FORMAT(DIFFERENTIATION_DATE, '%Y') AS YEAR, 
            MAX(SIZE_OF_COLONY) AS Max_Size
    FROM ECOLI_DATA
    GROUP BY DATE_FORMAT(DIFFERENTIATION_DATE, '%Y')
)

SELECT yearMax.YEAR,
        (yearMax.Max_Size - ECOLI_DATA.SIZE_OF_COLONY) AS YEAR_DEV, 
        ECOLI_DATA.ID
FROM ECOLI_DATA
INNER JOIN yearMax
ON DATE_FORMAT(ECOLI_DATA.DIFFERENTIATION_DATE, '%Y') = yearMax.YEAR
ORDER BY YEAR ASC, YEAR_DEV ASC;

 

위의 쿼리로 진행했을 때, 결과는 동일하게 나오지만 '실패'라고 뜬다. 

 

이유를 모르겠어서 찾아보니...

'DATE_FORMAT' 함수를 사용하면 원래 DATE 였던 자료형이 string 자료형이 되어서 결과는 맞지만 답이 틀리게 나온다고 한다. 

따라서 날짜 타입의 열에서 직접 연도를 추출하는 Year 함수를 사용하여 다시 풀어보았다. 

 

---

전체적인 풀이 과정은 다음과 같다. 

1. CTE를 사용하여 각 연도별 가장 큰 대장균의 크기를 구한다. 

WITH yearMax AS (
    SELECT YEAR(DIFFERENTIATION_DATE) AS YEAR, 
           MAX(SIZE_OF_COLONY) AS Max_Size
    FROM ECOLI_DATA
    GROUP BY YEAR(DIFFERENTIATION_DATE)
)

 

2. 메인 테이블과 with문을 조인하여, 각 대장균 개체의 연도별 크기 편차를 계산하고, 

주어진 조건에 맞게 쿼리를 작성한다. 

SELECT 
    yearMax.YEAR,
    (yearMax.Max_Size - ECOLI_DATA.SIZE_OF_COLONY) AS YEAR_DEV, 
    ECOLI_DATA.ID
FROM ECOLI_DATA
INNER JOIN yearMax
ON YEAR(ECOLI_DATA.DIFFERENTIATION_DATE) = yearMax.YEAR
ORDER BY YEAR ASC, YEAR_DEV ASC;

 

 

다른 사람 풀이

아래는 WITH문을 사용하지 않고, 더 간단하게 푼 풀이이다. 

SELECT YEAR(DIFFERENTIATION_DATE), 
		ABS(SIZE_OF_COLONY - MAX(SIZE_OF_COLONY)) AS YEAR_DEV, 
        ID 
FROM ECOLI_DATA 
GROUP BY YEAR(DIFFERENTIATION_DATE) 
ORDER BY YEAR(DIFFERENTIATION_DATE) ASC, YEAR_DEV ASC;

 

느낀점

YEAR 함수와 DATE_FORMAT 함수의 차이를 분명하게 알게 되었다. 

문제에서 주어진 형식이 DATE 타입이므로, 앞으로도 이러한 컬럼 형식을 잘 맞춰서 푸는 연습을 해야겠다.