윈도우 함수는 테이블에서 row 집합을 대상으로 계산하는 함수이다. 보통 min, max, count 함수들도 그 대상에 포함된다. 더 많은 함수가 있으니 클릭해서 참고하자. 보통 이런 함수들은 group by 절과 함께 사용해서 특정 값을 기준으로 그룹핑되어 요약된 행으로 산출된다. 하지만 윈도우 함수는 특정 행을 기준잡아서, 산출을 해주되 행수는 줄이지 않고 다 보여줄 수 있다. 예시를 보는게 더 이해가 빠를 듯 하다.
윈도우 함수
generate_series 함수를 이용해서 1~5까지 나오는 테이블셋을 이용해서 윈도우함수의 기능을 하나씩 실행해볼 예정이다. generate_series 관련 내용은 여기를 클릭해서 참고하자.
select aa
from generate_series(1, 5) A(aa);
aa
--
1
2
3
4
5
sum 함수를 group by절 없이, sum(컬럼명) over () 이런식으로 사용해보자. 아래와 같은 결과가 나온다. 모든 행마다, aa 열의 sum 값이 다 나오게 된다. 이건 over 절에 프레임 시작과 끝을 정의하지 않았기 때문에 그냥 테이블의 처음(1)과 끝(5)을 프레임으로 인식하게 된 것이다.
select aa
, sum(aa) over () col_sum
from generate_series(1, 5) A(aa);
aa|col_sum
--+-------
1| 15
2| 15
3| 15
4| 15
5| 15
프레임(Frame)의 범위는 아래의 인자들을 가지고 정의할 수 있다. offset에는 숫자를 넣으면 되고, 양수만 가능하다. 하나씩 예시를 들면서 알아보자.
1. UNBOUNDED PRECEDING
2. offset PRECEDING
3. CURRENT ROW
4. offset FOLLOWING
5. UNBOUNDED FOLLOWING
UNBOUNDED PRECEDING
UNBOUNDED PRECEDING은 파티션의 가장 첫번째 행을 의미한다. 파티션을 지정(partition by)을 하지 않았다면, 그냥 테이블 첫번째 행이라 생각하면 된다. 4번째 행의 sum 값은, 첫행(UNBOUNDED PRECEDING)부터 현재행(CURRENT ROW)인 4번째 행까지의 sum값이 나오게 되는 것이다.
select aa
, sum(aa) over (rows between
UNBOUNDED PRECEDING and CURRENT ROW) col_sum
from generate_series(1, 5) A(aa);
aa|col_sum
--+-------
1| 1 -- UNBOUNDED PRECEDING
2| 3
3| 6
4| 10 -- CURRENT ROW (10 = 1 + 2 + 3 + 4)
5| 15
offset PRECEDING
예를들어 2 PRECEDING 현재 행(CURRENT ROW)으로부터 2행 위의 값까지의 범위를 의미한다. 아래 sql 은 1 PRECEDING 이기 때문에 현재 행으로 부터 위로 1개행까지의 sum 값을 출력한다.
select aa
, sum(aa) over (rows between
1 PRECEDING and CURRENT ROW) col_sum
from generate_series(1, 5) A(aa);
aa|col_sum
--+-------
1| 1 -- 3 PRECEDING
2| 3 -- 2 PRECEDING
3| 5 -- 1 PRECEDING
4| 7 -- CURRENT ROW (7 = 3 + 4)
5| 9
offset FOLLOWING
예를들어 2 FOLLOWING 현재 행(CURRENT ROW)으로부터 아래로 2개행 값까지의 범위를 의미한다. 아래 sql 은 1 FOLLOWING 이기 때문에 현재 행으로 부터 아래로 1개행까지의 sum 값을 출력한다.
select aa
, sum(aa) over (rows between
CURRENT ROW and 1 FOLLOWING) col_sum
from generate_series(1, 5) A(aa);
aa|col_sum
--+-------
1| 3
2| 5 -- CURRENT ROW (5 = 2 + 3)
3| 7 -- 1 FOLLOWING
4| 9 -- 2 FOLLOWING
5| 5 -- 3 FOLLOWING
UNBOUNDED FOLLOWING
UNBOUNDED FOLLOWING은 파티션의 가장 마지막 행을 의미한다. 파티션을 지정(partition by)을 하지 않았다면, 그냥 테이블 마지막 행이라 생각하면 된다. 4번째 행의 sum 값은, 첫행(UNBOUNDED PRECEDING)부터 현재행(CURRENT ROW)인 4번째 행까지의 sum값이 나오게 되는 것이다.
select aa
, sum(aa) over (rows between
CURRENT row and UNBOUNDED FOLLOWING) col_sum
from generate_series(1, 5) A(aa);
aa|col_sum
--+-------
1| 15
2| 14 -- CURRENT ROW (5 = 2 + 3)
3| 12
4| 9
5| 5 -- UNBOUNDED FOLLOWING