PostgreSQL | Window 함수로 쿼리 성능 개선 하기

개발자티포 2025. 1. 15. 16:46

2025. 1. 15. 16:46

728x90

1. Window 함수

Window 함수는 쿼리에서 OVER() 절과 함께 사용된다. 이 OVER() 절은 함수가 어떻게 적용될지에 대한 범위(윈도우)를 정의한다. 윈도우는 PARTITION BY, ORDER BY 등을 사용하여 특정 그룹이나 순서대로 계산할 수 있게 한다.

<window_function> OVER (PARTITION BY <column> ORDER BY <column>)

ROW_NUMBER(): 각 행에 대해 순차적인 번호를 매긴다.

SELECT id, ROW_NUMBER() OVER (PARTITION BY id ORDER BY created_at) AS row_num
FROM orders;

PARTITION BY 를 써서 특정 컬럼으로 조건을 정하고 rank를 분리할 수도 있다.

RANK(): 순위 번호를 매기고, 동점인 경우 동일 순위를 부여한다.

SELECT id, amount, RANK() OVER (ORDER BY amount DESC) AS rank
FROM sales;

DENSE_RANK(): RANK()와 비슷하지만 동점인 경우에도 순위를 건너뛰지 않는다.

SELECT id, amount, DENSE_RANK() OVER (ORDER BY amount DESC) AS dense_rank
FROM sales;

NTILE(n): 데이터를 n개의 구간으로 나누어 각 행에 구간 번호를 부여 한다. .

SELECT id, amount, NTILE(4) OVER (ORDER BY amount) AS quartile
FROM sales;

SUM(), AVG(), MIN(), MAX() 등 집계 함수: 특정 윈도우 내에서 값을 집계한다.

SELECT id, amount, SUM(amount) OVER (PARTITION BY region ORDER BY date) AS running_total
FROM sales;

판매 데이터에서 지역별로 매출의 누적 합계를 계산하는 예시이다.

SELECT region, amount, 
       SUM(amount) OVER (PARTITION BY region ORDER BY sale_date) AS running_total
FROM sales;

ROWS BETWEEN을 사용하면 윈도우 범위를 더 세밀하게 지정할 수 있다. 예를 들어, 현재 행을 기준으로 이전 3개의 행까지의 데이터를 윈도우로 설정할 수 있다.

SELECT id, amount, 
       SUM(amount) OVER (ORDER BY sale_date ROWS BETWEEN 3 PRECEDING AND CURRENT ROW) AS moving_avg
FROM sales;

ROWS BETWEEN 3 PRECEDING AND CURRENT ROW: 현재 행과 이전 3개의 행을 포함하는 윈도우 범위를 설정한다.

728x90