[PostgreSQL] Window Function(윈도우 함수), 순위함수

윈도우 함수란

- 윈도우 함수는 테이블에서 행집합을 대상으로 `함수입니다.

- 행집합은 한 행에 대해서 어떠한 방식으로 관계된 행들이며, 집합 단위로 계산한다는 점에서 집계 함수와 비슷합니다.

- 하지만 집계 함수는 해당 되는 행집합에 대해서 하나의 로우로 그 결과물을 보여주지만, 윈도우 함수는 각 행마다 처리결과를 출력합니다. 따라서 윈도우 함수는 집계결과뿐만 아니라 각행의 특징도 볼 수 있습니다.

 

윈도우 함수의 구조

- 집약함수로 윈도함수를 사용하려면 집약함수 뒤에 OVER를 붙이고 윈도함수를 지정합니다.

- 만약 OVER구문에 매개변수를 지정하지 않으면 테이블 전체에 집약함수가 적용됩니다.

- 매개변수에 PARTITION BY (컬럼이름) 을 지정하면 해당 컬럼 값을 기반으로 그룹화하고 집약함수가 적용됩니다.

Select Window_Function(인수) 
    OVER(PARTITION BY 컬럼이름 ORDER BY 윈도우프레임)
From 테이블;

ORDER BY

- ORDER BY는 SELECT문에서 가지고 온 데이터를 특정 칼럼을 기준으로 테이블을 정렬하는 함수입니다.

- 기본값은 오름차순(ASC)(생략가능)이며 DESC를 입력하면 내림차순으로 정렬 할 수 있습니다.

순위함수

- ROW_NUMBER() : 유일한 순위를 붙이는 함수
- RANK() : 같은 순위를 허용해서 순위를 붙이는 함수(동순위 밑 순위는 동순위 개수만큼 증가)
- DENSE_RANK() : 같은 순위가 있을 때 같은 순위 다음에 있는 순위를 건너 뛰고 순위를 붙임
- LAG(대상칼럼, 행값) : 현재 행보다 앞에 있는 행의 값 출력
- LEAD(대상칼럼, 행값) : 현재 행보다 뒤에 있는 행의 값 출력
- FIRST_VALUE(대상칼럼) : 첫번째 레코드 추출
- LAST_VALUE(대상칼럼) : 마지막 레코드 추출

select
    product_id
    ,score
    ,ROW_NUMBER() over(order by score DESC) as row_num
    ,RANK() over(order by score DESC) as ranks
    ,DENSE_RANK() over(order by score DESC) as dense_ranks

    ,LAG(product_id) over(order by score DESC) as lag1
    ,LAG(product_id, 2) over(order by score DESC) as lag2 

    ,LEAD(product_id) over(order by score DESC) as lead1
    ,LEAD(product_id, 2) over(order by score DESC) as lead2

from review
order by row_num;

 

윈도프레임 지정별 상품 ID 집약

- array_agg 함수 활용

select product_id
, row_number() over(order by score DESC) as row
, array_agg(product_id)
over (order by score desc rows between unbounded preceding and unbounded following) as whole_agg
, array_agg(product_id)
over (order by score desc rows between unbounded preceding and current row) as cum_agg
, array_agg(product_id)
over (order by score desc rows between 1 preceding and 1 following) as local_agg
from popular_products
where category='action'
order by row
;

PARTITION BY와 ORDER BY조합

select
	category
	,score
	,ROW_NUMBER() over(PARTITION BY category order by score DESC) as row_num
	,RANK() over(PARTITION BY category order by score DESC) as ranks
	,DENSE_RANK() over(PARTITION BY category order by score DESC) as dense_ranks

from popular_products
order by row_num;

윈도우 프레임

- ORDER BY구문과 집약함수를 조합할 수가 있는데 이때 사용되는 것이 윈도우프레임 지정입니다.

- 아래가 기본적인 프레임 구문이며 start와 end 사이를 바꾸어가며 프레임지정을 다르게 할 수 있습니다.

OVER(ORDER BY 칼럼 ROWS BETWEEN start AND end)

- CURRENT ROW: 현재의 행
- n PRECEDING : n행 앞
- n FOLLOWING : n행 뒤
- UNBOUNDED PRECEDING : 이전행 전부
- UNBOUNDED FOLLOWING : 이후 행 전부

select 
    product_id
    ,ROW_NUMBER() over(order by score DESC) as row_num
    -- 누계점수 계산
    ,sum(score) over(order by score DESC
                    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
                    as sum_score
    -- 앞뒤로 평균계싼
    ,avg(score) over(order by score DESC
                    ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING)
                    as avg_score
    -- 순위가 가장 높은 id 추출
    ,FIRST_VALUE(product_id) over(order by score DESC
                    ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)
                    as first
    -- 순위가 가장 낮은 id 추출
    ,LAST_VALUE(product_id) over(order by score DESC
                    ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)
                    as last
from review
order by row_num;

카테고리 별 최상위 상품 추출

select distinct category
	,first_value(product_id) 
		over(PARTITION BY category order by score DESC
			 ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) as product_id
from popular_products;