SQL 익히기 - 윈도우 함수(Window Function)

윈도우 함수

- 윈도우 함수는 특정 기준에 따라 행마다 계산을 수행하는 함수라고 생각하면 된다.

- 집계함수는 그룹별로 묶어서 하나의 결과를 반환하지만, 윈도우 함수는 행마다 결과를 보여준다.

 

윈도우 함수의 구조

select window_function(arguments)
over(partition by 컬럼 order by windowing 절)
from 테이블

 

	설명
argument	함수의 인수
partition by	집합을 특정 기준에 의해 작은 그룹으로 나눔
order by	특정 항목에 대해 정렬
windowing	범위를 정함

 

windowing 옵션

	설명
ROWS	물리적 행(ROW) 단위로 범위를 지정
RANGE	값 단위로 범위를 지정

 

windowing 범위 지정

	설명
UNBOUNDED PRECEDING	범위의 시작을 데이터의 첫 행부터 지정
UNBOUNDED FOLLOWING	범위의 끝을 데이터의 마지막 행까지 지정
CURRENT ROW	현재 행만 포함
N PRECEDING	현재 행을 기준으로 n행 이전까지 포함
N FOLLOWING	현재 행을 기준으로 n행 이후까지 포함

 

---

집계 함수

	설명
SUM	합계 계산
AVG	평균 계산
COUNT	행 수 계산
MIN / MAX	최솟값 최댓값 계산

 

 

- 처음부터 현재 행까지의 누적 합

select name,age,sal,
sum(sal) over(order by sal rows between unbounded preceding and current row) 누적연봉
from test

 

- 처음부터 마지막까지의 합

select name,age,sal,
sum(sal) over(order by sal rows between unbounded preceding and unbounded following) 연봉_합
from test

 

- 내 연봉과 부서 평균 연봉의 비교

select name,age,department,sal as 연봉,
avg(sal) over(partition by department) sal_avg,
from test

 

- 부서 최소 연봉

select name,age,department,sal as 연봉,
min(sal) over (partition by department) as 부서최소연봉
from test

---

순위 함수(Rank)

 

- 특정 항목과 파티션에 대해서 순위를 계산할 수 있는 함수

	설명
RANK	- 동일한 순위는 동일한 값 부여 (일반적인 순위 집계)
DENSE_RANK	- 동일한 순위를 하나의 순위로 계산
ROW_NUMBER	- 동일한 순위에 고유한 순위 부여

 

- 연봉 전체 순위와 부서 순위 검색

- 1위가 2명이기 때문에 2위가 없다.

select name,age,department,sal as 연봉,
rank() over (order by sal desc) 전체순위,
rank() over (partition by department order by sal desc) 부서순위
from test

 

- dense_rank 사용

- 1위가 2명이지만 하나의 순위로 계산되기 때문에 2위가 있다.

select name,age,department,sal as 연봉,
dense_rank() over (order by sal desc) 전체순위,
dense_rank() over (partition by department order by sal desc) 부서순위
from test

 

- row_number 사용

- 연봉이 같지만 각각의 고유한 순위를 부여하기 때문에 순위가 다르다.

select name,age,department,sal as 연봉,
row_number() over (order by sal desc) 전체순위,
row_number() over (partition by department order by sal desc) 부서순위
from test

 

---

행 순서 함수

	설명
FIRST_VALUE	파티션에서 가장 처음에 나오는 값 MIN 함수와 같다
LAST_VALUE	파티션에서 가장 나중에 나오는 값 MAX 함수와 같다
LAG	이전 행
LEAD	특정 위치의 행 기본값은 1

 

 

- 부서 파티션을 만들고 연봉 오름차순 정렬의 첫번째 값을 가져왔다.

- 부서 최소 연봉이 나온다.

- 이렇게하면 범위에 대한 지정이 없기 때문에 파티션의 처음부터 현재 행 까지로 지정된다.

- 하지만 sal로 오름차순 정렬을 해두었기 때문에 첫번째 행이 무조건 최소값이다.

select name,age,department,sal as 연봉,
first_value(sal) over (partition by department order by sal) as 부서최소연봉
from test

 

 

- 파티션을 처음부터 마지막까지로 지정하고 마지막 값을 가져왔다.

- 오름차순이기 때문에 부서의 최대 연봉을 가져올 수 있다.

select name,age,department,sal as 연봉,
last_value(sal) over (partition by department order by sal rows between unbounded preceding and unbounded following) as 부서최대연봉
from test

 

- lag를 통해 바로 윗놈의 연봉을 가져왔다.

- 나보다 한단계 아래의(?) 연봉을 조회 했다.

 

select name,age,department,sal as 연봉,
lag(sal) over (partition by department order by sal)
from test

 

- lead는 내 행을 기준으로 n번째 행을 가져온다.

- 이번에는 한단계 위의 연봉을 조회했다.

select name,age,department,sal as 연봉,
lead(sal,1) over (partition by department order by sal)
from test

 

---

비율 관련 함수

	설명
CUME_DIST	- 파티션 전체에서 현재 행보다 작거나 같은 행에 대한 누적 백분율을 조회한다. - 0~1의 값을 가진다.
PERCENT_RANK	- 파티션에서 제일 처음 나온 것을 0, 나중에 나온것을 1로 하여 순위 백분율을 조회한다.
NTILE	- 전체 건수를 N등분 하여 결과를 조회한다.
RATIO_TO_REPORT	- 파티션의 SUM(칼럼)에 대한 행 별

 

 

- 연봉의 누적 백분율을 구해보았다.

select name,age,department,sal as 연봉,
cume_dist() over(order by sal)
from test

 

10명중 민수보다 연봉이 같거나 낮은 사람은 본인 포함 1명 = 0.1

10명중 의중이와 연봉이 같거나 낮은 사람은 본인 포함 2명 = 0.2

태연이와 태경이의 연봉인 9천만원보다는 전부 다 낮으니 1이 반환된다.

 

- 연봉의 순위 백분율

select name,age,department,sal as 연봉,
percent_rank() over(order by sal)
from test

 

 

- ntile 을 사용해서 연봉을 4등급으로 구분

select name,age,department,sal as 연봉,
ntile(4) over(order by sal desc)
from test

부러운 1등급..

 

- ratio_to_report를 활용해 전체 연봉의 합에서 내 연봉이 얼마나 차지하는지 확인

select name,age,department,sal as 연봉,
ratio_to_report(sal) over()
from test
order by sal