목차
- Window Function 개요
- 집계 함수
- Window Function 구문
1. Window Function 개요
행과 행간의 관계를 쉽게 정의하기 위해 만든 함수를 뜻함. 집계함수를 사용한다는 점에서 groupby 집계와 비슷하지만 윈도우 함수와 큰 차이점이 존재한다. 바로 출력되는 데이터 형태의 '집약' 여부다. 직관적인 차이는 아래 예시와 같다.
-- GROUP BY절을 이용한 집계
SELECT
category
,COUNT(name) as category_cnt
FROM tutorial.animal_crossing_construction
GROUP BY category

-- Window 함수를 이용한 집계
SELECT
category
,COUNT(name) OVER (PARTITION BY category) as category_cnt
FROM tutorial.animal_crossing_construction

위의 예시는 Mode SQL 사이트에서 animal_crossing_construction 이라는 튜토리얼 테이블로 실행한 결과다. 두 쿼리 결과를 비교해 보았을 때,
- groupby 절은 집계하고자 하는 속성의 고유 개수만큼 결과가 집약되어 출력
- window 함수는 집계하고자 하는 속성의 고유 개수에 관계 없이 모든 행에 집계 결과를 출력
와 같은 차이점을 보이고 있다.
2. 집계 함수
2.1) 순위 함수
- RANK: 특정 컬럼의 순위 도출하는 함수. 동일한 값에 대해서는 같은 순위를 부여하며 중간 순위는 비움.
-- RANK 함수를 이용한 집계
SELECT
name
,category
,buy
,RANK() OVER (PARTITION BY category ORDER BY buy DESC) as buy_rank
FROM tutorial.animal_crossing_construction

- DENSE_RANK: 동일한 값에 대해서 같은 순위를 부여. RANK함수와 달리 중간 순위를 비우지 않음
-- DENSE_RANK 함수를 이용한 집계
SELECT
name
,category
,buy
,DENSE_RANK() OVER (PARTITION BY category ORDER BY buy DESC) as buy_rank
FROM tutorial.animal_crossing_construction

- ROW_NUMBER: RANK, DENSE_RANK 함수와 달리 동일한 값에도 고유 순위를 부여함.
-- ROW_NUMBER 함수를 이용한 집계
SELECT
name
,category
,buy
,ROW_NUMBER() OVER (PARTITION BY category ORDER BY buy DESC) as buy_rank
FROM tutorial.animal_crossing_construction

2.2) 일반 집계 함수
- SUM: 합계
- MAX: 최대값
- MIN: 최소값
- AVG: 평균값
- COUNT: 개수
-- MAX 함수를 이용한 집계
SELECT
name
,category
,buy
,MAX(buy) OVER (PARTITION BY category ORDER BY buy DESC) as max_buy
FROM tutorial.animal_crossing_construction

일반 집계 함수는 순위 함수와 달리 () 안에 집계하고자 하는 대상의 컬럼명을 기입해야 함.
2.3) 그룹 내 행 순서 함수
- FIRST_VALUE: over 절 내 partition 별 윈도우에서 가장 먼저 나온 값 도출
-- FIRST_VALUE 함수를 이용한 집계
SELECT
name
,category
,buy
,FIRST_VALUE(buy) OVER (PARTITION BY category) as first_buy
FROM tutorial.animal_crossing_construction

- LAST_VALUE: over 절 내 partition 별 윈도우에서 가장 나중에 나온 값 도출
-- LAST_VALUE 함수를 이용한 집계
SELECT
name
,category
,buy
,LAST_VALUE(buy) OVER (PARTITION BY category) as last_buy
FROM tutorial.animal_crossing_construction

- LAG: 이전 n번째 행의 값 가져옴. 최대 3개의 인자를 가지며
- 첫 번째 인자: 집계할 컬럼명
- 두 번째 인자: 몇 번째 앞의 행을 가져올지 결정 (default 값은 1)
- 세 번째 인자: 가져올 행이 없을 경우 채우고자 하는 값
-- LAG 함수를 이용한 집계
SELECT
name
,category
,buy
,LAG(buy, 2) OVER (PARTITION BY category) as lag_buy
FROM tutorial.animal_crossing_construction

- LEAD: 이후 n번째 행의 값 가져옴. 인자 개수는 LAG 함수와 동일
-- LEAD 함수를 이용한 집계
SELECT
name
,category
,buy
,LEAD(buy, 2) OVER (PARTITION BY category) as lead_buy
FROM tutorial.animal_crossing_construction

2.4) 그룹 내 비율 함수
- PERCENT_RANK: partition 별로 가장 먼저 나오는 값을 0, 마지막에 나오는 값을 1로 해서 순서별 백분율 산출
-- PERCENT_RANK 함수를 이용한 집계
SELECT
name
,category
,buy
,PERCENT_RANK() OVER (PARTITION BY category ORDER BY buy) as buy_percent_rank
FROM tutorial.animal_crossing_construction

- CUME_DIST: partition 별 누적백분율 산출
-- CUME_DIST 함수를 이용한 집계
SELECT
name
,category
,buy
,CUME_DIST() OVER (PARTITION BY category ORDER BY buy) as buy_percent_rank
FROM tutorial.animal_crossing_construction

- NTILE: partition 별 집계 결과를 n등분한 결과 산출. 이 때, 해당 함수의 인자로 몇 등분할 것인지 숫자를 지정해야 함.
-- NTILE 함수를 이용한 집계
SELECT
name
,category
,buy
,NTILE(4) OVER (PARTITION BY category ORDER BY buy) as buy_percent_rank
FROM tutorial.animal_crossing_construction

3. Window Function 구문

상단의 이미지는 Window 함수 문법구조를 나타내고 있다. Google Cloud 내 Bigquery 가이드 문서에서 참고한 내용이다.
Window 함수의 대략적인 구조는 다음과 같다.
- function_name: 윈도우 절을 동작시키는 집계함수를 뜻함. 앞서 소개한 집계함수들이 모두 이에 해당됨. 여기서 argument_list는 집계함수의 특성에 따라 들어갈 수도 있고 안들어갈 수도 있음.
- OVER: 윈도우 함수를 사용함을 나타내는 키워드로 반드시 기입해야 함.
- over_clause: function_name (집계함수)을 어떻게 주어진 행에 적용시킬지 나타내는 구문. () 안에 조건을 기입함.
- PARTITION BY: 윈도우 함수를 어떤 컬럼 카테고리를 기준으로 나눌지 설정하는 구문. group by 와 비슷한 역할을 한다고 생각하면 됨.
- ORDER BY: 윈도우 함수를 어떤 컬럼을 기준으로 정렬할지 설정하는 구문. 오름차순이면 ASC 또는 빈 구문, 내림차순이면 DESC를 기입
- window_frame_clause: 윈도우 함수에 적용되는 행을 어떤 범위로 구간을 나눌지 적용시키는 구문
- rows_range
- ROWS: 물리적 행 단위. 모든 행을 1개의 행으로 독립적으로 인식
- RANGE: 논리적 행 집합. ORDER BY절에 명시된 컬럼으로 논리적인 행 집합 구성. 집합으로 묶인 그룹이 1개의 행으로 인식됨.
- frame_start | frame_between
- CURRENT ROW: 현재 행
- UNBOUNDED:가장 맨 앞 또는 맨 뒤의 행
- (UNBOUNDED OR N) PRECEDING: 이전 N번째 행
- (UNBOUNDED OR N) FOLLOWING: 다음 N번째 행
- rows_range
rows_range와 frame_start | frame_between 구문의 종류 별 예시를 통해 차이를 알아보자.
1. ROWS와 RANGE의 차이
SELECT
name
,category
,buy
,SUM(buy) OVER (PARTITION BY category ORDER BY buy ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) as window_buy_row_sum
,SUM(buy) OVER (PARTITION BY category ORDER BY buy RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) as window_buy_range_sum
FROM tutorial.animal_crossing_construction

sum() 집계 함수를 사용하여 category 컬럼의 속성 별로 나누어 (partition by) buy 컬럼을 기준으로 오름차순 (order by) 할 때, 첫 번째 행부터 헌재 행 (BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)까지 집계하는 window 함수를 명시한 결과다.
- ROWS: 각 행을 독립적인 행으로 간주. 현재 행으로부터 이전 행의 모든 buy컬럼 값을 합산하여 누적 합을 계산
- RANGE: order by 절에 명시했던 buy컬럼의 값을 기준으로 행을 묶어서 집계. 즉, buy 컬럼의 속성 값이 동일할 경우, 동일한 행으로 간주하고 합계를 계산
2. PRECEDING & FOLLOWING
SELECT
name
,category
,buy
,SUM(buy) OVER (PARTITION BY category ORDER BY buy ROWS BETWEEN 2 PRECEDING AND 2 FOLLOWING) as window_buy_row_sum
FROM tutorial.animal_crossing_construction

category 컬럼의 각 카테고리 속성 내에서 현재 행 기준으로 이전 2개, 이후 2개에 해당되는 buy값을 합계한 결과다.
3. UNBOUNDED
SELECT
name
,category
,buy
,SUM(buy) OVER (PARTITION BY category ORDER BY buy ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) as window_buy_row_sum
FROM tutorial.animal_crossing_construction

category 컬럼의 각 카테고리 속성 내에서 현재 행 기준으로 이전 모든 행, 이후 모든 행에 해당되는 buy값을 합계한 결과다.