PostgreSQL : Windows Function (윈도우함수 정리)

postgres

Windows Function을 사용하면 group by를 사용하지 않고도 SQL의 aggregate 함수를 사용할 수 있다.

-- 원래 데이터 
SELECT country, month, goalamount 
FROM TARGET 
order by month, country 

TARGET 이라는 테이블에 아래와 같이 국가별, 월별 목표금액이 있는 테이블이 있다고 가정을 하고...

TARGET

* 편의상 모든 결과는 20 row 까지만 캡춰를 하였습니다.

 

1. 집계 함수

-- 집계 함수 
SELECT 	country, month, goalamount
		, avg(goalamount) over() as avg_amount
		, avg(goalamount) over(partition by month) as avg_month
FROM TARGET

result1

2. 순위 함수

-- 순위 함수 
select 	country, month, goalamount 
		, row_number() over(order by goalamount desc) as row
		, rank() over(order by goalamount desc) as rank
		, dense_rank() over(order by goalamount desc) as dense_rank
		, lag(month) over(order by goalamount desc) as lag1
		, lag(month, 2) over(order by goalamount desc) as lag2
		, lead(month) over(order by goalamount desc) as lead1
		, lead(month, 2) over(order by goalamount desc) as lead2
from TARGET 
order by row

result2

3. 집계함수 + order by 

-- 집계함수 + order by 
select 	country, month, goalamount 
		, row_number() over(order by goalamount desc) as row
		, avg(goalamount) over(partition by country) as avg_country
		, sum(goalamount) over(order by goalamount desc rows between unbounded preceding and current row)as cum_amount
		, avg(goalamount) over(order by goalamount desc rows between 1 preceding and 1 following) as avg_neibour
		, first_value(month) over(order by goalamount desc rows between unbounded preceding and unbounded following) as first_value
		, last_value(month) over(order by goalamount desc rows between unbounded preceding and unbounded following) as last_value  
		, array_agg(country) over(order by goalamount desc rows between 1 preceding and 1 following) as array_agg 
from TARGET 
order by row

result3

4. Partition By 와 Order by 함께 사용하기

-- partition by 와 order by 함께 사용하기 
select 	country, month, goalamount 
		, row_number() over(partition by country order by goalamount desc) as row
		, rank() over(partition by country order by goalamount desc) as rank
		, dense_rank() over(partition by country order by goalamount desc) as dense_rank
		, avg(goalamount) over(partition by country order by goalamount desc rows between 1 preceding and 1 following) as complex_avg
from TARGET
order by country, row

result4

5. 그룹별 상위 3개 추출하기

-- 상위 n개 추출하기 
select *
from (
	select 	country, month, goalamount 
			, row_number() over(partition by country order by goalamount desc) as rank
	from TARGET 
	) A 
where rank <= 3

result5