윈도우 함수
윈도우 함수의 개념
-
컬럼과 컬럼간의 연산이 아닌, 행과 행간의 비교 또는 연산을 처리하기 위한 함수
-
데이터베이스를 사용한 온라인 분석 처리 용도로 사용하기 위해서 SQL에 추가된 기능으로 OLAP 함수로도 불림
-
서브쿼리에서도 사용가능하지만 함수 자체에서 중첩으로 사용할 수는 없다.
-
윈도우 함수 처리로 인해 결과 건수가 달라지지는 않음
윈도우 함수 문법
| 윈도우 함수 | - 순위 함수, 집계 함수, 순서 함수, 비율 함수 |
| OVER | - 윈도우 함수에는 OVER 키워드가 필수 |
| PARTITION BY | - 전체를 여러 개의 그룹으로 구분하기 위한 기준을 정의 - GROUP BY 구문과 의미상으로 유사 |
| ORDER BY | - 그룹 내에서 순서를 정의 |
| WINDOWING 절 | - 그룹 내에서 윈도우 함수를 적용할 행의 범위를 정의 |
Windowing 절의 문법 | ROWS, RANGE
| ROWS | - 물리적인 범위 (ROWS 기준) - 물리적 단위로 행 집합 지정 |
| RANGE | - 문제의 조회 결과에서 동일한 레코드는 누적을 함께 계산하므로 RANGE 사용 - 논리적인 범위 (VALUE 기준) - 논리적 주소로 행 집합 지정 |
Start_Point
| BETWEEN ~ AND | - 윈도우 시작, 끝 위치 지정 - 최초의 레코드 |
| UNBOUNDED PRECEDING | - 윈도우 시작위치 = 첫 번째 행 - Start_Point만 들어갈 수 있음 - 파티션의 first row - 최초의 레코드 |
| CURRENT ROW | - 현재의 레코드 - START_POINT, END_POINT 모두 사용 가능. |
| 값 PRECEDING | - 값 만큼 이전의 레코드 |
| 값 FOLLOWING | - 값 만큼 이후의 레코드 |
End_Point
| UNBOUNDED FOLLOWING | - 윈도우 시작위치 = 마지막 행 - End_Point에만 들어갈 수 있으며, 파티션의 last row |
| CURRENT ROW | - 현재의 레코드 - START_POINT, END_POINT 모두 사용 가능. |
| 값 PRECEDING | - 이전의 레코드 |
| 값 FOLLOWING | - 이후의 레코드 |
예문 ★★★★★★(중요)
직업별로 급여 누적 합계
SUM(SAL) OVER( PARTITION BY JOB BY DESC
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW )
-> 첫째 ROW부터 진행되는 현재 ROW까지 윈도우로 설정되었으므로 누적합계
직업별로 SAL이 누적되서 나옴 다음 직업군은 다시 누적합계 계산
직업별 전체 합계
SUM(SAL) OVER( PARTITION BY JOB ORDER BY EMPNO
ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING )
-> 첫째 ROW부터 마지막 ROW까지 윈도우 범위로 설정되었으므로
각 직업별의 SAL전체 합계가 출력됨
순서 함수
✔ FIRST_VALUE
- 파티션 별 윈도우의 첫 번째 값을 반환
- MIN 함수와 같은 결과
✔ LAST_VALUE
- 파티션 별 윈도우의 마지막 값을 반환
- MAX 함수와 같은 결과
✔ LAG
- 파티션 별 윈도우에서 이전 행의 값을 반환
- 없는 경우 NULL
✔ LEAD
- 파티션 별 윈도우에서 이후 행의 값을 반환
- 다음 행을 가지고 옴
순위 함수
✔ RANK
- 순위를 표시할 때 공동순위가 있을 경우 개수를 뛰어 넘고 등수를 표시
- Ex) 1 1 3 4 5 6 7... 순서로 표시
✔ DENSE_RANK
- 순위를 표시할 때 공동순위가 있어도 계속 순서대로 순위를 표시한다.
- Ex) 1 1 2 3 3 4... 순서로 표시
✔ PERCENT_RANK
- 파티션별 윈도우에서 제일 먼저 나오는 것을 0으로, 제일 늦게 나오는 것을 1로 하여 값이 아닌 행의 순서별 백분율을 구하는 Window Function
✔ ROW_NUMBER
- 동일한 값과 별개로 행의 순서만 출력
비율 함수
✔ RATIO_TO_REPORT
- 파티션 내 SUM 값에 대한 백분율을 계산
✔ PERCENT_RANK
- 파티션별 윈도우에서 제일 먼저 나오는 것을 0으로, 제일 늦게 나오는 것을 1로 하여 값이 아닌 행의 순서별 백분율을 구하는 Window Function
✔ CUME_DIST
- 파티션 내 순서별 백분율을 계산하며, 0보다 큰 값부터 시작
✔ NTILE
- 인수 값으로 균등하게 n 등분하고, 남은 행은 앞에서부터 순차적으로 할당하여 계산
- NTILE(3)에서 7개 행을 2, 2, 2로 균등하게 3등분하고, 남은 행을 앞에서부터 순차적으로 할당하므로 3, 2, 2개씩 분할
