database - 윈도우 함수에서의 집계함수, 이동평균

ㅡ 집약 함수를 윈도우함수로 사용

 

집약함수를 윈도우함수로 사용하는 방법을 알아보자.

모든 집약함수는 윈도우함수로 사용할 수 있으며, 구문은 윈도우 전용 함수를 사용할 때와 동일하다.

 

-- SUM함수를 윈도우 함수로 사용.

select goods_id, goods_name, sell_price,
SUM (sell_price) OVER (ORDER BY goods_id) AS 누적합계
from Goods;

 

지금까지처럼 집계 대상이 되는 열을 인수로 지정한다, 다만 단순한 합계가 아니다.

일반적으로 '누계'라 부르는 방식의 집계 방법으로,

시계열 순으로 그때그때의 매출 금액의 총액을 산출할 때 자주 사용하는 방법이다.

 

-- AVG 함수를 윈도우함수로 사용

select goods_id, goods_name, sell_price,
AVG (sell_price) OVER (ORDER BY goods_id) AS 누적평균
from Goods;

 

결과를 보면 평균을 구하면서도, 집계 대상은 자신보다 위에 있는 레코드임을 알 수 있다.

이와 같이 자신의 레코드(현재 레코드)를 기준으로 집계 대상을 판단한다는 점이 윈도우 함수로 집약함수를 사용했을 때의 큰 특징이다.

 

ㅡ 이동 평균 산출

 

윈도우함수는 테이블을 윈도우라는 부분을 집합으로 잘라서 그 안에서 순서를 정하는 것이다.

이 윈도우 안에서 한층 더 집계 범위를 나누어서 지정할 수 있다.

이 옵션을 '프레임'이라고 한다.

 

다음과 같이 ORDER BY구 뒤에 범위 지정 키워드를 사용하면 된다.

 

-- 집계 대상 레코드를 직전 3행으로 한다.

select goods_id, goods_name, sell_price,
AVG (sell_price) OVER (ORDER BY goods_id
                              ROWS 2 PRECEDING) AS 이동평균
from Goods;

 

ROWS(행)와 PRECEDING(~ 전)이라는 키워드로 '~행 전까지'라는 프레임을 지정하고 있다.

따라서 ROWS 2 PRECEDING은 2행 전까지라는 프레임 지정이 되며,

집계 대상 레코드는 자신 포함 직전 3행으로 제한되는 것이다.

이와 같은 집계 방법을 이동 평균이라고 한다. 그때그때 '최근 상황'을 파악하고자 할 때 편리하다.

그래서 주식 경향을 시계열로 분석할 때 자주 사용된다.

또한, PRECEDING 대신에 FOLLOWING이라는 키워드를 사용하면 '~행 후까지'라는 프레임 지정도 가능하다.

 

또한, 만약 현재 레코드 전후 행을 집계 대상으로 포함하고 싶은 경우에는

PRECEDING과 FOLLOWING 키워드를 병용하는 것도 가능하다.

 

select goods_id, goods_name, sell_price,
AVG (sell_price) OVER (ORDER BY goods_id
                              ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) AS 이동평균
from Goods;