WINDOW 함수
SELECT WINDOW함수( ) OVER ( <행 분할> <행 정렬> <대상 행 지정> ) FROM TABLE;
- 행과 행 사이의 연산을 하기 위함
- OVER : 대상, ~에게
- ( <행 분할> <행 정렬> <대상 행 지정> ) : 윈도우 함수 지정 범위
- 행 분할 : PARTITION BY (GROUP BY 역할)
- 행 정렬 : ORDER BY (ORDER BY 역할)
- 행 지정 : ROWS 또는 RANGE (WHERE 역할)
- 순위 함수 : RANK, DENSE_RANK, ROW_NUMBER
- 윈도우 집계 함수 : SUM, MAX, MIN, AVG, COUNT
- 비율 함수 : RATIO_TO_REPORT, PERCENT_RANK, CUME_DIST, NTITLE
ex)
SELECT JOB, SUM(SAL) OVER (PARTITION BY JOB
ORDER BY SAL DESC
ROWS UNBOUNDED PRECEDING) AS SUM_SAL
FROM EMP;
| ENAME | JOB | SAL |
| FORD | ANALYST | 3000 |
| SCOTT | ANALYST | 3000 |
| MILLER | CLERK | 1300 |
| ADAMS | CLERK | 1100 |
| JAMES | CLERK | 950 |
| SMITH | CLERK | 800 |
PARTITION BY JOB → JOB 기준으로 파티션을 친다고 생각(빨간색, 노란색으로 구분함)
ORDER BY SAL DESC → (각각의 JOB 안에서) SAL 기준 내림차순으로 정렬
ROWS → 행
UNBOUNDED PRECEDING → 한없이 위쪽에! 위에서부터 현재행까지 (어디까지라는게 따로 없으면 현재행까지!)
→ 위에서부터 내려오면서 현재행과 그위에 행들 모두 더하면 됨
| SUM_SAL |
| 3000 |
| 6000 (3000 + 3000) |
| 1300 |
| 2400 (1300 + 1100) |
| 3350 (1300 + 1100 + 950) |
| 4150 (1300 + 1100 + 950 + 800) |
⭐
ROWS
: 행의 위치를 기준으로 행 선택
UNBOUNDED PRECEDING
↑
PRECEDING
↑
CURRENT ROW
↓
FOLLOWING
↓
UNBOUNDED FOLLOWING
- 현재 행 기준 위에 위치한 행은 PRECEDING
- 현재 행 기준 아래에 위치한 행은 FOLLOWING
- 맨 위의 행은 UNBOUNDED PRECEDING (무한한 상위 행)
- 맨 아래의 행은 UNBOUNDED FOLLOWING (무한한 하위 행)
ex)
ROWS UNBOUNDED PRECEDING
- 윈도우 함수의 연산을 맨 위에부터 현재 행까지
ROWS UNBOUNDED FOLLOWING
- 윈도우 함수의 연산을 현재 행에서 맨 아래 행까지
ROWS 1 PRECEDING
- 윈도우 함수의 연산을 한 칸 위 행부터 현재 행까지
ROWS 2 FOLLOWING
- 윈도우 함수의 연산을 현재 행부터 두 칸 아래 행까지
⭐
RANGE
: 컬럼의 값을 기준으로 행 선택
RANGE 150 PRECEDING
- 현재 컬럼의 값을 기준으로 작은 값에서 150 이하로 차이가 나는 행들을 선택
RANGE BETWEEN 150 PRECEDING AND 150 FOLLOWING
- 현재 컬럼의 값 기준으로 작은 값에서 150 이하로 차이나고 현재 컬럼 값 기준으로 150 이하로 차이가 나는 행들을 선택
⭐
RANK()
: 중복되는 값은 공동 등수 수여, 다음 등수를 제거 / 연속적이지 않은 등수가 나올 수 있음
RANK() OVER (ORDER BY ~)
RANK - 중복하는 등수 존재, 중복이 있는 경우 연속하는 등수 없음
DENSE_RANK - 중복하는 등수 존재, 중복이 있어도 다음 연속하는 등수 표시
ROW_NUMBED - 중복하는 등수 없음, 연속하는 등수