(SQL > ★ SELF JOIN Lev2) : 20일차 연도별 대장균 크기의 편차 구하기

 

 

☑️ 정답 및 체크

✔ 체크 할 부분

    1) DATE_FORMAT(value, type)을 사용해도 되지만, 연,월,일만 구할 때에는 YEAR(value),    MONTH(value),DAY(value)를  사용해도 됨

    2) LEFT JOIN보다 JOIN을 사용하는게 아래에서는 적절 --> JOIN은 일치하는 데이터만 반환하기 때문

    3) 아래처럼 하나의 테이블을 변형해서 Join하는걸 Self Join이라고 함

    4) JOIN ON에서 ON의 조건은 최대한 디테일하게 가는게 데이터 조회시 성능 좋음

         (e.DIFFERENTIATION_DATE 보다는 YEAR(e.DIFFERENTIATION_DATE)이런식으로 하는게 효율적)

-- 분화된 연도(YEAR), 분화된 연도별 대장균 크기의 편차(YEAR_DEV), 대장균 개체의 ID(ID) 를 출력
-- 분화된 연도별 대장균 크기의 편차(YEAR_DEV) = 분화된 연도별 가장 큰 대장균의 크기 - 각 대장균의 크기
-- 연도별 오름차순 / 대장균 크기의 편차 오름차순 
# 분화된 연도별 대장균 크기의 편차 = 분화된 연도별 가장 큰 대장균의 크기 - 각 대장균의 크기

# 테이블 2개를 다르게 만들어서, 하나는 최대값만 있고 다른 하나는 기존 테이블 // year_dev는 두개의 테이블의 값을 차감하는 형식으로 만들어보기
# 1. 연도가 같은 date의 최대값 구하기 // 최대값 테이블

# 2. 기존 테이블과 join
SELECT YEAR(e.DIFFERENTIATION_DATE) AS YEAR, (m.MAX - e.SIZE_OF_COLONY) AS YEAR_DEV, e.ID
FROM ECOLI_DATA e
JOIN (
    SELECT YEAR(m.DIFFERENTIATION_DATE) AS YEAR, MAX(SIZE_OF_COLONY) AS MAX
FROM ECOLI_DATA m
GROUP BY YEAR(m.DIFFERENTIATION_DATE)
        ) m
ON YEAR(e.DIFFERENTIATION_DATE) = m.YEAR
ORDER BY YEAR ASC, YEAR_DEV ASC