[컴][DB] Postgres window function

window function 사용법 / rank 구하는 방법 / db 에서 rank 가져오기 / sql 로 rank 구하기 / 랭크 구하기

Window function

PostgreSQL 에서 Window function 을 제공한다. ref. 2 을 보면 이런 Window Function 을 제공하는 DB 를 볼 수 있다. 많이들 사용하는 MySQL 에서는 제공하지 않는다. 하지만 Oracle 에서는 제공한다. 여하튼 이녀석의 사용법을 대략적으로 알아보자.

window function 에 대한 이야기는 ref. 1 에 나와있다. 간략하게 이야기 하면, aggregate function 처럼 어떤 계산을 해준다. 하지만 aggregate function 의 결과가 하나의 row 로 보여진다면, 이 window function 은 row 마다 결과를 보여준다.

결과적으로 동작이 조금 달라서 이름을 다르게 지었다고 보면 된다. 실제로 PostgreSQL 에서 Aggregate function 을 만들고, 그녀석을 window function 처럼 사용할 수 있다.

예제 

avg()

여하튼 ref. 1 에 있는 예제를 한 번 보자.

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 가 없으면 기본적으로 frame (window frame)은 partition 의 모든 row 가 된다. 그런데 ORDER BY 가 OVER 에 들어가면 ORDER BY 한 column 의 각각의 row 가 window frame 이 된다. 이 때 중복된 값은 같은 frame 이 된다.

ref. 1 의 예제를 보면, 이해가 쉽다.

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 로 DESC 정렬해서 rank 를 구하고, 다른 한개(rank_2)는 그냥 price 별로 DESC 정렬해서 구한다.
이렇게 나온 순위(rank) 를 더해서 sum_rank 를 구한다.
그리고 이것을 DESC 정렬한다.

함수 종류

이런 식으로 사용할 수 있는 함수가 몇개 있다. 나열해 보면 아래와 같다.[참고 : PostgreSQL: Documentation: 9.2: Window Functions]

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

nth_value, lead, lag 관한 예제

이런 window function 은 aggregate function 을 만들면 된다. 아래를 참고하자.

• http://i5on9i.blogspot.kr/2016/08/db-postgresql-aggregate-function.html

Reference

1. PostgreSQL: Documentation: 9.5: Window Functions
2. The Difference Between ROW_NUMBER(), RANK(), and DENSE_RANK() | Java, SQL and jOOQ.