🚀[SQL 알고리즘 문제 풀이] Programmers : 연도별 대장균 크기의 편차 구하기(MAX)

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

프로그래머스

 

🔓 문제 설명

분화된 연도(YEAR), 분화된 연도별 대장균 크기의 편차(YEAR_DEV), 대장균 개체의 ID(ID) 를 출력하는 SQL 문을 작성해주세요.

분화된 연도별 대장균 크기의 편차는 분화된 연도별 가장 큰 대장균의 크기 - 각 대장균의 크기로 구하며

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

 

예시 문제 데이터(ECOLI_DATA)

ID	PARENT_ID	SIZE_OF_COLONY	DIFFERENTIATION_DATE	GENOTYPE
1	NULL	10	2019-01-01	5
2	NULL	2	2019-01-01	3
3	1	100	2020-01-01	4
4	2	10	2020-01-01	4
5	2	17	2020-01-01	6
6	4	101	2021-01-01	22

---

⚡SQL Query

내가 작성한 답(MySQL)

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

 

또 다른 MySQL 답

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

 

Oracle 답

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

 

---

🔎 풀이 설명

1. 연도 추출: YEAR() 또는 EXTRACT를 통해 날짜에서 연도 정보를 추출합니다.

2. 연도별 최대값

• PARTITION BY YEAR(): 데이터를 연도별로 나눕니다.
• MAX(SIZE_OF_COLONY): 연도별 안에서 가장 큰 값을 찾습니다.

3. 편차 계산

• 연도별 최대값 - 각 크기(SIZE_OF_COLONY)를 수행하여 YEAR_DEV를 구합니다.

4. 정렬

---

💡인사이트

1.  셀프 조인 방식(내가 작성한 방식) vs 윈도우 함수 방식

FROM ECOLI_DATA A JOIN (
    SELECT YEAR(DIFFERENTIATION_DATE) AS YEAR, MAX(SIZE_OF_COLONY) AS MAX_SIZE
    FROM ECOLI_DATA
    GROUP BY YEAR(DIFFERENTIATION_DATE)
) B
ON YEAR(A.DIFFERENTIATION_DATE) = B.YEAR

MAX(SIZE_OF_COLONY) OVER (PARTITION BY YEAR(...))

비교 항목	윈도우 함수(OVER)	셀프 조인(JOIN + GROUP BY)
코드 가독성	매우 높음. 쿼리가 간결해서 한눈에 들어옴	보통. 서브 쿼리가 포함되어 구조가 복잡함
작동 원리	한 번의 스캔으로 행 유지 + 집계 수행	테이블을 두 번 읽음(원본 + 집계)

 

 

2. Window Sort 부하

• 윈도우 함수는 내부적으로 데이터를 정렬해야 하므로 데이터 양이 많을 경우 부하가 생길 수 있습니다.

 

3. 인덱스 설계

• (DIFFERENTIATION_DATE, SIZE_OF_COLONY) 결합 인덱스가 있다면 성능 향상을 기대해 볼 수 있겠지만, 날짜 함수(YEAR())를 사용하면 인덱스 활용이 제한될 수 있습니다.