250321 프로그래머스 연도별 대장균 크기의 편차 구하기

[문제]

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

 

[풀이]

1) DIFFERENTIATION_DATE의 연도: YEAR, 연도별 가장 큰 대장균 크기-각 대장균 크기: YEAR_DEV로 별칭하고, ID도 조회

2) 같은 연도에서 큰 값 구하고, 거기서 각 대장균 크기 빼기 -> PARTITION BY 

3) 같은 연도에 대해 대장균 크기 편차 오름차순 정렬

 

** PARTITION BY **

분석함수([컬럼]) OVER(PARTITION BY 컬럼1, 컬럼2, ..) [ORDER BY 절][WINDOWING 절])

분석함수를 사용할 때 PARTITION BY를 사용하여 그룹으로 묶어 연산할 수 있다.

GROUP BY를 사용하지 않고, 조회된 각 행에 그룹으로 집계된 값을 표시할 때 OVER 절과 함께 PARTITION BY절 사용하면 된다.

 

** 집계 함수**

: DB에서 그룹 단위로 데이터를 집계하고 통계적인 값을 계산하기 위해 사용되는 함수

- 여러 개의 행을 하나의 결과로 반환 -> SUM(합), AVG(평균), COUNT(개수), MAX(최댓값), MIN(최솟값) 등

---

[오답 분석하기]

1)  PARTITION BY DIFFERENTIATION_DATE vs PARTITION BY YEAR(DIFFERENTIATION_DATE)

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

** 문제에서 요구한 것은 '연도별'

- 단순히 DIFFERENTIATION_DATE로만 하게 된다면, 연도 뿐만 아니라 월, 일도 다른 그룹으로 그룹화 되어버려서 문제에서 요구한 사항과 다르다.

-> 따라서 YEAR()로 묶어주면서 '연도별'이라는 조건을 충족시켜줘야한다.

 

2) MAX(), 그룹화

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

** 실행 단계도 가지 못하고 오류 발생 

- 왜? MAX는 그룹화를 하지 않으면 사용할 수 없다.

- 즉, MAX()는 집계 함수이므로 그룹을 명확하게 지정하지 않으면 어떻게 최댓값을 구하는지 알 수 없다.

- MAX()는 전체 데이터에서 하나의 값만 반환하고자 하는데, SIZE_OF_COLONY는 개별 값인데 이게 동시에 들어가버려서 오류 발생!

 

[정답]

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

=> 앞에서 오류가 났던 사항 2가지를 반영시켜주면서 성공적으로 실행 완료