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

🔎 문제설명

• 출처: 프로그래머스
• 사용언어(RDBMS): SQL(MySQL)
• 문제:

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

✅ 제출답안

# with문을 사용한 답안
WITH MAX_SIZE_BY_YEAR AS (
SELECT YEAR(DIFFERENTIATION_DATE) AS YEAR, MAX(SIZE_OF_COLONY) AS MAX_SIZE
FROM ECOLI_DATA
GROUP BY YEAR(DIFFERENTIATION_DATE)
)
SELECT Y.YEAR, 
       Y.MAX_SIZE - E.SIZE_OF_COLONY AS YEAR_DEV, 
       ID
FROM ECOLI_DATA E LEFT JOIN MAX_SIZE_BY_YEAR Y 
    ON YEAR(DIFFERENTIATION_DATE) = Y.YEAR
ORDER BY 1, 2;

# with문을 사용하지 않은 답안
SELECT A.YEAR,
       A.MAX_SIZE - E.SIZE_OF_COLONY AS YEAR_DEV,
       E.ID
FROM ECOLI_DATA E JOIN (SELECT YEAR(DIFFERENTIATION_DATE) YEAR, 
                               MAX(SIZE_OF_COLONY) AS MAX_SIZE
                       FROM ECOLI_DATA
                       GROUP BY YEAR(DIFFERENTIATION_DATE)) A 
                       ON YEAR(E.DIFFERENTIATION_DATE) = A.YEAR
ORDER BY 1, 2;

🚀 풀이기록

처음 이 문제를 접했을 때 with문을 사용해서 풀었고, 두 번째는 with문을 사용하지 않고 풀었다.

답안을 제출하면서 두번 모두 같은 부분에서 에러를 냈다.
GPT에게 물어본 결과, 위의 두 조건문의 차이는 테이블 별칭과 함수의 결합 방식 때문이라고 한다.

1. 에러가 난 경우

   FROM ECOLI_DATA E LEFT JOIN MAX_SIZE_BY_YEAR Y 
       ON E.YEAR(DIFFERENTIATION_DATE) = Y.YEAR  

   E.YEAR(…)를 테이블 별칭 + 함수 이름형태를 함수 호출이 아닌 컬럼 참조로 착각할 수 있다고 한다. YEAR()는 함수인데다 해당 테이블에는 YEAR로 된 컬럼명이 없어서 에러가 발생했다고 한다.

2. 성공한 코드

   
   FROM ECOLI_DATA E LEFT JOIN MAX_SIZE_BY_YEAR Y 
       ON YEAR(DIFFERENTIATION_DATE) = Y.YEAR
   

   테이블 별칭을 제거하고 YEAR(컬럼명)만 사용한 경우, YEAR(…)함수 안에 직접 컬럼 이름을 넣었고, DIFFERENTIATION_DATE 컬럼은 ECOLI_DATA 테이블에만 존재하므로 에러없이 실행되었다고 한다.

3. 에러 없이 테이블을 명시해서 사용하는 경우

   FROM ECOLI_DATA E LEFT JOIN MAX_SIZE_BY_YEAR Y 
       ON YEAR(E.DIFFERENTIATION_DATE) = Y.YEAR

   YEAR()함수를 그대로 쓰되, 함수안에서만 테이블 별칭을 붙이면 정확하게 해석된다고 한다.