[SQL] Programmers 문제 풀기 (연도별 대장균 크기의 편차 구하기)

해당 문제는 Programmers에서 사용한 문제이며 모든 테이블의 자료와 출처는 Programmers임을 밝힙니다.

프로그래머스

해당 글에서는 SELF JOIN과 GROUP BY와 여러 집계함수를 이용하여 푸는 문제로 초보자가 하기에는 보통정도의 난이도를 가지고 있습니다.

해당 문제 원본을 확인 하시려면 해당 사이트를 들어가시면 될 것 같습니다.

 

시작하겠습니다.

---

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

해당 문제에서 우선 생각해야 할 것이 연도별 가장 큰 대장균의 크기를 구해야 하는 것이고 해당 크기를 구하였다면 하나의 테이블에 존재를 해야 합니다.(하나의 테이블에 있어야 계산하기 유용하다 판단)

 

제가 이 문제를 풀면서 다 완벽하게 풀었으나, 하나의 문제 때문에 많은 시간을 버린...결과를 초래했습니다..

바로!! YEAR가 DATE 타입으로 설정되어있습니다. 하지만 해당 문제에서는 해당 YEAR를 DATE 타입으로 정렬하지말고 정수형으로 바꾸어서 정렬하기를 원했던 문제 였던 것 같습니다.

 

즉, DATE 타입을 정수형으로 바꾸는 방법은 CONVERT() 함수를 사용해야 합니다.

 

CONVERT() 함수 사용 방법 : 

CONVERT(NOW(), SIGNED) -> 지금 시간을 숫자로 변경

 

이러한 내용을 알고 푸신다면 훨씬 수월하게 풀릴 것입니다.

 

1. 대장균 최대 크기 값과 날짜 컬럼에서 연도로 묶고 연도를 가져와서 출력하기

SELECT DATE_FORMAT(DIFFERENTIATION_DATE,'%Y') AS YEARs
           , MAX(SIZE_OF_COLONY) AS BIG
FROM ECOLI_DATA 
GROUP BY DATE_FORMAT(DIFFERENTIATION_DATE,'%Y')

DATE_FORMAT 함수를 사용하여 DIFFERENTIATION_DATE 해당 컬럼의 연도('%Y')를 기준으로 GROUP BY해주었습니다.

연도별로 묶인 값 중 최대값만 뽑을 수 있도록 MAX 집계 함수를 이용해주었습니다.

코드결과

코드 결과와 같이 해당 연도에서의 대장균 최대 크기 값만 출력된 것을 확인 할 수 있습니다.

---

2. 위에서 구한 쿼리와 기존의 테이블을 JOIN을 사용하여 결합하기

SELECT *
FROM ECOLI_DATA e1
        JOIN
     (SELECT DATE_FORMAT(DIFFERENTIATION_DATE,'%Y') AS YEARs
           , MAX(SIZE_OF_COLONY) AS BIG
      FROM ECOLI_DATA 
      GROUP BY DATE_FORMAT(DIFFERENTIATION_DATE,'%Y')) e2 
      ON DATE_FORMAT(e1.DIFFERENTIATION_DATE,'%Y') = e2.YEARs

저희가 원하는 정답은 연도 별 대장균 크기 차이를 원하는 것이므로 각각의 테이블의 연도를 기준으로 JOIN을 해주면 됩니다.

코드 결과

위의 이미지와 같이 연도를 기준으로 합쳐진 것을 확인 할 수 있고 맨 오른쪽에 BIG 컬럼도 같이 들어간 것을 확인 할 수 있습니다.

---

3. 문제에서 원하는 컬럼 출력과 정렬 하기

SELECT CONVERT(DATE_FORMAT(e1.DIFFERENTIATION_DATE,'%Y'), SIGNED) AS YEAR
     , e2.BIG - e1.SIZE_OF_COLONY AS YEAR_DEV
     , e1.ID
FROM ECOLI_DATA e1
        JOIN
     (SELECT DATE_FORMAT(DIFFERENTIATION_DATE,'%Y') AS YEARs
           , MAX(SIZE_OF_COLONY) AS BIG
      FROM ECOLI_DATA 
      GROUP BY DATE_FORMAT(DIFFERENTIATION_DATE,'%Y')) e2 
      ON DATE_FORMAT(e1.DIFFERENTIATION_DATE,'%Y') = e2.YEARs
ORDER BY YEAR ASC, YEAR_DEV ASC

SELECT 절에서 연도만 원하므로 DATE_FORMAT을 사용해주었습니다.

저희는 이제 DATE 타입을 정수형 타입으로 변환 해주어야 합니다. 그래서 CONVERT() 함수를 사용해주었습니다.

 

대장균 크기 차이를 구하기 위해 BIG 컬럼과 기존의 크기를 가져와 빼주었습니다.

그리고 모든 조건이 만족 되었고 마지막으로 정렬을 해주면 해당 코드는 정답이 됩니다!

---

이상으로 Programmers 문제 풀이를 마치도록 하겠습니다.