[SQL] 윈도우(WINDOW) 함수

Oracle에서 윈도우 함수(WINDOW FUNCTION)는 OVER 키워드와 함께 사용되며 역할에 따라 다음과 같이 나눌 수 있다. (순위 함수, 집계 함수, 행 순서 함수, 비율 함수)

Aug 18, 2024

Contents

✅ 1. 윈도우 함수 ✅ 2. 순위 함수 ✅ 3. 집계 함수 ✅ 4. 행 순서 함수 ✅ 5. 비율 함수

💡

OVER 키워드와 함께 사용되며 역할에 따라 다음과 같이 나눌 수 있다.

✅ 1. 윈도우 함수 ☑️ 1) 함수 종류 ☑️ 2) 사용 옵션 ✅ 2. 순위 함수 ☑️ 1) RANK ☑️ 2) DENSE_RANK ☑️ 3) ROW_NUMBER ✅ 3. 집계 함수 ☑️ 1) MIN 사용 예시 ✅ 4. 행 순서 함수 ☑️ 1) FIRST_VALUE ☑️ 2) LAST_VALUE ☑️ 3) LAG ☑️ 4) LEAD ✅ 5. 비율 함수 ☑️ 1) RATIO_TO_REPORT ☑️ 2) PERCENT_RANK ☑️ 3) CUME_DIST ☑️ 4) NTILE

✅ 1. 윈도우 함수

☑️ 1) 함수 종류

순위 함수	RANK, DENSE_RANK, ROW_NUMBER
집계 함수	SUM, MAX, MIN, AVG, COUNT
행 순서 함수	FIRST_VALUE, LAST_VALUE, LAG, LEAD
비율 함수	CUME_DIST, PERCENT_RANK, NTILE, RATIO_TO_REPORT

☑️ 2) 사용 옵션

범위	의미
`UNBOUNDED PRECEDING`	위쪽 끝 행
`UNBOUNDED FOLLOWING`	아래쪽 끝 행
`CURRENT ROW`	현재 행
`n PRECEDING`	현재 행에서 위로 n만큼 이동
`n FOLLOWING`	현재 행에서 아래로 n만큼 이동

기준	의미
`ROWS`	행 자체가 기준이 된다.
`RANGE`	행이 가지고 있는 데이터 값이 기준이 된다.

<사용 예시>


RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
> 처음부터 현재 행까지
> RANGE UNBOUNDED PRECEDING과 같은 의미

RANGE BETWEEN 10 PRECEDING AND CURRENT ROW
> 현재 행이 가지고 있는 값보다 10만큼 적은 행부터 현재 행까지
> RANGE 10 PRECEDING과 같은 의미

ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
> 현재 행부터 끝까지

ROWS BETWEEN CURRENT ROW AND 5 FOLLOWING
> 현재 행부터 아래로 5만큼 이동한 행까지

✅ 2. 순위 함수

`RANK`	1, 2, 2, 4, 5, 5, 7, …
`DENSE_RANK`	1, 2, 2, 3, 4, 4, 5, …
`ROW_NUMBER`	1, 2, 3, 4, 5, 6, 7, …

☑️ 1) RANK

💡

순위를 매기면서 같은 순위가 존재하면 존재하는 수만큼 다음 순위를 건너뛴다.

연봉(SAL)을 기준으로 RANK 순위를 매긴 예시

☑️ 2) DENSE_RANK

💡

순위를 매기면서 같은 순위가 존재하더라도 다음 순위를 건너뛰지 않고 이어서 매긴다.

연봉(SAL)을 기준으로 DENSE_RANK 순위를 매긴 예시

☑️ 3) ROW_NUMBER

💡

순위를 매기면서 동일한 값이라도 각기 다른 순위를 부여한다.

연봉(SAL)을 기준으로 ROW_NUMBER 순위를 매긴 예시

✅ 3. 집계 함수

💡

SUM, MAX, MIN, AVG, COUNT 등의 집계함수도 윈도우 함수로써 사용이 가능하다.

☑️ 1) MIN 사용 예시

각 부서 별 최소 연봉을 집계하여 나타낸 예시

✅ 4. 행 순서 함수

☑️ 1) FIRST_VALUE

💡

파티션 별 가장 선두에 위치한 데이터를 구하는 함수이다.

부서로 파티션 후, 각 부서의 최대 연봉을 가져오는 예시

☑️ 2) LAST_VALUE

💡

파티션 별 가장 끝에 위치한 데이터를 구하는 함수이다. FIRST_VALUE와 반대라고 생각하면 된다.

위 쿼리에서 FIRST_VALUE → LAST_VALUE로만 바꾸었는데 결과가 뭔가 이상하다..! 원래라면, 각 부서별로 최저 연봉을 부서별로 동일한 연봉을 가져와야 하는데, 위 결과에는 그냥 자신의 연봉을 가져온다.

💡

그 이유는, WINDOWING 절의 DEFAULT가 RANGE UNBOUNDED PRECEDING이기 때문이다. 따라서, 파티션의 범위가 맨 위 끝 행부터 현재 행까지로 지정되었기 때문이다. 이 문제를 해결하기 위해선, WINDOWING 절에 RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING을 명시해야 한다.