프로그래머스 SQL 고득점 Kit - 연도별 대장균 크기의 편차 구하기

프로그래머스 SQL 고득점 Kit
https://school.programmers.co.kr/learn/challenges?tab=sql_practice_kit

SUMMAXMIN

problem

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

task

1. 분화된 연도(YEAR), 분화된 연도별 대장균 크기의 편차(YEAR_DEV), 대장균 개체의 ID(ID) 를 출력
   -> YEAR_DEV만 구한다면 YEAR, ID는 주어진 값이기 때문에 SELECT만 하면 됨.

2. 분화된 연도별 대장균 크기의 편차는 분화된 연도별 가장 큰 대장균의 크기 - 각 대장균의 크기
   한개 키워드씩 구하려고 노력했다.

• 분화된 연도별 가장 큰 대장균의 크기

SELECT MAX(SIZE_OF_COLONY) 
FROM ECOLI_DATA 
WHERE YEAR(DIFFERENTIATION_DATE) = YEAR(D.DIFFERENTIATION_DATE)

• 각 대장균의 크기
  -> 주어진 값이므로 그대로 사용

3. 결과는 연도에 대해 오름차순으로 정렬하고 같은 연도에 대해서는 대장균 크기의 편차에 대해 오름차순으로 정렬

ORDER BY YEAR,
    	 YEAR_DEV;

code

SELECT 
    YEAR(D.DIFFERENTIATION_DATE) AS YEAR,
    (SELECT MAX(SIZE_OF_COLONY) 
     FROM ECOLI_DATA 
     WHERE YEAR(DIFFERENTIATION_DATE) = YEAR(D.DIFFERENTIATION_DATE)
    ) - D.SIZE_OF_COLONY AS YEAR_DEV,
    D.ID

FROM 
    ECOLI_DATA D
    
ORDER BY YEAR,
    	 YEAR_DEV;

note

서브쿼리(Sub query)에 부족함을 느끼는 듯하다.
GPT 형님이 WITH 구문 활용해서 푸는 법도 알려주셨다.
부족함을 많이 느낀 문제 .......... 아자아자 화이팅

# 수정 전
WHERE DATE_FORMAT(R.CREATED_DATE, '%Y-%m') = '2022-10'

# 수정 후
WHERE DATE_FORMAT(B.CREATED_DATE, '%Y-%m') = '2022-10'

WITH 구문의 활용

WITH YearlyMaxSize AS (
    SELECT
        YEAR(DIFFERENTIATION_DATE) AS YEAR,
        MAX(SIZE_OF_COLONY) AS MAX_SIZE
    FROM 
        ECOLI_DATA
    GROUP BY 
        YEAR(DIFFERENTIATION_DATE)
)

SELECT 
    YEAR(D.DIFFERENTIATION_DATE) AS YEAR,
    Y.MAX_SIZE - D.SIZE_OF_COLONY AS YEAR_DEV,
    D.ID
FROM 
    ECOLI_DATA D
JOIN 
    YearlyMaxSize Y
ON 
    YEAR(D.DIFFERENTIATION_DATE) = Y.YEAR
ORDER BY 
    YEAR,
    YEAR_DEV;