[SQL][프로그래머스] 연도별 대장균 크기의 편차 구하기 - YEAR + DATE_FORMAT + LEFT OUTER JOIN + GROUP BY + MAX + SCALAR SUBQUERY MYSQL

 

SELECT YEAR(ED.DIFFERENTIATION_DATE) AS YEAR, 
(( select max(ed2.size_of_colony) from ecoli_data as ed2 where year(ed2.DIFFERENTIATION_DATE) = year(ed.DIFFERENTIATION_DATE) ) - ED.SIZE_OF_COLONY) AS YEAR_DEV, ED.ID
FROM ECOLI_DATA AS ED
ORDER BY YEAR ASC, YEAR_DEV ASC;

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

프로그래머스

코드설명

YEAR + DATE_FORMAT + LEFT OUTER JOIN + GROUP BY + MAX + SCALAR SUBQUERY 를 활용합니다.

 

문제에서 특이한점은 DATE_FORMAT을 사용할시 보기에는 같은 결과를 반환하는데 제출시 틀리다는 것 입니다.

DATE_FORMAT과 YEAR, MONTH, DAY 는 어떤 차이가 있을까요??

 

먼저, 제가 주로 DATE_FORMAT을 사용했던 이유는 DATE_FORMAT은 원하는 형식으로 쉽게 데이터를 PARSING할 수 있기 떄문이었습니다.

예를 들어, '2019-08' 이 주어졌다고 했을떄 DATE_FORMAT을 사용할경우에는 아래와 같이 쉽게 작성할 수 있습니다.

DATE_FORMAT(YEARDATE, '%Y-%m')

 

두 함수 모두 각각의 장단점이 있습니다. DATE_FORMAT은 위와 같이 원하는 형식으로 쉽게 만들 수 있다는 점, YEAR, MONTH, DAY는 손쉽게 '년도, 월, 날짜'를 가져올 수 있다는 것 입니다.

 

가장 큰 차이점은 반환형식입니다.

DATE_FORMAT은 문자열을 반환하는 반면, YEAR는 숫자를 반환합니다.

 

이 문제에서 DATE_FORMAT을 사용하지 못하는 것은 정답 CASE에 INTEGER 타입으로 고정시켜놓았기 때문으로 보입니다. 

 

 

이와 같이 SUBQUERY를 사용해도 되지만, LEFT OUTER JOIN으로 처리하는것이 더 빠를 것 입니다.

 

ECOLI_DATA ED1는 기존 테이블.

ECOLI_DATA ED2는 각 연도별로 최대 SIZE_OF_COLONY를 구한 테이블.

 

이 ED1과 ED2를 LEFT OUTER JOIN합니다.

즉, ED1에 ED2를 연결시킵니다. (LEFT OUTER JOIN이기에 만약 ED2값이 존재하지 않는다면 NULL값으로 갱신될 것 입니다. 하지만, 이 문제같은경우 ECOLI_DATA 테이블 1개를 기준으로 작업하기에 NULL이 나올 가능성은 전혀 없습니다.)

SELECT YEAR(ED1.DIFFERENTIATION_DATE) AS YEAR, (ED2_SUB.MAX_COLONY - ED1.SIZE_OF_COLONY) AS YEAR_DEV, ED1.ID
FROM ECOLI_DATA AS ED1
LEFT OUTER JOIN ( SELECT YEAR(ED2.DIFFERENTIATION_DATE) AS YEAR, MAX(ED2.SIZE_OF_COLONY) AS MAX_COLONY FROM ECOLI_DATA AS ED2 GROUP BY YEAR(ED2.DIFFERENTIATION_DATE)) AS ED2_SUB
ON YEAR(ED1.DIFFERENTIATION_DATE) = ED2_SUB.YEAR
ORDER BY YEAR ASC, YEAR_DEV

위와 같이 작성하면, 서브쿼리로 작성한 것보다 훨씬 빠를 것으로 예상됩니다.

코드

INNER JOIN을 활용한 코드입니다.

SELECT YEAR(ED1.DIFFERENTIATION_DATE) AS YEAR, (ED2.MAX_SIZE - ED1.SIZE_OF_COLONY) AS YEAR_DEV, ED1.ID 
FROM ECOLI_DATA AS ED1
INNER JOIN ( SELECT MAX(SIZE_OF_COLONY) AS MAX_SIZE, YEAR(DIFFERENTIATION_DATE) AS MAX_DATE
FROM ECOLI_DATA GROUP BY MAX_DATE) AS ED2
WHERE YEAR(ED1.DIFFERENTIATION_DATE) = ED2.MAX_DATE
ORDER BY YEAR(ED1.DIFFERENTIATION_DATE) ASC, YEAR_DEV ASC

LEFT OUTER JOIN을 활용한 코드입니다. ( 어처피 년도별로 모든 년도가 있으므로 NULL일 경우는 없기에 INNER JOIN과 똑같은 결과가 반환됩니다. )

SELECT YEAR(ED1.DIFFERENTIATION_DATE) AS YEAR, (ED2_SUB.MAX_COLONY - ED1.SIZE_OF_COLONY) AS YEAR_DEV, ED1.ID
FROM ECOLI_DATA AS ED1
LEFT OUTER JOIN ( SELECT YEAR(ED2.DIFFERENTIATION_DATE) AS YEAR, MAX(ED2.SIZE_OF_COLONY) AS MAX_COLONY FROM ECOLI_DATA AS ED2 GROUP BY YEAR(ED2.DIFFERENTIATION_DATE)) AS ED2_SUB
ON YEAR(ED1.DIFFERENTIATION_DATE) = ED2_SUB.YEAR
ORDER BY YEAR ASC, YEAR_DEV

YEAR과 SCALAR SUBQUERY를 활용한 코드입니다..

SELECT 
    YEAR(ED1.DIFFERENTIATION_DATE) AS YEAR, 
    ( ( SELECT MAX(ED2.SIZE_OF_COLONY) FROM ECOLI_DATA AS ED2 WHERE YEAR(ED2.DIFFERENTIATION_DATE) = YEAR(ED1.DIFFERENTIATION_DATE) ) - ED1.SIZE_OF_COLONY) AS YEAR_DEV,
    ED1.ID AS ID
FROM ECOLI_DATA AS ED1
ORDER BY YEAR ASC, YEAR_DEV ASC;

 

DATE_FORMAT을 활용한경우(제출시 결과는 같아보이는데 틀림)

SELECT 
    DATE_FORMAT(ED1.DIFFERENTIATION_DATE, '%Y') AS YEAR, 
    ( ( SELECT MAX(ED2.SIZE_OF_COLONY) FROM ECOLI_DATA AS ED2 WHERE YEAR(ED2.DIFFERENTIATION_DATE) = YEAR(ED1.DIFFERENTIATION_DATE) ) - ED1.SIZE_OF_COLONY) AS YEAR_DEV,
    ED1.ID AS ID
FROM ECOLI_DATA AS ED1
ORDER BY YEAR ASC, YEAR_DEV ASC;

 

 

SELECT절에 서브쿼리를 사용합니다. (매번 연산이 필요하므로 느릴 것으로 예상됩니다.)

 

SELECT YEAR(ED.DIFFERENTIATION_DATE) AS YEAR, (( select max(ed2.size_of_colony) from ecoli_data as ed2 where year(ed2.DIFFERENTIATION_DATE) = year(ed.DIFFERENTIATION_DATE) ) - ED.SIZE_OF_COLONY) AS YEAR_DEV, ED.ID
FROM ECOLI_DATA AS ED
ORDER BY YEAR ASC, YEAR_DEV ASC;

테이블을 조인한뒤, 처리합니다. 이떄 중요점은, MAX(SIZE_OF_COLONY) 해당 년도의 최대 크기를 계산하고, YEAR(DIFFERENTIATION_DATE)는 INNER JOIN을 할떄 결합조건으로 사용되므로 함꼐 SELECT해서 가져와야 합니다. 이떄 서브쿼리는 GROUP BY "년도"로 처리합니다.

SELECT YEAR(ED1.DIFFERENTIATION_DATE) AS YEAR, ED2.MAX_SIZE - ED1.SIZE_OF_COLONY AS YEAR_DEV, ED1.ID AS ID
FROM ECOLI_DATA AS ED1
INNER JOIN (
    select max(size_of_colony) as max_size, year(DIFFERENTIATION_DATE) as max_year from ecoli_data group by year(DIFFERENTIATION_DATE) 
) as ED2
ON YEAR(ED1.DIFFERENTIATION_DATE) = ED2.max_year
ORDER BY YEAR ASC, YEAR_DEV ASC