[HiveSQL] Window Function 윈도우 범위 지정하기

저번에 윈도우 함수 기본 문법과 사용 방법에 대해 알아보았습니다. 오늘은 더 나아가 윈도우 범위를 지정해보도록 하겠습니다.

 

복습해보면 윈도우 함수의 기본 문법은 아래와 같습니다.

분명 window_function 뒤에, 집약할 대상을 괄호에 넣고, over는 필수! partition by, order by 는 옵션! 이라고 외우라고 했는데 뒤에 뭔가가 더 붙은게 보이시죠? 이것을 윈도우 범위를 지정한 것입니다. 즉, 현재 레코드 위치를 기반으로 상대적인 윈도우 범위를 정의한 것입니다. 현재 행을 기준으로 모든 레코드를 볼 것인지, 앞 뒤 몇 개만 볼 것인지, 이후 행만 볼 것인지 등을 지정할 수 있습니다. 

 

윈도우 범위를 지정하는 것이 상황에 따라 아주 유용한데,

특히 이동 평균을 구할 때, 일정 기간 누적합을 구할때, 결측치에 보정을 줄 때 등 앞뒤가 서로 연관되어 있을 때 상황에 유용합니다.

 

※ 윈도우 범위를 지정하지 않으면 order by 가 없는 경우는 모든 행, 있는 경우 첫 행에서 현재 행까지가 디폴트로 지정됩니다.

SELECT
	column1,
	column2,
  	-- 윈도우 함수 적용
 	window_function(column3) OVER (PARTITION BY partition_column ORDER BY order_column ROWS BETWEEN <start> AND <end>) AS result_column
FROM 테이블이름
;

 

 

ROWS BETWEEN <start> AND <end> : 윈도우의 범위를 정의합니다. 즉, 현재 행을 중심으로 몇 행을 포함할 것인지를 결정합니다.

<start>, <end> 로 가능한 키워드

• CURRENT ROW : 현재 행
• n PRECEDING : n 행 앞
• n FOLLOWING : n 행 뒤
• UNBOUNDED PRECEDING : 이전 행 전부
• UNBOUNDED FOLLOWING : 이후 행 전부

-- 점수 높은 순서대로 순위 지정
ROW_NUMBER() OVER(ORDER BY score DESC) AS rank
-- 상위 순위부터 누적 점수 구하기
SUM(score) OVER(ORDER BY AS score DESC ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) as cum_score
-- 현재 행과 앞 뒤의 행의 평균 점수 구하기
AVG(score) OVER(ORDER BY score DESC ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) as local_avg
-- 순위 높은 상품 ID 추출하기
FISRT_VALUE(product_id) OVER(ORDER BY score DESC ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) as first_value
-- 순위 낮은 상품 ID 추출하기
LAST_VALUE(product_id) OVER(ORDER BY score DESC ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) as first_value

 

 

 

 

※ 윈도우 함수 기본 사용법

행과 행의 관계를 쉽게 계산해주는 윈도우 함수 Window Function [SQL]