postgres-window-functions

이 글은 PostgreSQL 18 문서 기준으로 적는다. PostgreSQL 문서의 current 링크는 시간이 지나면 다른 버전을 가리킬 수 있으므로, 여기서는 일부러 /docs/18/ 링크를 기준으로 본다.

PostgreSQL의 Window Function 문서를 다시 읽어보면 첫인상은 조금 밋밋하다. Oracle Window Function 문서를 먼저 보고 오면 특히 그렇다. Oracle 쪽 문서는 aggregate_function, ranking_function, analytic_function, over_clause, partition_by_clause, order_by_clause, frame_clause를 비교적 직접적으로 나눠 설명한다. (Oracle Docs)

반면 PostgreSQL의 functions-window 문서는 꽤 짧다. row_number, rank, dense_rank, percent_rank, cume_dist, ntile, lag, lead, first_value, last_value, nth_value 같은 내장 Window Function 목록을 보여주고, 일반 aggregate function도 OVER 절이 붙으면 Window Function처럼 동작할 수 있다고 설명하는 정도다. (PostgreSQL)

그런데 여기서 중요한 점은 PostgreSQL의 문서가 빈약하다고 해서 기능의 구조가 단순한 것은 아니라는 점이다. 오히려 PostgreSQL에서 눈여겨볼 부분은 WINDOW clause를 통해 명시적인 window definition을 정의할 수 있다는 것이다.

SELECT
    depname,
    empno,
    salary,
    sum(salary) OVER w AS sum_salary,
    avg(salary) OVER w AS avg_salary
FROM empsalary
WINDOW w AS (
    PARTITION BY depname
    ORDER BY salary DESC
);

여기서 w는 하나의 이름 붙은 window definition이다. sum(salary) OVER w, avg(salary) OVER w처럼 여러 Window Function이 같은 window definition을 공유할 수 있다.

PostgreSQL 튜토리얼 문서도 이 점을 짧게 언급한다. 여러 Window Function이 같은 windowing behavior를 원할 때 각각의 OVER 절에 반복해서 쓰는 대신, WINDOW clause에서 이름을 붙이고 OVER에서 참조할 수 있다고 설명한다. (PostgreSQL)

Window Function과 Window Clause는 다르다

처음에는 window function과 WINDOW clause를 같은 것으로 생각하기 쉽다. 하지만 둘은 다르다.

Window Function은 실제 값을 계산하는 함수다.

row_number() OVER (...)
avg(salary) OVER (...)
lag(price) OVER (...)

반면 WINDOW clause는 계산 자체가 아니라, 계산에 사용할 window definition을 이름 붙여 정의하는 절이다.

PostgreSQL SELECT 문법을 보면 WINDOW clause는 HAVING 뒤, ORDER BY 앞에 위치한다.

SELECT ...
FROM ...
WHERE ...
GROUP BY ...
HAVING ...
WINDOW window_name AS ( window_definition )
ORDER BY ...

PostgreSQL 18의 SELECT 문서에서도 WINDOW window_name AS ( window_definition ) 형태가 문법에 포함되어 있다. (PostgreSQL)

즉 WINDOW clause는 새로운 row를 만들어내는 FROM clause의 relation은 아니다. 이미 FROM, WHERE, GROUP BY, HAVING을 거쳐 만들어진 query result의 row들에 대해, Window Function이 어떤 기준으로 주변 row들을 볼 것인지 정의하는 장치에 가깝다.

Window Function은 row를 유지한다

Window Function의 가장 중요한 특징은 row를 줄이지 않는다는 점이다.

일반 aggregate는 여러 row를 하나의 결과 row로 줄인다.

SELECT depname, avg(salary)
FROM empsalary
GROUP BY depname;

이 경우 부서별로 하나의 row만 나온다.

하지만 Window Function은 기존 row를 그대로 유지하면서, 각 row에 대해 관련 row 집합을 보고 값을 계산한다.

SELECT
    depname,
    empno,
    salary,
    avg(salary) OVER (PARTITION BY depname) AS dept_avg
FROM empsalary;

PostgreSQL 튜토리얼은 Window Function을 “현재 query row와 관련된 table row들의 집합을 대상으로 계산하는 함수”라고 설명한다. 일반 aggregate와 비슷한 계산을 하지만, row들이 하나의 output row로 합쳐지지 않고 각자의 identity를 유지한다는 점이 핵심이다. (PostgreSQL)

이 차이가 Window Function을 이해하는 출발점이다.

GROUP BY는 row를 접는다. Window Function은 row를 유지한 채 옆을 본다.

OVER 절이 하는 일

Window Function은 항상 OVER 절과 함께 사용된다. PostgreSQL 문서도 Window Function은 OVER clause가 필요하다고 설명한다. OVER가 붙어야 일반 aggregate와 구분되어 Window Function으로 계산된다. (PostgreSQL)

avg(salary) OVER (PARTITION BY depname)

여기서 OVER 안쪽은 크게 세 가지를 정의한다.

OVER (
    PARTITION BY ...
    ORDER BY ...
    ROWS / RANGE / GROUPS ...
)

첫 번째는 PARTITION BY다. 현재 row가 어떤 그룹 안에서 계산될지 정한다.

두 번째는 ORDER BY다. partition 안에서 row들의 순서를 정한다. row_number, rank, lag, lead 같은 함수는 이 순서에 크게 의존한다.

세 번째는 frame이다. partition 전체를 볼 것인지, 현재 row까지 볼 것인지, 현재 row 주변 몇 개만 볼 것인지 정한다.

예를 들어 다음 쿼리는 부서별 salary 순서 안에서 누적합을 계산한다.

SELECT
    depname,
    empno,
    salary,
    sum(salary) OVER (
        PARTITION BY depname
        ORDER BY salary
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS running_sum
FROM empsalary;

PostgreSQL 문서는 aggregate가 Window Function으로 사용될 때 현재 row의 window frame 안에 있는 row들을 대상으로 aggregate한다고 설명한다. 특히 ORDER BY와 기본 frame을 함께 쓰면 running sum 같은 동작이 나올 수 있다고 설명한다. (PostgreSQL)

명시적 WINDOW 정의

PostgreSQL에서 재미있는 부분은 OVER (...)를 매번 직접 쓰지 않고, WINDOW clause로 이름을 붙일 수 있다는 점이다.

SELECT
    depname,
    empno,
    salary,
    sum(salary) OVER dept_window AS sum_salary,
    avg(salary) OVER dept_window AS avg_salary,
    rank() OVER dept_window AS salary_rank
FROM empsalary
WINDOW dept_window AS (
    PARTITION BY depname
    ORDER BY salary DESC
);

이렇게 하면 같은 window definition을 여러 함수가 공유한다.

이 방식은 단순히 타이핑을 줄이는 편의 기능만은 아니다. SQL을 읽을 때도 의미가 분리된다.

WINDOW dept_window AS (
    PARTITION BY depname
    ORDER BY salary DESC
)

이 부분은 “부서별로 나누고, salary가 높은 순서로 정렬한 window”라는 정의다.

그리고 SELECT list에서는 그 window를 이용해 무엇을 계산할지만 보여준다.

sum(salary) OVER dept_window
avg(salary) OVER dept_window
rank() OVER dept_window

즉 “window를 정의하는 일”과 “그 window 위에서 값을 계산하는 일”이 분리된다.

이 점이 나중에 PostgreSQL 코드 레벨을 볼 때도 중요할 것 같다. SQL 문법상으로는 Window Function, OVER clause, WINDOW clause, window definition, frame clause가 서로 다른 개념으로 나뉘어 있기 때문이다.

WINDOW clause는 재사용 가능하다

PostgreSQL 18 문서에 따르면 WINDOW clause의 일반 형태는 다음과 같다.

WINDOW window_name AS ( window_definition ) [, ...]

그리고 window_name은 OVER clause나 이후의 window definition에서 참조될 수 있다. (PostgreSQL)

즉 이런 식의 정의도 가능하다.

SELECT
    depname,
    empno,
    salary,
    sum(salary) OVER w1 AS running_sum,
    avg(salary) OVER w2 AS moving_avg
FROM empsalary
WINDOW
    base AS (
        PARTITION BY depname
        ORDER BY salary
    ),
    w1 AS (
        base
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ),
    w2 AS (
        base
        ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING
    );

여기서 base는 partition과 order를 정의한다. w1, w2는 base를 바탕으로 frame만 다르게 정의한다.

PostgreSQL 문서에 따르면 기존 window name을 참조하면 partitioning clause와 ordering clause를 복사할 수 있지만, 새 window는 자기 frame clause를 사용한다. 다만 복사되는 window에는 frame clause가 없어야 한다. (PostgreSQL)

이 부분은 꽤 흥미롭다. SQL 표면에서는 단순한 이름 재사용처럼 보이지만, 내부적으로는 window definition을 복사하고, 확장하고, 제약을 검사해야 한다.

문서는 빈약하지만 문법은 생각보다 넓다

PostgreSQL의 Window Function 설명은 Oracle 문서에 비해 친절하다고 느껴지지는 않는다. 특히 처음 공부하는 입장에서는 functions-window, tutorial-window, sql-select 문서를 왔다 갔다 해야 한다.

하지만 SELECT 문법 쪽을 보면 PostgreSQL의 window frame 정의는 꽤 넓다.

{ RANGE | ROWS | GROUPS } frame_start [ frame_exclusion ]

{ RANGE | ROWS | GROUPS } BETWEEN frame_start AND frame_end [ frame_exclusion ]

frame_start, frame_end에는 다음이 올 수 있다.

UNBOUNDED PRECEDING
offset PRECEDING
CURRENT ROW
offset FOLLOWING
UNBOUNDED FOLLOWING

또 frame exclusion도 있다.

EXCLUDE CURRENT ROW
EXCLUDE GROUP
EXCLUDE TIES
EXCLUDE NO OTHERS

PostgreSQL 18의 SELECT 문서는 RANGE, ROWS, GROUPS frame mode와 EXCLUDE 옵션을 문법으로 제시한다. (PostgreSQL)

그러니까 PostgreSQL 문서가 짧다고 해서 문법이 단순한 것은 아니다. 오히려 개념 설명은 짧고, 정확한 세부사항은 SELECT reference에 흩어져 있는 느낌이다.

기본 frame은 함정이 될 수 있다

Window Function에서 특히 조심해야 할 함수가 있다.

first_value()
last_value()
nth_value()

이 함수들은 partition 전체가 아니라 window frame을 기준으로 값을 계산한다.

PostgreSQL 문서도 first_value, last_value, nth_value는 window frame 안의 row만 고려한다고 설명한다. 기본 frame은 partition 시작부터 current row의 마지막 peer까지이므로, last_value나 nth_value에서 기대와 다른 결과가 나올 수 있다고 경고한다. (PostgreSQL)

예를 들어 다음 쿼리는 직관과 다르게 동작할 수 있다.

SELECT
    depname,
    empno,
    salary,
    last_value(salary) OVER (
        PARTITION BY depname
        ORDER BY salary
    ) AS last_salary
FROM empsalary;

많은 사람이 last_value라고 하면 partition의 마지막 값을 기대한다. 하지만 기본 frame이 “현재 row까지”에 가까운 형태라면, last_value는 partition 전체의 마지막 row가 아니라 현재 frame의 마지막 row를 본다.

partition 전체의 마지막 값을 원한다면 frame을 명시하는 편이 안전하다.

SELECT
    depname,
    empno,
    salary,
    last_value(salary) OVER (
        PARTITION BY depname
        ORDER BY salary
        ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
    ) AS last_salary
FROM empsalary;

Window Function을 쓸 때는 PARTITION BY, ORDER BY만 볼 것이 아니라 frame까지 같이 봐야 한다.

PostgreSQL 18에서 구현되지 않은 표준 옵션

PostgreSQL 문서에는 SQL 표준에는 있지만 PostgreSQL에는 구현되지 않은 옵션도 언급되어 있다.

예를 들어 SQL 표준은 lead, lag, first_value, last_value, nth_value에 대해 RESPECT NULLS / IGNORE NULLS 옵션을 정의하지만 PostgreSQL은 이를 구현하지 않았고, 항상 표준 기본값인 RESPECT NULLS처럼 동작한다. 또한 nth_value의 FROM FIRST / FROM LAST 옵션도 구현되어 있지 않고 기본값인 FROM FIRST만 지원한다. (PostgreSQL)

이런 부분은 나중에 PostgreSQL과 Oracle, Snowflake, Trino 등의 Window Function 지원 범위를 비교할 때 따로 정리해볼 만하다.

정리

PostgreSQL Window Function 문서는 처음 보기에는 다소 빈약하다. Oracle 문서처럼 문법 요소를 친절하게 층층이 나눠 설명한다기보다는, 튜토리얼에는 짧은 소개를 두고, 함수 목록은 functions-window에 두고, 실제 자세한 문법은 SELECT reference에 흩어놓은 구조에 가깝다.

하지만 그 안에서 중요한 포인트가 있다.

PostgreSQL은 OVER (...) 안에 window definition을 직접 쓰는 방식뿐 아니라, WINDOW clause를 통해 explicit window를 정의할 수 있다.

WINDOW w AS (
    PARTITION BY ...
    ORDER BY ...
    ROWS BETWEEN ...
)

그리고 여러 Window Function은 이 이름 붙은 window를 공유할 수 있다.

sum(x) OVER w
avg(x) OVER w
rank() OVER w

이 구조를 보면 Window Function은 단순히 함수 목록을 외우는 기능이 아니다. 현재 row를 기준으로 어떤 row 집합을 볼 것인지 정의하고, 그 정의 위에서 값을 계산하는 구조다.

다음에는 PostgreSQL 코드 수준에서 이 개념들이 어떻게 표현되는지 보고 싶다. 예를 들어 parser 단계에서 WINDOW clause가 어떤 parse tree node로 들어가는지, OVER clause와 named window reference가 어떻게 연결되는지, planner/executor에서는 WindowAgg가 어떤 정보를 받아 실행되는지까지 따라가 보면 좋겠다.

일단 이번 글의 결론은 이렇다.

PostgreSQL Window Function 문서는 친절하진 않다. 하지만 WINDOW clause를 통해 window definition을 명시적으로 이름 붙일 수 있다는 점은 꽤 중요하다. 그리고 이 지점이 나중에 코드 레벨로 들어갈 때 좋은 출발점이 된다.