[MySQL] WINDOW 함수
WINDOW 함수
기본구조
SELECT WINDOW함수( ) OVER ( <행 분할> <행 정렬> <대상 행 지정> )
FROM TABLE;
· 행 분할 : PARTITOIN BY < GROUP BY 역할
· 행 정렬 : ORDER BY < ORDER BY 역할
· 행 지정 : ROWS 또는 RANGE < WHERE의 역할
| 함수구분 | 종류 |
| 순위함수 | RANK, DENSE_RANK, ROW_NUMBER |
| 집계함수 | SUM, MAX, MIN, AVG, COUNT |
| 행 순서 함수 | FIRST_VALUE, LAST_VALUE, LAG, LEAD |
| 비율 함수 | RATIO_TO_REPORT, PERCENT_RANK, CUME_DIST, NTILE |
WINDOWIG절
ROWS & RANGE
ROWS 는 행의 수를 선택할 때 사용
RANGE 는 값의 범위를 선택할 때 사용
 |
UNBOUNDED PRECEDING PRECEDING (이전의 행) ▲ CURRENT ROW (현재 행인 경우) ▼ FOLLOWING : 다음에 나오는 행 UNBOUNDED FOLLOWING |
> 현재 연산 작업이 이루어지는 행 (CURRENT ROW) 기준
위로 위치한 행은 PRECEDING
아래로 위치한 행은 FOLLOWING
위치에 따라서 바로 한 칸 위는 1 PRECEDING
바로 한 칸 아래는 1 FOLLOWING 이라 부른다.
맨 위의 행은 UNBOUNDED PRECEDING (무한한 상위 행 )이라 부른다.
맨 아래 행은 UNBOUNDED FOLLOWING (무한한 하위 행) 이라 부른다.
예시)
SELECT JOB,
SUB(SAL) OVER (PARITTION BY JOB
ORDER BY SAL DESC
ROWS UNBOUNDED PRECEDING) AS SUM_SAL
FROM emp;* 예시와 같이 ROWS UNBOUNDED PRECEDING 이라고 시작점만 적은 경우
CURRENT ROW까지 자동 연산된다.
- UNBOUNDED PRECEDING ~ CURRENT ROW : 가장 상위행부터 현재행까지
- CURRENT ROW ~ UNBOUNDED FOLLOWING : 현재 행 부터 가장 하단행까지
ROWS에 대한 예시
| 코드 | 의미 |
| ROWS UNBOUNDED PRECEDING | 윈도우 함수 연산을 맨 위부터 현재행(CURRENT ROW) 까지 |
| ROWS UNBOUNDED FOLLOWING | 윈도우 함수 연산을 현재 행부터 맨 아래 행까지 |
| ROWS 1 PRECEDING | 윈도우 함수 연산을 현재 행 기준 한칸 위부터 현재행까지 계산 |
| ROWS 2 FOLLOWING | 윈도우 함수 연산을 형재 행 기준 한칸 아래까지 계산 |
예시)
SELECT JOB, ENAME, SAL,
SUM(SAL) OVER (ORDER BY SAL
ROWS 1 PRECEDING) AS CUME_SAL
FROM emp;
| 코드 | 의미 |
| ROWS BETWEEN UNBOUNDED PRECEDING AND 2 FOLLOWING |
가장 상위 행부터 현재 행 기준 아래 두 번째 행까지 포함해서 계산 |
| ROWS BETWEEN 2 PRECEDING AND CURRENT ROW |
현재 행 기준 두칸 위 행부터 현재행까지 포함해서 계산 |
| ROWS 1 PRECEDING AND UNBOUNDED FOLLOWING |
현재 행 기준 한칸 위부터 최하단 행까지 계산 |
| ROWS 1 FOLLOWING AND 3 FOLLOWING |
현재 행 기준 한칸 아래부터 세칸 아래 행까지 총 3행 계산 |
RANGE에 대한 예시
ROWS는 '행의 위치'를 기준으로 행을 선택한다면
RANGE는 '칼럼의 값' 기준으로 연산을 적용시킬 행을 선택.
SELECT JOB, ENAME, SAL,
SUM(SAL) OVER (ORDER BY SAL
RANGE 150 PRECEDING) AS CUME_SAL
FROM emp
WHERE JOB = 'CLERK' OR JOB= 'SALESMAN';> job이 CLERK 또는 SALESMAN인 사람들을
SAL 오름차순으로 정렬해서 현재 SAL 컬럼 기준으로 차이가 150 이하인 행의
job, ename, sal을 출력한다.
①
2행의 JAMES 의 경우 SAL 이 950
1행 SMITH의 800과 150 차이가 나므로 > ROWS 150 PRECEDING 만족
800과 950 을 더하여 > SUM(sal)
1750으로 값이 계산
②
반면 TURNER 의 경우 이전행이 1500 – 150 = 1350 이상이여야 값이 합쳐질 수 있는데
MILLER 의 SAL 이 1300
→ 연산의 대상이 되지 못함.
RANGE는 ORDER BY 를 통해 정렬된 컬럼에 같은 값이 존재하는 ROW가 여러 개일 경우,
동일한 컬럼값을 가지는 모든 ROW를 묶어서 연산
| 코드 | 의미 |
| RANGE 수치 PRECEDING 예) RANGE 150 PRECEDING |
현재 컬럼 값 기준으로 작은 값에서 수치 이하로 차이가 나는 행들을 선택적으로 계산 > 현재 컬럼 값을 기준으로 작은 값에서 150 이하로 차이가 나는 행들을 선택적으로 계산 |
| RANGE UNBOUNDED PRECEDING | 현재 칼럼의 값(포함하여) 기준 작은 값들을 모두 선택하여 계산 |
| RANGE BETWEEN 150 PRECEDING AND 150 FOLLOWING | 현재 칼럼의 값보다 작은 값에서 150 이하로 차이가 나고 현재 칼럼 값 기준으로 큰 값에서150이하로 차이가 나는 행들을 선택 |
| RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW |
현재 컬럼 값보다 작은 값을 가지는 행부터 현재 행까지 모두 선택 = RANGE UNBOUNDED PRECEDING |
RANGE 요약
> 컬럼의 값들 중 선택적으로 집계함수로 연산을 해야 한다거나 같은 값들이 필요하지 않을 경우 사용