[SQL] Window Function 정리
Window Function 윈도우 함수
행과 행 간을 비교, 연산, 정의하기 위한 함수로, 분석 함수나 순위 함수라고도 한다.
- 윈도우 함수에는 기존에 사용하던 집계함수도 있고, 윈도우 함수 전용으로 만들어진 함수도 있다.
- 윈도우 함수는 다른 함수와 달리 중첩해서 사용할 수 없지만, 서브쿼리에서는 사용할 수 있다.
* 윈도우 함수와 GROUP BY절
GROUP BY절과 비슷하게 데이터를 그룹화하여 집계한다.
가장 큰 차이점 -> '집약 과정'이 존재하는가?
- GROUP BY절은 집계된 결과만 보여준다. 지정된 컬럼으로 데이터를 자르고, 집계함수를 이용해 집약시키기 때문에 행의 수가 줄어든다.
- 윈도우 함수는 PARTITION BY에 지정된 컬럼으로 데이터를 자르므로 기존 데이터에 집계된 값을 추가하여 나타낸다. 즉, 행의 수가 그대로 유지된다.
윈도우 함수 문법
SELECT WINDOW_FUNCTION (ARGUMENTS) OVER ([PARTITION BY 컬럼] [ORDER BY 컬럼] [WINDOWING 절])
FROM 테이블명;
- WINDOW_FUNCTION : 윈도우 함수
- ARGUMENTS(인수) : 함수에 따라 0 ~ N개 인수가 지정될 수 있다.
- OVER 절 : 윈도우 함수에서 꼭 들어가야 하고, OVER 내부에 PARTITION BY 절과 ORDER BY 절이 들어간다.
- PARTITION BY 절 : 전체 집합을 기준에 의해 소그룹으로 나눌 수 있다. (생략 가능. 생략 시 전체 행에 대해 처리)
- ORDER BY 절 : 어떤 항목에 대해 순위를 지정할 지 order by 절을 기술한다. (생략 가능)
- WINDOWING 절 : WINDOWING 절은 함수의 대상이 되는 행 기준의 범위를 강력하게 지정할 수 있다. (SQL Server에서 지원 X)
윈도우 함수 종류
- 그룹 내 순위 함수(RANK) : RANK, DENSE_RANK, ROW_NUMBER
- 그룹 내 집계 함수(AGGREGATE) : SUM, MAX, MIN, AVG, COUNT (SQL Server는 OVER 절의 OREDER BY 지원 X)
- 그룹 내 행 순서 함수 : FIRST_VALUE, LAST_VALUE, LAG, LEAD (SQL Server에서는 지원 X)
- 그룹 내 비율 함수 : RATIO_TO_REPORT, PERCENT_RANK, CUME_DIST, NTILE
1. 그룹 내 순위 함수
모두 동일한 값에 대해 동일한 순위를 부여하지만, 각각 차이점이 있다.
RANK와 DENSE_RANK는 모두 공동순위가 존재하지만, RANK는 중복 인원 수만큼 다음 순위가 밀려나는 반면, DENSE_RANK는 중복 인원 수를 검사하지 않고 바로 다음 순위를 부여한다. ROW_NUMBER은 동일한 값이어도 고유한 순위를 부여한다.
RANK / DENSE_RANK / ROW_NUMBER 함수의 순위 부여 비교
| number | rank | dense_rank | row_number |
| 100 | 1 | 1 | 1 |
| 90 | 2 | 2 | 2 |
| 90 | 2 | 2 | 3 |
| 88 | 4 | 3 | 4 |
| 70 | 5 | 4 | 5 |
2. 그룹 내 집계 함수
SUM, MAX, MIN, AVG, COUNT
주의) 집계 함수는 NULL 값을 제외하고 처리한다. NULL값으로 가능한 연산은 IS NULL, IS NOT NULL 뿐이다.
3. 그룹 내 행 순서 함수
- FIRST_VALUE : 파티션별 윈도우에서 가장 먼저 나온 값을 출력한다. 공동 등수를 인정하지 않고 처음 나온 행만 가져오며, MIN 함수를 쓰는 것과 동일한 결과를 얻는다.
- LAST_VALUE : 파티션별 윈도우에서 가장 마지막에 나온 값을 출력한다. 공동 등수를 인정하지 않고 마지막에 나온 행만 가져오며, MAX 함수를 쓰는 것과 동일한 결과를 얻는다.
- LAG : 파티션별 윈도우에서 이전 몇 번째 행의 값을 출력하고, 인자는 최대 3개까지 가진다.
LAG(컬럼, 인자1, 인자2)
인자1 : 몇 번째 앞의 행을 가져올지 결정. Defalut 값은 1.
인자2 : 가져올 행이 없을 경우 Defalut 값을 지정해주고, NVL/ISNULL 함수 기능과 동일.
- LEAD : 파티션별 윈도우에서 이후 몇 번째 행의 값을 출력하고, 인자는 최대 3개까지 가진다.
4. 그룹 내 비율 함수
- RATIO_TO_REPORT : 파티션 내 전체 SUM(컬럼)에 대한 행별 컬럼 값의 백분율을 소수점으로 출력한다. 결과값은 0~1 사이, 개별 비율의 합은 1이다.
- PERCENT_RANK : 파티션별로 가장 먼저 나오는 값을 0, 가장 마지막에 나오는 값을 1로 해서 행 순서별 백분율을 출력한다. 구간을 나누어 백분율로 표시한다. 다시 말해, 순서상 몇 번째에 위치하는지 0~1 사이의 값으로 출력한다.
- CUME_DIST : 파티션별 전체 건수에서 현재 행보다 작거나 같은 건수에 대한 누적백분율을 구한다. 다시 말해, 누적 순서상 몇 번째에 위치하는지 0~1 사이의 값으로 출력한다.
- NTILE : 파티션별 전체 건수를 ARGUMENT값으로 N등분한 결과를 출력한다.
예를 들어 NTILE(4)의 인자는 4, 전체 건수를 4등분한 결과를 출력한다.