윈도우 함수 사용방법
[윈도우 함수] 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개의 행을 이용합니다.
'파이썬으로 퀀트 프로그램 만들기 project > MySQL' 카테고리의 다른 글
MySQL - 테이블 결합, inner join, outer join (0) | 2023.07.31 |
---|---|
MySQL - case (0) | 2023.07.31 |
MySQL - like 술어, 문자열 일치, between 술어, isnull, in 술어 (0) | 2023.07.31 |
MySQL - 날짜함수, extract (0) | 2023.07.31 |
MySQL - error code: 1175, safe update 모드 해제 방법 (0) | 2023.07.27 |