[PostgreSQL] window function

window function은 aggregate function처럼 어떤 계산을 도와줍니다. 하지만 aggregate function의 결과가 하나의 row로 보여진다면, 이 window function은 row마다 결과를 보여줍니다. 결과적으로 동작이 조금 달라서 이름을 다르게 지었다고 보면 됩니다. 실제로 PostgreSQL에서 Aggregate function을 만들고, 그녀석을 window function처럼 사용할 수 있습니다.

예제

avg()

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

위의 sql 을 해석하면 아래와 같습니다.

• empsalary table 에서 salary에 대한 avg() 를 구함. 그런데 avg() 는 "depname 칼럼의 값"을 구분(partition)해서 구함.
• 주의할 점은 OVER가 들어가야 window function으로 인식. 그렇지 않으면 그냥 aggregation이 됨.

만약 group by 를 사용한다면, 아래처럼 바꿀 수 있을 것입니다.

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

결과

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)

rank()

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

empsalary table에서 depname의 값별로 salary에 대한 rank 를 구합니다.

ORDER BY

 이 window function에서 OVER부분에 들어가는 ORDER BY는 역할이 좀 다릅니다. 이 partition 안에서 row들의 집합을 window frame이라고 하는데, 많은 window function이 partition전체에서 동작하지 않고 이 window frame의 row 들에서 동작합니다.

ORDER BY가 없으면 기본적으로 window frame은 partition의 모든 row가 됩니다. 그런데 ORDER BY가 OVER에 들어가면 ORDER BY한 column 각각의 row가 window frame이 됩니다. 이 때 중복된 값은 같은 frame이 됩니다.

예제

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)

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)

2개의 column 값 더해서 새로운 값

이번에는 2개의 rank 를 구하고, 이것을 더해 보겠습니다.

SELECT t.code, rank+rank_2 AS sum_rank from ( SELECT code, market, rank() OVER (PARTITION BY market ORDER BY price DESC) AS rank, rank() OVER (ORDER BY price DESC) AS rank_2 FROM test_market ) AS t ORDER BY sum_rank DESC;

test_market이라는 table에서 2개의 rank를 구하는데, 1개는 market값 당 price로 내림차순 정렬하여 rank를 구하고, 다른 한 개(rank_2)는 그냥 price 당 내림차순 정렬해서 구합니다. 이렇게 나온 순위(rank)를 더해서 sum_rank 를 구하고 내림차순을 한 쿼리입니다.

partition by

partition by는 위의 예제에서 확인한 것처럼 PARTITION BY로 정의하는 목록이 같은 그룹인지, 다른 그룹인지 구분하는 기준으로 사용되고, 같은 그룹 내에서는 현재 로우 값도 포함해서 계산된 윈도우 함수의 결과 값을 공유합니다.

응용

여러 개의 윈도우 함수를 함께 쓸 때는 각각 OVER 절을 사용해야하는데, 이 때 지정할 원도우 프래임이 복잡하다면, 중복 입력해야하는 이슈가 발생해 오류가 발생할 가능성이 커집니다. 이 문제점을 줄이기 위해서 WINDOW절을 이용합니다

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

함수 종류

아래는 윈도우 함수의 리스트입니다.

1. row_number
2. rank
3. dense_rank
4. percent_rank
5. cume_dist
6. ntile
7. lag
8. lead
9. first_value
10. last_value
11. nth_value

더 자세한 내용을 아래 링크에서 확인할 수 있습니다.

공식문서: https://www.postgresql.org/docs/9.5/static/tutorial-window.html

번역문서: http://postgresql.kr/docs/9.5/tutorial-window.html