[프로그래머스/PRGMS] 299310 연도별 대장균 크기의 편차 구하기 (서브쿼리, Window 함수)

문제

https://school.programmers.co.kr/learn/courses/30/lessons/299310

프로그래머스

 

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

 

 

풀이에 앞서 ...

Window 함수

: 윈도우 함수란 행과 행 간의 관계를 쉽게 정의하기 위해 만들어진 함수이다.

기존 SQL의 집계함수가 컬(열)럼 간 연산에는 사용하기 좋으나 레코드(행) 간의 연산은 처리하지 못함에 따라 윈도우 함수가 만들어졌다.

 

아래와 같이 크게 5가지로 나눌 수 있다.

• 그룹 내 순위 : RANK, DENSE_RANK, ROW_NUMBER
• 그룹 내 집계 : SUM, MAX, MIN, AVG, COUNT
• 그룹 내 행 순서 : FIRST_VALUE, LAST_VALUE, (LEAD :오라클)
• 그룹 내 비율 : CUME_DIST, PERCENT_RANK, NTILE, RATIO_TO_REPORT
• 통계 분석 : REGR_SLOPE, REGR_INTERCEPT ...

윈도우 함수를 사용하기 위하여 OVER 절과 함께 사용하며, 이 안에 다양한 옵션 제공이 가능하다.

→ PARTITION BY, ORDER BY, ROWS BETWEEN ~ AND ~ , CURRENT ROW .. 등등

 

아래와 같이 사용할 수 있다.

SELECT WINDOW_FUNCTION (ARGUMENTS) 
OVER ([PARTION BY 조건] [ORDER BY] [ROWS BETWEEN a AND b ])
FROM TABLE

 

 

 

문제 풀이 1 - 서브쿼리 사용

1. 연도별 가장 큰 대장균의 크기

select YEAR(differentiation_date) as year, max(size_of_colony) as size
from ecoli_data
group by YEAR(differentiation_date)

year() 를 통해 연도로 casting 해 준 값을 기준으로 그룹화(group by)한 후 가장 큰 대장균을 찾는다.

 

2. 편차 구하기 

1을 서브쿼리로 활용하여 조건에 만족하는 값들을 뽑아온다.

 

코드

select YEAR(differentiation_date) as year, abs(size_of_colony - size) as year_dev, id 
from ecoli_data ecoli join 
(select YEAR(differentiation_date) as year, max(size_of_colony) as size
from ecoli_data
group by YEAR(differentiation_date)
) dev
on YEAR(ecoli.differentiation_date) = dev.year
order by year, year_dev

 

서브쿼리 말고는 풀 수 있는 방법이 없을까?

위에서의 윈도우 함수를 활용해보자!

 

문제 풀이 2 - Window 함수 사용

서브쿼리로 넣었던 부분을 window 함수를 사용하여 수정한다.

코드

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, year_dev

 

훨씬 간결해졌다~