[MYSQL] 윈도우 함수 총정리 2 (범위지정 ROWS/RANGE)

 

윈도우 함수 총정리 2 에서는 윈도우 프레임 내의 범위 지정을 다룰 예정입니다.

윈도우 함수의 기본 개념, 집계 및 순위 매기기 활용은 윈도우 함수 총정리 1을 참고해주세요.

 

범위지정

윈도우 프레임 내에서의 범위 지정 또한 중요한 부분입니다. 

예를 들어 특정 기간에 대한 금액의 누적합을 구하고 싶은 경우 활용할 수 있겠지요?

 

기본 문법

기본적인 윈도우 함수의 OVER 안에 ROWS/RANGE BETWEEN이 추가됩니다. 

SELECT 집계함수() OVER (PARTITION BY {컬럼명1}, ORDER BY {컬럼명2} ROWS|RANGE BETWEEN {시작} AND {끝}) 
FROM {테이블};

• ROW는 현재행 기준 전후 N개의 물리적인 행 단위의 프레임 지정
• RANGE는 논리적인 값을 범위로 지정, 숫자와 날짜 datatype 에 사용 가능 
• 범위 설정 없이 사용할 경우 RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW 가 기본값

RANGE	역할
UNBOUNDED PRECEDING	윈도우 내 첫번째부터
UNBOUNDED FOLLOWING	윈도우 내 마지막 행까지
CURRENT ROW	현재행
N PRECEDING	현재 행에서 이전 N행 포함
N FOLLOWING	현재 행에서 다음 N행 포함

 

 

실습

여러 예제로 실습해 보겠습니다. DB 는 오라클에서 제공하는 sakila 의 payment 테이블을 사용했습니다. 

 

• 결제날짜 기준 결제금액 누적합

날짜가 같을 경우 누적합도 모두 동일하게 하나의 최종 값으로 표시되는 것을 확인할 수 있습니다. 

select date_format(payment_date, '%Y-%m-%d') as date, amount,
	sum(amount) over (order by date_format(payment_date, '%Y-%m-%d') 
    			range BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) as running_total
from sakila.payment ;

 

• 고객별 현재날짜 포함 직전 6일까지의 결제금액 평균

select customer_id , date(date_format(payment_date, '%Y-%m-%d')) as date, amount,
	avg(amount) over (partition by customer_id 
			order by date(date_format(payment_date, '%Y-%m-%d')) asc 
			range between interval '6' day preceding and current row) as moving_avg
from sakila.payment ;

 

• 고객별 각 날짜에 대한 마지막 값 출력

select customer_id , date(date_format(payment_date, '%Y-%m-%d')) as date, amount,
	last_value(amount) over (partition by customer_id 
			order by date(date_format(payment_date, '%Y-%m-%d'))  
			range between unbounded preceding and unbounded following) as lastvalue
from sakila.payment ;

 

 

이상 윈도우 프레임 내의 범위 지정에 대해 알아봤습니다. 

 

 

 

 

오랑우탄이 영어를 하고 오랑이가 쿼리마스터가 되는 그날까지~