SQL의 Window Function

jinny

Published in

None

6 min readJan 2, 2020

안녕하세요. 휴먼스케이프에서 개발을 하고 있는 jinny입니다.

최근 쿼리를 작성하는 과정에서 평소 잘 쓰지 않던 SQL 구문들을 알게 되었습니다. 이중에서 SQL의 Window function을 소개하려고 합니다.

Window Function 이란

Window function은 특정 row들의 집합을 가지고 이루어지는 함수입니다. 이렇게 보면 기존의 aggregate function과 다를 것이 없다고 느껴지지만, 둘 사이에는 큰 차이점이 있습니다.

Aggregate function은 row들의 집합을 대상으로 계산된 결과를 하나의 single result로 축약하지만, window function은 그렇지 않습니다. 계산 결과에서 기존의 row 개수를 그대로 유지합니다.

학생들의 시험 성적을 저장한 8개의 row를 가진 테이블이 있습니다.

시험 성적 평균을 aggregate function으로 구하면, 결과는 하나의 row이지만, window function을 사용하면 결과는 8개의 row가 됩니다.

위 예제에서 우리는 window function이 사용되는 포맷을 알 수 있습니다. [window function] OVER (…something…) 의 형태로, 반드시 OVER 절을 수반합니다.

예제1 — PARTITION BY

aggregate function에서 group by를 사용할 수 있듯이, window function에서 PARTITION BY를 사용해서 계산의 범위(partition)를 특정할 수 있습니다. 만약 partition by절이 없다면, partition은 테이블 전체 row가 됩니다.

예제2 — ORDER BY

순서가 주어져야 하는 경우 ORDER BY절을 사용할 수 있습니다.

위 예제들에는 SELECT 이후에 FROM 절만 왔지만, WHERE, GRUP BY, HAVING절 등이 이어지면, FROM절의 테이블이 WHERE, GRUP BY, HAVING에 의해 필터링된 결과에 window function이 적용됩니다.

Window frame

PostgreSQL의 문서를 보면, window function을 다음과 같이 소개합니다.

A window function performs a calculation across a set of table rows that are somehow related to the current row.

여기서 문장 뒷부분의 “somehow related to the current row.” 에 주목해볼만 합니다.

window function에서는, 항상 그러한 것은 아니지만, 때로 current row를 기준으로 계산이 이루어집니다. 이것과 연관된 개념이 바로 window frame입니다. window frame은 partition의 first row부터 current row까지의 범위를 말합니다. 윈도우 프레임은 window function에서 ‘window’ 가 의미하는 바이기도 합니다.

ORDER BY가 없을 때와 달리, ORDER BY로 row의 처리순서를 부여하면, 순서에 따라 current row의 개념이 생깁니다. ORDER BY절이 있을 때, 각 partition의 첫번째 row부터 current row까지의 window frame을 기준으로 누적 합이 계산되는 것을 확인할 수 있습니다.

여기서 주목해야 할 또다른 점은, 서로 순서가 같은 row는 동일한 peer로 취급되어, 같은 계산 결과를 낸다는 점입니다. 6,7 번째 row에서 누적 합의 결과를 보면 200, 200으로 서로 같은 것을 확인할 수 있습니다.