[SQL] 연도별 대장균 크기의 편차 구하기, GROUP BY, JOIN

※ 연도별 대장균 크기의 편차 구하기

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

프로그래머스

 

문제 해결 TIP

우선 분화된 연도별 가장 큰 대장균의 크기를 구하는 테이블을 만든다.

SELECT YEAR(DIFFERENTIATION_DATE) AS YEAR, MAX(SIZE_OF_COLONY) AS MAX
FROM ECOLI_DATA
GROUP BY YEAR(DIFFERENTIATION_DATE);

출력 시에는 기존 테이블에 있던 데이터를 출력해야 하고, 위의 테이블과 YEAR칼럼 값을 맞춰주기 위해 아래와 같이 필요한 값들로만 테이블을 새로 생성했다.

SELECT YEAR(DIFFERENTIATION_DATE) AS YEAR, SIZE_OF_COLONY, ID
FROM ECOLI_DATA;

두 테이블을 조인하여 SELECT문에서 편차를 구한 후, ORDER BY절을 통해 분화된 연도에 대해 오름차순으로 정렬하고, 같은 연도에 대해서는 편차별로 오름차순해준다.

 

전체 코드

SELECT A.YEAR AS YEAR, (B.MAX-A.SIZE_OF_COLONY) AS YEAR_DEV, A.ID
FROM (
    SELECT YEAR(DIFFERENTIATION_DATE) AS YEAR, SIZE_OF_COLONY, ID
    FROM ECOLI_DATA) AS A 
JOIN (
    SELECT YEAR(DIFFERENTIATION_DATE) AS YEAR, MAX(SIZE_OF_COLONY) AS MAX
    FROM ECOLI_DATA
    GROUP BY YEAR(DIFFERENTIATION_DATE)) AS B
ON A.YEAR=B.YEAR
ORDER BY YEAR, YEAR_DEV;