MySQL - 윈도우 함수

윈도우 함수 사용방법

[윈도우 함수] over

([partition by [열 리스트]]

order by [정렬용 열 리스트])

 

여기서 partition by는 생략 가능합니다

 

 

#윈도우 함수로 사용할 수 있는 함수

-윈도우 전용 함수

rank, dense_rank, row_number 등

 

rank: 같은 순위인 행이 여러개 있으면 후순위로 건너뜀

예) 1위가 3개인 경우: 1위, 1위, 1위, 4위, ···

 

dense_rank: 같은 순위인 행이 여러개 있어도 후순위를 건너뛰지 않음

예) 1위가 3개인 경우: 1위, 1위, 1위, 2위, ···

 

row_number: 순위와 상관없이 연속 번호를 부여

예)  1위가 3개인 경우: 1위, 2위, 3위, 4위, ···

 

윈도우 전용 함수는 원칙적으로  select 구에만 사용할 수 있습니다

 

-집약함수

sum, avg, count, max, min 등

 

 

#윈도우 함수 사용 예시

 

-rank 사용

select col_1, col_2, col_3,
rank() over (partition by col_2 order by col_3) as ranking
from nameoftable;

→ col_2를 기준으로 partition을 나누고, 각 partition에서 col_3를 기준으로 ranking을 정하는 코드입니다.

partition by를 통해 구분된 행 집합을 '윈도우'라고 표현하며, 범위를 나타냅니다.

만약 partition by로 지정을 안하면 전체 테이블이 윈도우가 됩니다.

 

 

-전체 테이블을 윈도우로 지정

select col_1, col_2, col_3,
sum(col_3) over() as current_sum
from nameoftable;

→ 전체 테이블을 하나의 윈도우로 인식하고 col_3의 합계를 current_sum열에 표시합니다.

이를 응용하면 누적합계도 계산할 수 있습니다.

 

 

-누적 합계

select col_1, col_2, col_3,
sum(col_3) over(order by col_1) as current_sum
from nameoftable;

→ col_1이 각각의 행을 구분하는 고유한 id라고 했을 때, col_1을 기준으로 하여 

current_sum열에 누적 합계를 표시할 수 있습니다.

만약 sum 자리에 avg를 쓰면 누적 평균 또한 계산할 수 있습니다.

 

 

-열 기준이 아닌 범위 기준으로 윈도우 설정

select col_1, col_2, col_3,
avg(col_3) over(order by col_1 row 2 preceding) as moving_avg
from nameoftable;

→최근 3개 행에 대한 누적 평균을 계산해줍니다.

'row 2 preceding' 의 뜻이 현재 행과 앞의 두개 행을 사용한다는 뜻입니다.

 

 

 

-following 사용

select col_1, col_2, col_3,
avg(col_3) over(order by col_1 row between current row and 2 following) as moving_avg
from nameoftable;

→앞의 행이 아니라 뒤의 행을 이용하고 싶으면 preceding 대신 following을 입력하면 됩니다.

 

 

-preceding과 following 동시 사용

select col_1, col_2, col_3,
avg(col_3) over(order by col_1 row between 1 preceding and 1 following) as moving_avg
from nameoftable;

→ 앞의 행 1개와 뒤의 행 1개와 현재 행, 총 3개의 행을 이용합니다.