윈도우 함수

윈도우 함수 (Window Functions)

윈도우 함수(window function)는 현재 행과 어떻게든 연관된 테이블 행들의 집합에 대해 계산을 수행해요. 집계 함수(aggregate function)로 할 수 있는 계산과 비슷해 보이죠. 하지만 윈도우 함수는 일반(비윈도우) 집계 호출처럼 행들을 하나의 출력 행으로 뭉쳐버리지 않아요. 대신 각 행은 자기만의 정체성(identity)을 그대로 유지해요. 그렇다면 윈도우 함수는 어떻게 행에 접근할까요? — 내부적으로 윈도우 함수는 쿼리 결과의 현재 행뿐 아니라 그 이상에도 접근할 수 있어요.

각 직원의 급여를 자기 부서의 평균 급여와 비교하는 예를 볼게요.

SELECT depname, empno, salary, avg(salary) OVER (PARTITION BY depname) FROM empsalary;

  depname  | empno | salary |          avg
-----------+-------+--------+-----------------------
 develop   |    11 |   5200 | 5020.0000000000000000
 develop   |     7 |   4200 | 5020.0000000000000000
 develop   |     9 |   4500 | 5020.0000000000000000
 develop   |     8 |   6000 | 5020.0000000000000000
 develop   |    10 |   5200 | 5020.0000000000000000
 personnel |     5 |   3500 | 3700.0000000000000000
 personnel |     2 |   3900 | 3700.0000000000000000
 sales     |     3 |   4800 | 4866.6666666666666667
 sales     |     1 |   5000 | 4866.6666666666666667
 sales     |     4 |   4800 | 4866.6666666666666667
(10 rows)

앞의 세 출력 열은 empsalary 테이블에서 그대로 나온 거고, 테이블의 각 행마다 출력 행이 하나씩 있어요. 네 번째 열은 현재 행과 같은 depname 값을 가진 모든 테이블 행에 대해 구한 평균이에요. (사실 이건 비윈도우 avg 집계와 같은 함수인데, OVER 절이 있으면 윈도우 함수로 취급되어 윈도우 프레임(window frame) 범위에서 계산돼요.)

윈도우 함수 호출은 항상 함수 이름과 인자 바로 뒤에 OVER 절이 따라와요. 이게 일반 함수나 비윈도우 집계와 문법적으로 구분되는 지점이에요. OVER 절은 쿼리의 행들이 윈도우 함수에 의해 처리되기 위해 정확히 어떻게 나뉘는지를 결정해요. OVER 안의 PARTITION BY 절은 행들을 그룹, 즉 **파티션(partition)**으로 나눠요. 파티션 안의 행들은 PARTITION BY 식(들)의 값이 같아요. 각 행에 대해 윈도우 함수는 현재 행이 속한 파티션에 들어 있는 행들에 대해 계산돼요.

OVER 안에서 ORDER BY를 쓰면 윈도우 함수가 행들을 처리하는 순서도 제어할 수 있어요. (윈도우의 ORDER BY는 행이 출력되는 순서와 일치할 필요도 없어요.) 예를 들어볼게요.

SELECT depname, empno, salary,
       row_number() OVER (PARTITION BY depname ORDER BY salary DESC)
FROM empsalary;

  depname  | empno | salary | row_number
-----------+-------+--------+------------
 develop   |     8 |   6000 |          1
 develop   |    10 |   5200 |          2
 develop   |    11 |   5200 |          3
 develop   |     9 |   4500 |          4
 develop   |     7 |   4200 |          5
 personnel |     2 |   3900 |          1
 personnel |     5 |   3500 |          2
 sales     |     1 |   5000 |          1
 sales     |     4 |   4800 |          2
 sales     |     3 |   4800 |          3
(10 rows)

여기서 볼 수 있듯 row_number 윈도우 함수는 각 파티션 안의 행들에 ORDER BY 절이 정의한 순서대로 일련 번호를 매겨요 (동률인 행들은 정해지지 않은 순서로 번호가 매겨져요). row_number는 명시적 인자가 필요 없어요. 그 동작이 전적으로 OVER 절에 의해 결정되기 때문이에요.

윈도우 함수가 고려하는 행들은 쿼리의 FROM 절이 만들어내고, 있으면 WHERE, GROUP BY, HAVING 절이 걸러낸 **가상 테이블(virtual table)**의 행들이에요. 예컨대 WHERE 조건을 만족하지 못해 제거된 행은 어떤 윈도우 함수에서도 보이지 않아요. 하나의 쿼리는 서로 다른 OVER 절로 데이터를 다르게 잘라 내는 윈도우 함수를 여러 개 포함할 수 있어요. 하지만 그 모두는 이 가상 테이블이 정의한 동일한 행 집합을 대상으로 동작해요.

행의 정렬 순서가 중요하지 않다면 ORDER BY를 생략할 수 있다고 이미 봤어요. PARTITION BY를 생략하는 것도 가능하고, 그 경우 모든 행을 포함하는 단일 파티션이 하나 생겨요.

윈도우 함수와 함께 꼭 알아둬야 할 중요한 개념이 하나 더 있어요. 각 행마다 자기 파티션 안에 **윈도우 프레임(window frame)**이라고 부르는 행들의 집합이 존재한다는 점이에요. 어떤 윈도우 함수는 파티션 전체가 아니라 윈도우 프레임의 행들에 대해서만 동작해요. 기본적으로 ORDER BY가 주어지면 프레임은 파티션의 시작부터 현재 행까지의 모든 행, 그리고 ORDER BY 절 기준으로 현재 행과 같은 값인 뒤따르는 행들로 구성돼요. ORDER BY를 생략하면 기본 프레임은 파티션의 모든 행이 돼요. [5] sum을 쓰는 예를 볼게요.

SELECT salary, sum(salary) OVER () FROM empsalary;

 salary |  sum
--------+-------
   5200 | 47100
   5000 | 47100
   3500 | 47100
   4800 | 47100
   3900 | 47100
   4200 | 47100
   4500 | 47100
   4800 | 47100
   6000 | 47100
   5200 | 47100
(10 rows)

위에서 OVER 절에 ORDER BY가 없으니 윈도우 프레임이 파티션과 같아지고, PARTITION BY가 없어서 파티션이 곧 테이블 전체가 됐어요. 다시 말해 각 합계는 테이블 전체를 대상으로 구해지기 때문에 모든 출력 행에 같은 결과가 나와요. 그런데 ORDER BY 절을 추가하면 결과가 완전히 달라져요.

SELECT salary, sum(salary) OVER (ORDER BY salary) FROM empsalary;

 salary |  sum
--------+-------
   3500 |  3500
   3900 |  7400
   4200 | 11600
   4500 | 16100
   4800 | 25700
   4800 | 25700
   5000 | 30700
   5200 | 41100
   5200 | 41100
   6000 | 47100
(10 rows)

여기서 합계는 첫 번째(가장 낮은) 급여부터 현재 급여까지 구해져요. 현재 행과 같은 값의 중복(duplicate)도 포함해요. (중복된 급여들의 결과를 눈여겨보세요.)

윈도우 함수는 쿼리의 SELECT 목록과 ORDER BY 절에서만 허용돼요. GROUP BY, HAVING, WHERE 같은 다른 곳에서는 쓸 수 없어요. 그 이유는 이 절들이 처리된 다음에 윈도우 함수가 논리적으로 실행되기 때문이에요. 또한 윈도우 함수는 비윈도우 집계 함수보다 나중에 실행돼요. 그러니까 윈도우 함수의 인자에 집계 함수 호출을 넣는 것은 유효하지만, 그 반대(집계 함수의 인자에 윈도우 함수를 넣는 것)는 안 돼요.

윈도우 계산이 끝난 뒤에 행을 필터링하거나 그룹 지어야 한다면 서브셀렉트(sub-select)를 쓰면 돼요. 예를 들어볼게요.

SELECT depname, empno, salary, enroll_date
FROM
  (SELECT depname, empno, salary, enroll_date,
     row_number() OVER (PARTITION BY depname ORDER BY salary DESC, empno) AS pos
     FROM empsalary
  ) AS ss
WHERE pos < 3;

위 쿼리는 내부 쿼리에서 row_number가 3보다 작은(즉 각 부서의 처음 두 행) 행들만 보여줘요.

쿼리에 윈도우 함수가 여러 개 들어가면 각각에 별도의 OVER 절을 써줄 수 있어요. 하지만 여러 함수가 같은 윈도우 동작을 원한다면 이렇게 쓰는 건 중복돼서 실수하기 쉬워요. 대신 각 윈도우 동작에 WINDOW 절로 이름을 붙이고 OVER에서 그 이름을 참조하면 돼요. 예를 들어볼게요.

SELECT sum(salary) OVER w, avg(salary) OVER w
  FROM empsalary
  WINDOW w AS (PARTITION BY depname ORDER BY salary DESC);

윈도우 함수에 대한 더 자세한 내용은 4.2.8절(윈도우 함수 구문), 9.22절(윈도우 함수 목록), 7.2.5절(윈도우 처리), 그리고 SELECT 참조 문서에서 찾아볼 수 있어요.

[5] 윈도우 프레임을 다른 방식으로 정의하는 옵션도 있지만 이 튜토리얼에서는 다루지 않아요. 자세한 내용은 4.2.8절을 보세요.

더 알아보기 (Learn more)