[프로그래머스] 👩🏻‍💻 (MySQL) 연도별 대장균 크기의 편차 구하기

1. 문제 설명

문제

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

---

2. 접근방법

 

가장 큰 대장균의 크기를 구해줘야 하므로 MAX(SIZE_OF_COLONY)를 해 줘야 할 것 같고 연도별로 구해야 하니까 윈도우 함수써 줘서 연도로 파티셔닝 해 주어야 한다. 일단 이 작업을 먼저 한 후에 해당 결과를 가지고 다시 데이터를 추출 해 주고 ORDER BY 정렬을 해 주어야 겠다고 생각했다. 

 

먼저, 편차 계산에 필요한 컬럼을 불러오는 작업을 한다. 쿼리의 목적은 연도별 대장균 크기 편차(YEAR_DEV)를 계산하고, 연도(YEAR)와 대장균 개체(ID)를 함께 출력하는 것이다. 따라서

- ID
- SIZE_OF_COLONY
- YEAR(DIFFERENTIATION_DATE)
- MAX(SIZE_OF_COLONY) OVER (PARTITION BY YEAR(DIFFERENTIATION_DATE))

 

를 불러와야 한다.

여기까지 쿼리를 짜면 이렇게 된다.

SELECT 
    ID, 
    SIZE_OF_COLONY, 
    YEAR(DIFFERENTIATION_DATE) AS YEAR, 
    MAX(SIZE_OF_COLONY) OVER (PARTITION BY YEAR(DIFFERENTIATION_DATE)) AS MAX_SIZE
FROM ECOLI_DATA

 

이렇게 임시 테이블을 만들어 주었으면 이 테이블을 FROM 절에 넣어줘서 서브쿼리로 활용한다.

대장균 크기 편차 계산을 하고, 오름차순 정렬을해 주면 된다. 

---

3. 정답코드

SELECT 
    YEAR,
    (MAX_SIZE - SIZE_OF_COLONY) AS YEAR_DEV,
    ID
FROM (
    SELECT 
        ID,
        SIZE_OF_COLONY,
        YEAR(DIFFERENTIATION_DATE) AS YEAR,
        MAX(SIZE_OF_COLONY) OVER (PARTITION BY YEAR(DIFFERENTIATION_DATE)) AS MAX_SIZE
    FROM ECOLI_DATA
) AS TEMP
ORDER BY YEAR, YEAR_DEV;

 

서브쿼리에 ALIAS를 꼭 줘야 한다. 데이터베이스는 해당 서브쿼리 결과를 임시 테이블로 간주하기 때문에, 이 테이블을 참조하려면 반드시 이름을 알아야 하기 때문이다. 

 

그리고 처음에는 SELECT 절에 YEAR(DIFFERENTIATION_DATE)라고 써서 오류가 났는데 이미 서브쿼리에서 별칭을 주었기 때문에 그냥 YEAR이라고 써 주니까 정답 처리가 됐다.

---

DATE에서 연도 추출할 수 있는 함수는 `YEAR()`말고도 `EXTRACT`도 있다.

`EXTRACT(YEAR FROM 컬럼명)` 형식으로 사용한다.

SELECT 
    EXTRACT(YEAR FROM DIFFERENTIATION_DATE) AS YEAR,
    (MAX_SIZE - SIZE_OF_COLONY) AS YEAR_DEV,
    ID
FROM (
    SELECT 
        ID,
        SIZE_OF_COLONY,
        DIFFERENTIATION_DATE,
        MAX(SIZE_OF_COLONY) OVER (PARTITION BY EXTRACT(YEAR FROM DIFFERENTIATION_DATE)) AS MAX_SIZE
    FROM ECOLI_DATA
) AS TEMP
ORDER BY YEAR, YEAR_DEV;