4.4 윈도우함수
4.4.1 윈도우함수의 개념
윈도우함수 : 행과 행 간의 관계를 나타내는 연산을 쉽게 하기 위한 함수. 각 행의 기존 구성을 유지한 상태로 새로운 칼럼을 추가하거나 기존의 칼럼을 변경한다. 모든 윈도우함수는 OVER 키워드와 함께 사용된다.
이후의 윈도우함수 예제에서는 그룹함수에서와 같이 아래 테이블을 사용한다.
| DEPARTMENT | GRADE | SCORE |
|---|---|---|
| 소프트웨어학과 | 1학년 | 3.7 |
| 소프트웨어학과 | 2학년 | 4.1 |
| 소프트웨어학과 | 2학년 | 2.6 |
| 소프트웨어학과 | 3학년 | 3.5 |
| 해고방지학과 | 2학년 | 3.6 |
| 해고방지학과 | 3학년 | 4.2 |
| 해고방지학과 | 4학년 | 2.9 |
| 해고방지학과 | 4학년 | 3.7 |
4.4.2 순위함수
순위함수 : 순위를 계산하는 함수.
| 함수명 | 설명 | 예 |
|---|---|---|
| RANK | 동일 순위는 같은 순위값을 가지며, 순위는 누적 순위 + 1이 된다. |
1,2,2,4,4,4,7 |
| DENSE_RANK | 동일 순위는 같은 순위값을 가지며, 순위는 앞 순위 + 1이 된다. |
1,2,2,3,3,3,4 |
| ROW_NUMBER | 동일 순위라도 각각의 행이 고유한 순위를 가진다. |
1,2,3,4,5,6,7 |
①
SELECT DEPARTMENT, GRADE, SCORE,
RANK() OVER(ORDER BY SCORE DESC) AS RANK
FROM STUDENT;
=> SCORE가 높은 것부터 순위를 매긴다. 순위는 누적 순위 + 1.
| DEPARTMENT | GRADE | SCORE | RANK |
|---|---|---|---|
| 해고방지학과 | 3학년 | 4.2 | 1 |
| 소프트웨어학과 | 2학년 | 4.1 | 2 |
| 소프트웨어학과 | 1학년 | 3.7 | 3 |
| 해고방지학과 | 4학년 | 3.7 | 3 |
| 해고방지학과 | 2학년 | 3.6 | 5 |
| 소프트웨어학과 | 3학년 | 3.5 | 6 |
| 해고방지학과 | 4학년 | 2.9 | 7 |
| 소프트웨어학과 | 2학년 | 2.6 | 8 |
②
SELECT DEPARTMENT, GRADE, SCORE,
DENSE_RANK() OVER(ORDER BY SCORE DESC) AS RANK
FROM STUDENT;
=> SCORE가 높은 것부터 순위를 매긴다. 순위는 앞 순위 + 1.
| DEPARTMENT | GRADE | SCORE | RANK |
|---|---|---|---|
| 해고방지학과 | 3학년 | 4.2 | 1 |
| 소프트웨어학과 | 2학년 | 4.1 | 2 |
| 소프트웨어학과 | 1학년 | 3.7 | 3 |
| 해고방지학과 | 4학년 | 3.7 | 3 |
| 해고방지학과 | 2학년 | 3.6 | 4 |
| 소프트웨어학과 | 3학년 | 3.5 | 5 |
| 해고방지학과 | 4학년 | 2.9 | 6 |
| 소프트웨어학과 | 2학년 | 2.6 | 7 |
③
SELECT DEPARTMENT, GRADE, SCORE,
ROW_NUMBER() OVER(ORDER BY SCORE DESC) AS RANK
FROM STUDENT;
=> SCORE가 높은 것부터 순위를 매긴다. 행 인덱스를 순위로 갖는다.
| DEPARTMENT | GRADE | SCORE | RANK |
|---|---|---|---|
| 해고방지학과 | 3학년 | 4.2 | 1 |
| 소프트웨어학과 | 2학년 | 4.1 | 2 |
| 소프트웨어학과 | 1학년 | 3.7 | 3 |
| 해고방지학과 | 4학년 | 3.7 | 4 |
| 해고방지학과 | 2학년 | 3.6 | 5 |
| 소프트웨어학과 | 3학년 | 3.5 | 6 |
| 해고방지학과 | 4학년 | 2.9 | 7 |
| 소프트웨어학과 | 2학년 | 2.6 | 8 |
4.4.3 집계함수
집계함수 : 앞에서 배운 집계함수와 같다. OVER절을 함께 사용하여 파티션 별로 집계하거나 누적 집계를 계산할 수 있다.
PATITION BY : OVER와 같이 사용하여 원본 행을 유지하며 그룹별 계산 수행을 돕는다.
| 함수명 | 설명 |
|---|---|
| COUNT | 값이 존재하는 행의 개수를 파티션 별로 집계하거나 누적 집계를 반환한다. |
| SUM | 파티션 별 합계를 구하거나 누적 합계를 반환한다. |
| AVG | 파티션 별 평균을 구하거나 누적 평균을 반환한다. |
| MIN | 파티션 별 최솟값을 반환한다. |
| MAX | 파티션 별 최댓값을 반환한다. |
①
SELECT DEPARTMENT, GRADE,
COUNT(*) OVER(PARTITION BY GRADE) AS PART_CNT
FROM STUDENT;
=> 학년별로 파티션을 나누고 파티션별 개수를 구한다.
| DEPARTMENT | GRADE | PART_CNT |
|---|---|---|
| 소프트웨어학과 | 1학년 | 1 |
| 소프트웨어학과 | 2학년 | 3 |
| 소프트웨어학과 | 2학년 | 3 |
| 해고방지학과 | 2학년 | 3 |
| 소프트웨어학과 | 3학년 | 2 |
| 해고방지학과 | 3학년 | 2 |
| 해고방지학과 | 4학년 | 2 |
| 해고방지학과 | 4학년 | 2 |
②
SELECT DEPARTMENT, GRADE, SCORE,
MAX(SCORE) OVER(PARTITION BY GRADE) AS PART_MAX
FROM STUDENT;
=> 학년별로 파티션을 나누고 파티션별 SCORE 최댓값을 구한다.
| DEPARTMENT | GRADE | SCORE | PART_MAX |
|---|---|---|---|
| 소프트웨어학과 | 1학년 | 3.7 | 3.7 |
| 소프트웨어학과 | 2학년 | 4.1 | 4.1 |
| 소프트웨어학과 | 2학년 | 2.6 | 4.1 |
| 해고방지학과 | 2학년 | 3.6 | 4.1 |
| 소프트웨어학과 | 3학년 | 3.5 | 4.2 |
| 해고방지학과 | 3학년 | 4.2 | 4.2 |
| 해고방지학과 | 4학년 | 2.9 | 3.7 |
| 해고방지학과 | 4학년 | 3.7 | 3.7 |
4.4.4 행 순서함수
| 함수명 | 설명 |
|---|---|
| FIRST_VALUE | 파티션 별로 가장 처음 값을 반환한다. |
| LAST_VALUE | 파티션 별로 가장 마지막 값을 반환한다. |
| LAG | 입력된 인자의 값만큼 이전 행의 값을 반환한다. |
| LEAD | 입력된 인자의 값만큼 이후 행의 값을 반환한다. |
①
SELECT DEPARTMENT, GRADE, SCORE,
FIRST_VALUE(SCORE) OVER(PARTITION BY GRADE) AS PART_FIRST
FROM STUDENT;
=> 학년별로 파티션을 나누고 파티션별 첫 번째 SCORE를 구한다.
| DEPARTMENT | GRADE | SCORE | PART_FIRST |
|---|---|---|---|
| 소프트웨어학과 | 1학년 | 3.7 | 3.7 |
| 소프트웨어학과 | 2학년 | 4.1 | 4.1 |
| 소프트웨어학과 | 2학년 | 2.6 | 4.1 |
| 해고방지학과 | 2학년 | 3.6 | 4.1 |
| 소프트웨어학과 | 3학년 | 3.5 | 3.5 |
| 해고방지학과 | 3학년 | 4.2 | 3.5 |
| 해고방지학과 | 4학년 | 2.9 | 2.9 |
| 해고방지학과 | 4학년 | 3.7 | 2.9 |
②
SELECT DEPARTMENT, GRADE, SCORE,
LAG(SCORE, 2) OVER(ORDER BY SCORE) AS SCORE_2
FROM STUDENT;
=> SCORE 순으로 정렬하고 2행 앞의 SCORE값을 구한다.
| DEPARTMENT | GRADE | SCORE | SCORE_2 |
|---|---|---|---|
| 소프트웨어학과 | 2학년 | 2.6 | [NULL] |
| 해고방지학과 | 4학년 | 2.9 | [NULL] |
| 소프트웨어학과 | 3학년 | 3.5 | 2.6 |
| 해고방지학과 | 2학년 | 3.6 | 2.9 |
| 소프트웨어학과 | 1학년 | 3.7 | 3.5 |
| 해고방지학과 | 4학년 | 3.7 | 3.6 |
| 소프트웨어학과 | 2학년 | 4.1 | 3.7 |
| 해고방지학과 | 3학년 | 4.2 | 3.7 |
4.4.5 비율함수
비율함수 : 파티션 별로 전체 개수나 합계를 구한 후, 그에 대한 비율을 구하는 함수.
| 함수명 | 설명 |
|---|---|
| CUME_DIST | 파티션 별로 해당 값 이하 행의 전체에 대한 비율을 반환한다. 마지막 행의 값이 1이 된다. |
| PERCENT_RANK | 파티션 별로 순위별 백분율을 반환한다. 가장 첫 행이 0, 마지막 행이 1이 된다. |
| NTILE | 파티션을 N등분하여 1부터 N까지의 등급 값을 반환한다. 전체 개수를 N으로 나누고 나머지를 앞 등급 순서로 1씩 배분하여 등급별 개수를 정한다. |
| RATIO_TO_REPORT | 인자값의 파티션 별 합계에 대한 비율을 반환한다. |
SELECT DEPARTMENT, GRADE, SCORE,
ROUND(CUME_DIST() OVER(ORDER BY SCORE), 3) AS C_DIST,
ROUND(PERCENT_RANK() OVER(ORDER BY SCORE), 3) AS P_RANK,
NTILE(3) OVER(ORDER BY SCORE) AS NTILE,
ROUND(RATIO_TO_REPORT(SCORE) OVER(), 3) AS REPORT
FROM STUDENT
ORDER BY SCORE;
| DEPARTMENT | GRADE | SCORE | C_DIST | P_RANK | NTILE | REPORT |
|---|---|---|---|---|---|---|
| 소프트웨어학과 | 2학년 | 2.6 | 0.125 | 0.000 | 1 | 0.092 |
| 해고방지학과 | 4학년 | 2.9 | 0.250 | 0.143 | 1 | 0.103 |
| 소프트웨어학과 | 3학년 | 3.5 | 0.375 | 0.286 | 1 | 0.124 |
| 해고방지학과 | 2학년 | 3.6 | 0.500 | 0.429 | 2 | 0.127 |
| 소프트웨어학과 | 1학년 | 3.7 | 0.750 | 0.571 | 2 | 0.131 |
| 해고방지학과 | 4학년 | 3.7 | 0.750 | 0.571 | 2 | 0.131 |
| 소프트웨어학과 | 2학년 | 4.1 | 0.875 | 0.857 | 3 | 0.145 |
| 해고방지학과 | 3학년 | 4.2 | 1.000 | 1.000 | 3 | 0.148 |