Postgresql 윈도우 함수의 범위 지정

PostgreSQL의 윈도우 함수에서 범위를 지정하는 방법은 프레임 절(frame clause)을 통해 이루어집니다. 프레임 절은 윈도우 함수가 작동하는 행의 집합을 정의합니다. 주요 범위 지정 방법은 다음과 같습니다:

## ROWS와 RANGE

윈도우 프레임을 정의하는 두 가지 주요 방식:

1. ROWS: 물리적인 행의 위치를 기준으로 프레임을 정의합니다.
2. RANGE: 현재 행의 값을 기준으로 값의 범위를 지정합니다.

## 프레임 지정 구문

기본 구문:

```sql
OVER (
  ORDER BY column
  [ROWS | RANGE] BETWEEN frame_start AND frame_end
)
```

frame_start와 frame_end에 사용할 수 있는 옵션:

- UNBOUNDED PRECEDING: 파티션의 첫 번째 행
- UNBOUNDED FOLLOWING: 파티션의 마지막 행
- CURRENT ROW: 현재 행
- n PRECEDING: 현재 행에서 n행 이전
- n FOLLOWING: 현재 행에서 n행 이후

## 예시

1. 현재 행과 이전 2개 행:
```sql
ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
```

2. 현재 행부터 파티션 끝까지:
```sql
ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
```

3. 값 기반 범위 (예: 현재 값 ±100):
```sql
RANGE BETWEEN 100 PRECEDING AND 100 FOLLOWING
```

## 기본 프레임

- ORDER BY 없는 경우: 전체 파티션이 기본 프레임
- ORDER BY 있는 경우: RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW가 기본

## 주의사항

1. RANGE는 ORDER BY 절의 데이터 타입과 호환되어야 합니다.
2. 날짜/시간 데이터의 경우 INTERVAL을 사용할 수 있습니다:
```sql
RANGE BETWEEN INTERVAL '3' DAY PRECEDING AND CURRENT ROW
```

3. ROWS는 정확한 행 수를, RANGE는 값의 범위를 지정합니다. 데이터 특성에 따라 적절히 선택해야 합니다.

프레임 절을 통한 범위 지정은 윈도우 함수의 강력한 기능을 최대한 활용할 수 있게 해주며, 복잡한 분석 작업을 효율적으로 수행할 수 있게 합니다[3][11][12].

출처
[1] PostgreSQL Tutorial: RANGE Clause with Window Functions https://www.rockdata.net/tutorial/window-functions-range-clause/
[2] PostgreSQL Window Functions: The Ultimate Guide - Neon https://neon.tech/postgresql/postgresql-window-function
[3] 14.20.3 Window Function Frame Specification https://dev.mysql.com/doc/refman/8.4/en/window-functions-frames.html
[4] Master PostgreSQL Window Functions - Complete Guide https://risingwave.com/blog/master-postgresql-window-functions-complete-guide/
[5] SQL Window Functions: ROWS BETWEEN vs RANGE BETWEEN https://www.linkedin.com/pulse/sql-window-functions-rows-between-vs-range-prashanth-mangipudi-bgfsc
[6] Understanding WINDOW in PostgreSQL (With Examples) - Timescale https://www.timescale.com/learn/understanding-window-in-postgresql-with-examples
[7] Documentation: 17: 3.5. Window Functions - PostgreSQL https://www.postgresql.org/docs/current/tutorial-window.html
[8] Understanding Window Functions - The Art of PostgreSQL https://tapoueh.org/blog/2013/08/understanding-window-functions/
[9] Data Processing With PostgreSQL Window Functions - Timescale https://www.timescale.com/learn/postgresql-window-functions
[10] Documentation: 17: 9.22. Window Functions - PostgreSQL https://www.postgresql.org/docs/current/functions-window.html
[11] postgresql - Limit RANGE window function amount of rows considered https://stackoverflow.com/questions/77039433/limit-range-window-function-amount-of-rows-considered
[12] PostgreSQL window functions crash course https://blog.gkomninos.com/postgresql-window-functions-crash-course
[13] Window Functions in PostgreSQL - Stackademic https://blog.stackademic.com/window-functions-in-postgresql-6bdd660bc065?gi=644d0d57e591
[14] Window Functions - postgresql.kr https://postgresql.kr/docs/9.6/functions-window.html
[15] RANGE Clause in Window Functions: 5 Practical Examples https://learnsql.com/blog/range-clause/