PostgreSQL - Window function

AVG(), MAX(), MIN() 등 집약함수를 쓸때, 하나의 결과를 리턴하지만, Over 구문을 사용해서 윈도함수로 쓸 수 있다. 물론 PostgreSQL에만 지원하는 것은 아니고, 대다수의 DBMS에서도 사용할 수 있다.

아래 예시는 집약함수를 적용한 값과 Over 구문을 사용해서 윈도함수로 적용한 값의 예이다.

CREATE TABLE exam_tab (
    user_id    varchar(255)
  , exam_id varchar(255)
  , score      numeric
);

INSERT INTO exam_tab
VALUES
    ('USER01', 'A001', 4.0)
  , ('USER01', 'A002', 5.0)
  , ('USER01', 'A003', 5.0)
  , ('USER02', 'A001', 3.0)
  , ('USER02', 'A002', 3.0)
  , ('USER02', 'A003', 4.0)
  , ('USER03', 'A001', 5.0)
  , ('USER03', 'A002', 4.0)
  , ('USER03', 'A003', 4.0)
;
SELECT
	user_id
	, exam_id
	, score
	, ROUND(AVG(score) OVER(), 2) as 전체평균
	, ROUND(AVG(score) OVER(PARTITION By user_id), 2) as 사용자별_평균
FROM
exam_tab;

Round 함수를 써서 소숫점 두자리 반올림 처리해서 아래와 같은 결과를 구할 수 있다.

윈도함수란 테이블 내부에 '윈도'라고 부르는 범위를 정의하고, 해당 범위 내부에 포함된 값을 특정 레코드에서 자유롭게 사용하려고 도입한 것이다.^[각주:1]

Over 구문 내부에 Order by 구문을 사용할 수 있는데, 이를 사용하면 윈도 내부에 있는 데이터의 순서를 정의할 수 있다. Over 구문 내에 Order by 와ROW_NUMBER(), DENSE_RANK(), LAG(), LEAD() 등의 함수를 이용해서 아래와 같이 다양한 추출 속성을 얻을 수 있다.

CREATE TABLE popular_movie (
    movie_id varchar(255)
  , category   varchar(255)
  , score      numeric
);

INSERT INTO popular_movie
VALUES
    ('A001', 'action', 94)
  , ('A002', 'action', 81)
  , ('A003', 'action', 78)
  , ('A004', 'action', 64)
  , ('D001', 'drama' , 90)
  , ('D002', 'drama' , 82)
  , ('D003', 'drama' , 78)
  , ('D004', 'drama' , 58)
;

SELECT
	movie_id
	, category
	, score
	, ROW_NUMBER() 	OVER(ORDER BY score DESC) AS 유일_일반순위
	, RANK()	OVER(ORDER BY score DESC) AS RANK
	, DENSE_RANK()	OVER(ORDER BY score DESC) AS DenseRank
	, LAG(movie_id) OVER(ORDER BY score DESC) AS Lag1
	, LAG(movie_id, 2) OVER(ORDER BY score DESC) AS Lag2
	, LEAD(movie_id) OVER(ORDER BY score DESC) AS Lead1
	, LEAD(movie_id, 2) OVER(ORDER BY score DESC) AS Lead2
FROM popular_movie
order by 유일_일반순위 asc;

RANK함수는 같은 순위의 레코드 뒤의 순위 번호를 건너뛴다. DENSE_RANK 함수는 같은 순위의 레코드 뒤의 순위 번호를 건너뛰지 않는다. LAG함수는 현재 행을 기준으로 앞의 행의 값을 추출할 수 있으며, LEAD함수는 현재 행을 기준으로 뒤의 행의 값을 추출하는데 사용된다.

아래 결과는 위 쿼리문의 결과이다.

FIRST_VALUE윈도 함수는 윈도 내부의 가장 첫 번째 레코드를 추출해주는 함수다. LAST_VALUE 함수는 윈도 내부의 가장 마지막 레코드를 추출해주는 함수다.

이 때, 윈도의 프레임을 지정해주는 구문이 ROWS BETWEEN start AND end 이다. start와 end에는 아래와 같은 키워드가 올 수 있으며, 참고로, n PRECEDING는 n행 앞이라는 의미이며, n FOLLOWING는 n행 뒤라는 의미이다. UNBOUNDED PRECEDING는 이전 행 전부를 말하며, UNBOUNDED FOLLOWING는 이후 행 전부를 말한다.

SELECT
	movie_id
	, category
	, score
	, ROW_NUMBER() OVER(ORDER BY score DESC) as ROW
	, ROUND(AVG(score) OVER(ORDER BY score DESC
	ROWS BETWEEN 0 PRECEDING AND 1 FOLLOWING),2) AS 앞뒤행평균
	, FIRST_VALUE(movie_id) OVER(ORDER BY score DESC
	ROWS BETWEEN 1 PRECEDING AND 4 FOLLOWING) AS FIRST_VAL
	, LAST_VALUE(movie_id) OVER(ORDER BY score DESC
	ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS LAST_VAL
FROM
popular_movie
order by ROW;

ARRAY_AGG함수를 이용해서, 프레임 범위내의 값을 배열로 리턴할 수 있다. 

SELECT
	movie_id
	, category
	, score
	, ROW_NUMBER() OVER(ORDER BY score DESC) as ROW
	, ARRAY_AGG(movie_id) OVER(ORDER BY score DESC
	ROWS BETWEEN 0 PRECEDING AND 3 FOLLOWING) as ARRAY_AGG
	, ROUND(AVG(score) OVER(ORDER BY score DESC
	ROWS BETWEEN 0 PRECEDING AND 1 FOLLOWING),2) AS 앞뒤행평균
	, FIRST_VALUE(movie_id) OVER(ORDER BY score DESC
	ROWS BETWEEN 1 PRECEDING AND 4 FOLLOWING) AS FIRST_VAL
	, LAST_VALUE(movie_id) OVER(ORDER BY score DESC
	ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS LAST_VAL
FROM
popular_movie
order by ROW;

partition by 와 order by 구문을 조합해서 분류별 순위를 계산할 수 있다.

SELECT
	movie_id
	, category
	, score
	, ROW_NUMBER() OVER(PARTITION BY category ORDER BY score DESC) as ROW
	
FROM
popular_movie
order by category, ROW;

카테고리별로 순위를 계산해서 각 카테고리 상위 n개를 추출할 수 있다.

select * 
from
(
	select movie_id, category, score
		, row_number() over(partition by category order by score desc) as rank
	from popular_movie
) as inner_query
where rank <= 2
order by category, rank;

1. 데이터 분석을 위한 SQL 레시피 에서 인용 [본문으로]