오라클 OVER() 개념부터 사용법까지

오라클에서 OVER() 를 사용하면

GROUP BY나 서브쿼리를 사용하지 않고

분석 함수(SUM, MAX, COUNT)과

집계 함수(GROUP BY, ORDER BY)를 사용할 수 있는데

 

집계 함수 사용 시

서브쿼리가 지나치게 길어지는 것을 고려해보면

OVER를 사용해 쿼리 길이를 획기적으로 줄일 수 있다

 

OVER()는

분석_함수() OVER(PARTITION BY 컬럼 / ORDER BY 컬럼 / 세부 분할 기준)

으로 이루어지는데

여기서 분석 함수와 OVER()는 필수지만

OVER 안의 내용들은

필요할 경우에만 작성해주면 된다

(RANK, DENSE_RANK는 ORDER BY 필수)

 

분석 함수에는

COUNT(컬럼) - 갯수

SUM(컬럼) - 합계

AVG(컬럼) - 평균

MAX(컬럼) - 최대값

MIN(컬럼) - 최소값

RANK() OVER(ORDER BY 컬럼...) - 순위

DENSE_RANK() OVER(ORDER BY 컬럼...) - 중복 순위가 있어도 1씩 증가

등이 사용되며

 

전체 명단은 아래 링크를 참조해주면 된다

Oracle Aggregate Functions Overview

PARTITION BY 컬럼은

어느 컬럼을 기준으로 쪼갤지를 의미한다

GROUP BY와 동일한 기능이라고 보면 되며

 

PARTITION BY JOB로 했을 시

JOB를 기준으로 쪼개개 된다

 

ORDER BY 컬럼은

정렬 시 기준을 설정해주며

기본은 오름차순이고 내림차순으로 설정해주려면

ORDER BY 컬럼 DESC를 써주면 된다

 

세부 분할 기준은

windowing_clause 라고 하는데

 

PARTITION BY, ORDER BY로

충분히 분할하지 못했을 경우 사용하며

ORDER BY를 사용한 상태에서만 적용 가능하다

 

세부 분할 기준은

조건에 맞는 ROW를 가지고 정렬하는 

ROWS BETWEEN start_point AND end_point와

조건에 맞는 값을 가지고 정렬하는 

RANGE BETWEEN start_point AND end_point

으로 나누어지는데

 

start_point에는 

UNBOUNDED PRECEDING - 첫줄부터

CURRENT ROW - 현재 줄까지

값 PRECEDING - 값부터

 

end_point에는

UNBOUNDED FOLLOWING - 마지막 줄까지

CURRENT ROW - 현재 줄까지

값 FOLLOWING - 값까지

 

가 들어가게 된다

 

ROWS를 사용한 예제를 보면

BETWEEN 1 PRECENDING AND 2 FOLLOWING

이 보이는데

 

해당하는 줄과 그 다음줄만 가지고

AVG를 수행한다는 말이다

 

가장 하단 컬럼을 보면

2850 + 1600 / 2 를 한 값인

2225가 OVER_COL로 들어오는 것이 보인다

 

다음으로 RANGE를 사용한 예제를 보면

INTERVAL '3' MONTH를 사용하여

전후 3개월로 조건을 걸어준 뒤 

수행한 것이 보이는데

 

가장 하단을 보면

12월 입사 기준으로 전후 3개월은

1981년 9월 ~ 1982년 3월이기 때문에

 

해당 값에 해당하는

1500 + 1250 + 950 / 3이 되어

OVER_COL에 1233.33이 나오게 되는 것이다