[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을 명시해야 한다.

위 WINDOWING 절을 추가하면, 원하는 결과를 얻을 수 있다 !

MIN으로도 동일한 결과를 얻을 수 있다! 단, ORDER BY SAL 뒤에 DESC를 붙이면 안 된다. 이유는 위에 나왔던 원인과 비슷하다.

☑️ 3) LAG

💡

파티션 별로 특정 수만큼 앞선 데이터를 구하는 함수이다.

자신보다 연봉이 3만큼 더 높은 연봉을 가져오는 예시. LAG의 두 번째 인자인 3을 생략하면, DEFAULT 값은 1이 된다.

☑️ 4) LEAD

💡

LAG과 반대로, 파티션 별로 특정 수만큼 뒤에 있는 데이터를 구하는 함수이다.

자신보다 연봉이 3만큼 더 낮은 연봉을 가져오는 예시. LEAD 역시 두 번째 인자인 3을 생략하면, DEFAULT 값은 1이 된다.

✅ 5. 비율 함수

☑️ 1) RATIO_TO_REPORT

💡

파티션 별 합계에서 차지하는 비율을 구하는 함수이다.

전체 연봉에서 각 사원이 차지하는 연봉 비율을 구하는 예시

각 부서별 연봉 합계에서 각 사원이 차지하는 연봉의 비율을 구하는 예시

☑️ 2) PERCENT_RANK

💡

해당 파티션의 맨 위 끝 행을 0, 맨 아래 끝 행을 1로 놓고 현재 행이 위치하는 백분위 순위 값을 구하는 함수이다.

전체 사원을 연봉으로 정렬한 뒤, 각 사원이 위치한 백분위 순위 값을 구하는 예시

☑️ 3) CUME_DIST

💡

해당 파티션에서의 누적 백분율을 구하는 함수이다. 결과값은 0보다 크고 1보다 작거나 같은 값을 가진다.

연봉이 높은 순서대로 누적 백분율을 구하는 예시

☑️ 4) NTILE

💡

주어진 수만큼 행들을 n등분한 후 현재 행에 해당하는 등급을 구하는 함수이다.

연봉을 기준으로 각각 1, 3, 5, 7등분하여 각 사원의 등급을 나타낸 것이다. 위를 보면, 15개의 데이터를 7등분을 했을 때 남은 1개는 맨 위 그룹부터 채운다.

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만큼 이동한 행까지