윈도우 함수

윈도우 함수 (Window Functions)

"현재 행과 연관된 행들의 묶음에 대해 계산을 하고 싶다" 고민할 때 가장 먼저 떠오르는 게 윈도우 함수예요. 단순 집계와 달리 각 행의 맥락을 살려 계산할 수 있어서, 순위·누적 합·이전/이후 행 참조 같은 작업을 쿼리 한 번에 처리할 수 있어요. 이 페이지에서 PostgreSQL에 내장된 범용 윈도우 함수들을 정리해 볼게요.

출처: 공식문서

윈도우 함수란 무엇인가

윈도우 함수는 현재 쿼리 행과 관련된 행들의 집합에 걸쳐 계산을 수행하는 기능이에요. 이 기능의 입문은 관련 섹션에서, 문법 상세는 또 다른 섹션에서 다루지만, 핵심은 이래요. 이 함수들은 반드시 윈도우 함수 문법, 즉 OVER 절과 함께 호출해야 해요.

파생된 형태를 하나 짚어볼게요. 아래 나열한 내장 윈도우 함수 외에도, 어떤 내장 또는 사용자 정의 일반 집계(ordered-set이나 hypothetical-set이 아닌 집계)도 윈도우 함수로 쓸 수 있어요. 집계 함수는 호출 뒤에 OVER 절이 있을 때만 윈도우 함수로 동작하고, 그렇지 않으면 일반 집계처럼 동작해 전체 집합에 대해 한 행을 반환해요.

범용 윈도우 함수 목록

아래 함수들은 모두 연관 윈도우 정의의 ORDER BY 절이 지정하는 정렬 순서에 의존해요. ORDER BY 컬럼만 봤을 때 구별되지 않는 행들을 "동료(peer)"라고 하는데, 네 개의 순위 함수( cume_dist 포함)는 동료 그룹의 모든 행에 같은 답을 주도록 정의돼 있어요.

함수	설명
`row_number () → bigint`	파티션 안에서 현재 행의 번호를 1부터 세어 반환해요.
`rank () → bigint`	현재 행의 순위를 반환하는데, 공백(gap)이 있어요. 즉 자신의 동료 그룹에서 첫 행의 `row_number` 와 같죠.
`dense_rank () → bigint`	공백 없이 현재 행의 순위를 반환해요. 사실상 동료 그룹의 개수를 세는 셈이에요.
`percent_rank () → double precision`	현재 행의 상대 순위로, (`rank` - 1) / (전체 파티션 행 수 - 1) 이에요. 값은 0에서 1 사이(양끝 포함)로 나와요.
`cume_dist () → double precision`	누적 분포로, (현재 행보다 앞서거나 동료인 파티션 행 수) / (전체 파티션 행 수)예요. 값은 1/`N` 에서 1 사이죠.
`ntile ( num_buckets integer ) → integer`	파티션을 최대한 균등하게 나눠, 1부터 인자 값 사이의 정수를 반환해요.
`lag ( value anycompatible [, offset integer [, default anycompatible ]] ) → anycompatible`	파티션 안에서 현재 행보다 `offset` 행 앞선 행의 `value` 를 반환해요. 그런 행이 없으면 `default` 를 반환하는데, `default` 는 `value` 와 호환되는 타입이어야 해요. `offset` 과 `default` 모두 현재 행 기준으로 평가되고, 생략하면 `offset` 은 1, `default` 는 `NULL` 이 돼요.
`lead ( value anycompatible [, offset integer [, default anycompatible ]] ) → anycompatible`	현재 행보다 `offset` 행 뒤의 행의 `value` 를 반환해요. 없으면 `default` 를 반환하고, 나머지 규칙은 `lag` 와 동일해요.
`first_value ( value anyelement ) → anyelement`	윈도우 프레임의 첫 번째 행에서 평가한 `value` 를 반환해요.
`last_value ( value anyelement ) → anyelement`	윈도우 프레임의 마지막 행에서 평가한 `value` 를 반환해요.
`nth_value ( value anyelement, n integer ) → anyelement`	윈도우 프레임의 `n` 번째 행(1부터 세기)에서 평가한 `value` 를 반환해요. 그런 행이 없으면 `NULL` 을 반환해요.

프레임의 함정과 주의점

first_value, last_value, nth_value 는 "윈도우 프레임" 안의 행만 고려하는데, 기본 프레임은 파티션 시작부터 현재 행의 마지막 동료까지예요. 그래서 last_value 에서는 (가끔 nth_value 에서도) 의외의 결과를 받기 쉬워요. OVER 절에 RANGE·ROWS·GROUPS 같은 프레임 지정을 추가하면 프레임을 재정의할 수 있어요.

집계 함수를 윈도우 함수로 쓰면 현재 행의 윈도우 프레임 안의 행들에 대해 집계를 수행해요. ORDER BY 와 기본 윈도우 프레임 정의를 함께 쓰면 "누적 합" 같은 동작을 만들어 내는데, 이게 원하는 것일 수도 아닐 수도 있죠. 파티션 전체에 대해 집계하려면 ORDER BY 를 생략하거나 ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING 을 쓰면 돼요. 다른 프레임 지정으로 또 다른 효과를 얻을 수 있어요.

참고: NULL 처리 옵션은 미구현

SQL 표준은 lead, lag, first_value, last_value, nth_value 에 RESPECT NULLS 나 IGNORE NULLS 옵션을 정의하는데, PostgreSQL에는 구현돼 있지 않아요. 항상 표준의 기본값인 RESPECT NULLS 처럼 동작해요. 마찬가지로 nth_value 의 FROM FIRST / FROM LAST 옵션도 미구현이라 기본 FROM FIRST 동작만 지원돼요. (FROM LAST 결과가 필요하면 ORDER BY 정렬 순서를 뒤집어서 얻을 수 있어요.)