윈도우 함수 정리

select 윈도우함수명(인수) over([partition by 컬럼] [order by 컬럼] [windowing절])
from 테이블명;

집계 윈도우 함수

over

윈도우 함수는 결과를 생성하기 위해 입력으로 고려해야 하는 행의 일부를 over절에서 정의한다

select 고객번호, 고객회사명, 마일리지, avg(마일리지) over()
from 고객
where 도시 = '서울특별시';

partition by

전체 집합을 특정 기중에 의해 소그룹으로 나누기 위해 over절 안에 partition by 사용

경기도 고객의 각 도시의 평균 마일리지

select 고객번호, 도시, 마일리지, avg(마일리지) over(partition by 도시)
from 고객
where 지역 = '경기도';

order by

over절에 order by를 사용하면 정렬기준을 지정할 수 있다
sum 윈도우 함수의 over절에 order by를 넣으면 누적 합을 구할 수 있다

고객의 마일리지 누적합

select 고객번호, 마일리지, sum(마일리지) over(order by 고객번호) as 누적합
from 고객

순위 윈도우 함수

함수	설명	ex
rank()	각 행의 순위 반환(동률 순위에 대해 동일한 숫자)	1, 2, 2, 4, 5
dense_rank()	순위 반환	1, 2, 2, 3, 4
row_number()	순위 반환	1, 2, 3, 4, 5
percent_rank()	백분율 순위 값의미(0~1사이 값으로 표현)	0, 0.25, 0.5, 0.5, 1
cume_dist()	파티션 내의 값의 누적 분표 계산	0.2, 0.4, 0.8, 0.8, 1
ntile(n)	정렬된 파티션의 행을 지정된 수의 그룹으로 나누며, 해당 행이 속한 그룹 번호를 반환	1, 1, 1, 2, 2

rank() over([partition by 컬럼] order by 컬럼)

경기도 고객의 각 도시별 마일리지가 많은 고객부터 순위

select 고객번호, 고객회사명, 담당자명, 마일리지, 
rank() over(partition by 도시 order by 마일리지 desc) as 순위
from 고객
where 지역 = '경기도';

행 순서 윈도우 함수

함수	설명
first_value()	첫 번째 값을 반환
last_value()	마지막 값을 반환
lag()	이전 행의 값을 반환
lead()	이후 행의 값을 반환
nth_value(n)	n번째 행의 값을 반환

윈도우함수(수식) over([partition by 컬럼] [order by 컬럼] [windowing절])

각 도시별 고객회사명, 마일리지, 최소 마일리지를 가진 고객회사명

select 도시, 고객회사명, 마일리지,
first_value(고객회사명) over(partition by 도시 order by 마일리지) 
from 고객;

범위 기준 옵션에 사용되는 키워드

키워드	설명
row	물리적인 단위로 행 집합을 지정한다. 같은 값을 가지는 행이 있으면 묶어서 한번에 연산
range	논리적인 주소에 의해 행 집합 지정. order by로 정렬된 컬럼에 대해 각 행마다 계산
between ~ and ~	윈도우의 시작과 끝 위치를 지정
unbounded preceding	윈도우에서 첫 번째 행을 의미
unbounded following	윈도우에서 마지막 행을 의미
current row	윈도우에서 현재 행을 의미

윈도우 내의 첫 번째 행부터 현재 행까지 범위로 지정
between range unbounded preceding and current row

현재 행을 기준으로 윈도우 내 앞 뒤 한 개씩을 범위로 지정
between 1 preceding and 1 following

윈도우 내 현재 행부터 마지막 행까지를 범위로 지정
between current row and unbounded following