[SQL] 윈도 함수 Window Function

 

분석할 때 정말 자주 사용 되는 윈도 함수!

그 중 특히 자주 쓰이는 종류들을 (내가 까먹지 않기 위해) 정리해두려 한다.

출처는 대부분 <데이터 분석을 위한 SQL 레시피 - 한빛미디어 www.hanbit.co.kr/store/books/look.php?p_code=B8585882565> 이다.

 

---

 

윈도 함수란?

테이블 내부에 '윈도'라고 부르는 범위를 정의하고 해당 범위 내부에 포함된 값을 특정 레코드에서 자유롭게 사용하려고 도입한 것.

다만 윈도 내부에서 특정 값을 참조하려면 해당 값이 위치를 명확하게 지정해야 한다.

 

*윈도 함수는 over() 구문과 함께 쓰인다.   over() : over 구문에 매개 변수를 지정하지 않으면 전체행에 집약 함수 적용 over(partition by 컬럼 이름) : 해당 컬럼 값을 기반으로 그룹화 하고 집약 함수 적용 over(order by 컬럼 이름) : 윈도 내부에 있는 데이터 순서 정의

 

---

 

ORDER BY 구문으로 테이블 내부의 순서 다루기

-- 점수 순서로 유일한 순위를 붙임
ROW_NUMBER() OVER(ORDER BY SCORE DESC) 
-- 같은 순위를 허용해서 순위를 붙임
RANK() OVER(ORDER BY SCORE DESC)
-- 같은 순위가 있을 때 같은 순위 다음에 있는 순위를 건너 뛰고 순위를 붙임
DENSE_RANK() OVER(ORDER BY SCORE DESC)

-- 현재 행보다 N번 째 앞에 있는 행의 값 추출하기
LAG(product_id) OVER(ORDER BY score DESC)
LAG(product_id,2) OVER(ORDER BY score DESC)

-- 현재 행보다 N번 째 뒤에 있는 행의 값 추출하기
LEAD(product_id) OVER(ORDER BY score DESC)
LEAD(product_id,2) OVER(ORDER BY score DESC)

 

ORDER BY 구문과 집약 함수 조합하기

-- 점수 상위부터 누계 점수 구하기 
SUM(socre)
    OVER(ORDER BY socre DESC ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)

-- 현재 행과 앞 뒤의 행이 가진 값을 기반으로 평균 점수 계산하기
AVG(score)
    OVER(ORDER BY score DESC ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) 

-- 순위가 높은 상품 ID 추출하기
FIRST_VALUE(product_id)
    OVER(ORDER BY score DESC ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)

-- 순위가 낮은 상품 ID 추출하기
LAST_VALUE(product_id)
    OVER(ORDER BY score DESC ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)

 

윈도우 프레임 지정에 대해서 :

프레임 지정이란 현재 레코드 위치를 기반으로 상대적인 윈도를 정의하는 구문

-- 기본형태
ROWS BETWEEN start AND end

start&end
CURRENT ROW(현재의 행)
n PRECEDING(n 행 앞)
n FOLLOWING(n 행 뒤)
UNBOUNDED PRECEDING(이전 행 전부)
UNBOUNDED FOLLOWING(이후 행 전부)

 

PARTITION BY 와 ORDER BY 조합하기

-- 카테고리별로 점수 순서로 정렬하고 유일한 순위를 붙임
ROW_NUMBER() OVER(PARTITION BY categoty ORDER BY socre DESC)
-- 카테고리별로 같은 순위를 허가하고 순위를 붙임 
RANK() OVER(PARTITION BY categoty ORDER BY socre DESC)
-- 카테고리별로 같은 순위가 있을 때 같은 순위 다음에 있는 순위를 건너 뛰고 순위를 붙임
DENSE_RANK() OVER(PARTITION BY categoty ORDER BY socre DESC)

-- 카테고리별 순위 최상위 상품 ID 를 추출
FIRST_VALUE(product_id) 
    OVER(PARTITION BY category ORDER BY score DESC 
    ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)