Menu

PostgreSQL 윈도우 함수: OVER, PARTITION BY, ROW_NUMBER

윈도우 함수는 관련된 행들의 그룹에서 각 행의 값을 계산하되 행을 합치지 않습니다. 순위, 누적 합계, 이동 평균, 이전 행과의 비교에 씁니다.

이 페이지에는 실행 가능한 에디터가 있습니다. 편집하고 실행하면 결과를 바로 볼 수 있습니다.

윈도우 함수는 관련된 행들의 집합, 즉 윈도우에서 각 행의 값을 계산하며, GROUP BY처럼 행을 합치지 않습니다. function() OVER (PARTITION BY ... ORDER BY ...) 형태로 씁니다. PARTITION BY는 행을 그룹으로 나누고, ORDER BY는 각 그룹 안의 순서를 정합니다.

모든 행이 결과에 남고, 각 행에 지역 합계가 붙습니다. north는 1000, south는 500입니다. 그러면 담당자별 비중은 식 하나로 구할 수 있습니다(ana는 north의 50퍼센트). sum, count, avg, min, max 같은 집계 함수는 OVER를 붙이면 모두 윈도우 함수가 됩니다. 비어 있는 OVER ()는 결과 전체를 하나의 윈도우로 만듭니다.

ROW_NUMBER, RANK, DENSE_RANK로 행에 번호 매기기

세 순위 함수는 ORDER BY 값이 같은 행이 있을 때만 다르게 동작합니다:

ben과 cy는 둘 다 85점입니다. row_number는 그래도 2와 3을 줍니다. ORDER BY로 동점을 가르지 않으면 누가 2가 될지는 정해져 있지 않습니다. rank는 둘 다 2를 주고 dee는 4로 건너뜁니다. dense_rank는 둘 다 2를 주고 3으로 이어 갑니다.

함수동점 처리동점 뒤 번호 건너뜀
row_number()서로 다른 번호없음
rank()같은 번호있음
dense_rank()같은 번호없음

그룹별 상위 N개 행 구하기

윈도우 함수는 WHERE 다음에 계산되므로 바로 거를 수 없습니다. 서브쿼리나 CTE에서 번호를 매기고 바깥에서 거르세요:

지역마다 실적 상위 두 명을 돌려줍니다. 2위 동점인 행을 모두 남기려면 row_number() 대신 rank()를 쓰세요. 그룹마다 첫 행만 필요하다면 DISTINCT ON이 더 짧습니다.

누적 합계와 이동 평균 구하기

OVER 안에 ORDER BY가 있으면 집계 함수가 프레임 위에서 동작합니다. 기본 프레임은 파티션의 첫 행부터 현재 행까지이므로 누적 합계가 됩니다. ROWS BETWEEN 절로 현재 행과 그 앞의 두 행처럼 다른 프레임을 지정할 수 있습니다:

누적 합계는 5일째에 130이 되고, 5일째의 3일 평균은 (30 + 20 + 50) / 3 = 33.3입니다.

기본 프레임은 RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW입니다. ORDER BY 값이 현재 행과 같은 행을 모두 포함하므로, day가 같은 두 행은 같은 누적 합계를 받습니다. 한 행씩 더하려면 ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW라고 쓰세요.

이전 행과 비교하기: LAG와 LEAD

lag(col)은 윈도우 순서에서 현재 행 바로 앞 행의 값을, lead(col)은 바로 뒤 행의 값을 읽습니다. 세 번째 인자로 기본값을 넘기지 않으면 범위를 벗어났을 때 둘 다 NULL을 돌려줍니다.

1일째에는 이전 행이 없어서 previous와 change가 NULL입니다. WINDOW 절은 윈도우를 이름으로 한 번 정의해 여러 함수가 함께 쓰게 합니다.

first_value는 기본 프레임으로 잘 동작하지만 last_value는 그렇지 않습니다. 기본 프레임이 현재 행에서 끝나므로 현재 행의 값을 돌려줍니다. ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING을 붙여 프레임을 파티션 전체로 만드세요. 그 밖의 윈도우 전용 함수로는 ntile(n), percent_rank(), cume_dist(), nth_value()가 있습니다. 전체 목록은 PostgreSQL 윈도우 함수 공식 문서에 있습니다.

집계 함수가 셀 행 거르기

FILTER (WHERE ...)는 윈도우 집계 함수에도 쓸 수 있어, 모든 행에 조건부 합계를 붙일 수 있습니다:

SELECT day,
       count(*) FILTER (WHERE status = 'error') OVER (ORDER BY day) AS errors_so_far
FROM requests;

자주 묻는 질문

GROUP BY와 PARTITION BY는 무엇이 다른가요?

GROUP BY는 그룹마다 한 행을 돌려줍니다. OVER 안의 PARTITION BY는 모든 행을 그대로 두고 각 행에 그룹 단위로 계산한 값을 붙이므로, 행의 금액 옆에 그룹 합계를 함께 보여 줄 수 있습니다.

WHERE에서 윈도우 함수를 쓸 수 있나요?

아니요. 윈도우 함수는 WHERE, GROUP BY, HAVING 다음에 실행되므로 SELECT 목록과 ORDER BY에서만 쓸 수 있습니다. 서브쿼리나 CTE에서 값을 계산하고 바깥 쿼리에서 거르세요.

LAST_VALUE가 왜 현재 행의 값을 돌려주나요?

OVER에 ORDER BY가 있으면 기본 프레임이 현재 행(과 동점인 행)에서 끝나므로, 프레임의 마지막 행이 곧 현재 행입니다. ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING을 붙여 프레임을 파티션 전체로 만드세요.

프레임에서 ROWS와 RANGE는 무엇이 다른가요?

ROWS는 물리적인 행을 셉니다. ROWS BETWEEN 2 PRECEDING AND CURRENT ROW는 현재 행과 그 앞의 두 행입니다. RANGE는 ORDER BY 값을 기준으로 동작하므로, 값이 같은 행(피어)은 항상 함께 프레임에 들어갑니다.

Coddy 프로그래밍 언어 일러스트

Coddy로 코딩 배우기

시작하기