[PostgreSQL] window function의 window frame 알아보기

window function에 관해서는 https://brownbears.tistory.com/310 에서 설명이 되어 있습니다.

window function의 사용예제를 보다 보면 BETWEEN 구문이 들어가 있는 것을 확인할 수 있습니다. 이를 window frame 즉, 프레임 지정 구문이라 합니다. 프레임 지정이란 현재 레코드 위치를 기반으로 상대적인 window를 지정하는 구문입니다.

프레임 지정 구문은 {범위} BETWEEN {시작} AND {종료}  또는 {범위} {시작} 형태입니다.

시작, 종료

시작과 종료에는 다음과 같은 키워드를 조합하여 사용할 수 있습니다.

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

등의 키워드가 존재합니다.

예제

현재 행 앞, 뒤의 행이 가진 값을 기반으로 평균 점수 계산

select AVG(score) OVER(ORDER BY score DESC ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING)

순위 상위부터의 누적 점수 계산하기

select AVG(score) OVER(ORDER BY score DESC ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW )

최근 7일 점수 계산하기

select AVG(SUM(score)) OVER(ORDER BY score DESC ROWS BETWEEN 6 PRECEDING AND CURRENT ROW )

월별 누적 매출 집계하기

-- dt의 값은 yyyy-mm-dd 형태로 저장되어 있다고 가정
select SUM(SUM(purchase_amount)) OVER(PARTITION BY substring(dt, 1, 7) ORDER BY dt ROWS UNBOUNDED PRECEDING)

범위

위 예시에서 본 것과 같이 범위엔 ROWS가 RANGE가 있습니다.

ROWS는 위에서 설명한 모든 키워드에서 사용을 할 수 있지만 RANGE는 UNBOUNDED 가 붙은 키워드가 무조건 1개 이상 사용이 돼야 합니다. ROWS와 RANGE는 특정한 상황일 때, 다른 기준으로 집계를 하므로 특징을 잘 파악하여 사용해야 합니다.

만약 ORDER BY를 위해 기준을 삼는 PARTITION BY에 선언된 필드가 unique 속성이 없을 경우, RANGE는 고유하지 않은 값에 대한 결과를 모두 결합하여 보여주는 반면 ROWS는 각각 개별적으로 처리합니다. 설명은 헷갈리지만 아래 예시를 보면 바로 이해가 됩니다.

with temp_table as (
    values
           (1, '김'),
           (1, '김'),
           (1, '김'),
           (1, '이'),
           (1, '이'),
           (1, '박')
)

select column1 AS number,
       column2 AS last_name,
       sum(column1) over (partition by column2 order by column2 range between unbounded preceding and current row ) as _range,
       sum(column1) over (partition by column2 order by column2 rows between unbounded preceding and current row ) as _rows
from temp_table;

number,last_name,_range,_rows
  1,       김,       3,    1
  1,       김,       3,    2
  1,       김,       3,    3
  1,       박,       1,    1
  1,       이,       2,    1
  1,       이,       2,    2

먼저 쿼리를 설명하면 두 번째 컬럼을 기준으로 첫 번째 컬럼인 숫자들을 이전 행들부터 현재 행까지 합하는 쿼리입니다. RANGE 키워드를 쓴 결과를 보면 기준이 중복이 되므로 가장 최종 결과 값으로 동일하게 표시된 것을 알 수 있고 ROWS의 결과는 중복여부 상관없이 기준에 맞게 추출된 것을 알 수 있습니다.