2-2-4. 윈도우 함수

1. 윈도우 함수의 개요

1️⃣ 윈도우 함수의 기초 개념

1) 윈도우 함수

• 윈도우 : 전체 데이터 중 특정한 부분만 발췌해서 보기 때문에 마치 창문을 통해 보는 것과 비슷하다는 의미에서 유래

• 고급 레벨의 SQL기능

• 보고서 작성 및 데이터 분석 등에서 매우 많이 활용됨 (분석 함수라고도 불림)

• 전체 데이터의 행들을 파티션으로 나누어 영역을 설정하고, 그 영역 안에서 행과 행 간의 관계를 쉽게 정의하기 위해 만들어진 함수

• 데이터 분석과 집계에 강력한 도구, 데이터베이스 내의 행을 비교하고 계산하는 다양한 방식으로 사용할 수 있음 → 복잡한 분석 작업을 효율적으로 수행

2️⃣ 윈도우 함수의 종류

1) 순위 함수

2) 집계 함수

3) 행 순서 관련 함수(오프셋 함수)

4) 비율 함수

5) 분석 함수 ❌

3️⃣ 윈도우 함수의 형식

1) 윈도우 함수의 형식

• OVER 키워드가 필수로 포함

• ROWS : 물리적인 결과 행의 수 / RANGE : 논리적인 값에 의한 범위
→ 둘 중 하나 선택해서 사용
단, WINDOWING 절은 SQL Server에서는 미지원

SELECT WINDOW_FUNCTION(ARGUMENTS)
			 OVER ([PARTITION BY 칼럼] [ORDER 절] [WINDOWING 절])
FROM 테이블명;

• WINDOW_FUNCTION : 기존에 사용하던 함수도 있고, 새롭게 WINDOW 함수용으로 추가된 함수도 있음

• ARGUMENTS(인수) : 함수에 따라 0 ~ N개의 인수가 지정될 수 있음

• PARTITION BY 절 : 전체 집합을 기준에 따라 소그룹으로 나눌 수 있음

• ORDER BY 절 : 어떤 항목에 대해 순위를 지정하고자 할 때 ORDER BY 절을 기술

• WINDOWING 절 : 함수의 대상이 되는 행 기준의 범위(계산이 되는 범위)를 세밀하게 지정할 수 있음

2) 윈도우 함수의 예

SELECT E.EMPNO, E.ENAME, E.DEPTNO, D.DNAME, E.SAL,
			 ROW_NUMBER() OVER(
												 PARTITION BY E.DEPTNO
												 ORDER BY E.SAL DESC
												 ) AS SALARY_RANK
FROM EMP E JOIN DEPT D
ON E.DEPTNO = D.DEPTNO;

2. 순위 함수

1️⃣ ROW_NUMBER()

1) ROW_NUMBER()

• SQL에서 각 행에 고유한 순위 번호를 할당하는 윈도우 함수

• 주로 데이터의 순서를 매기거나 특정 기준에 따라 행을 나열할 때 사용됨

2) ROW_NUMBER() 함수의 형식

ROW_NUMBER() OVER (PARTITION BY [파티션 기준] ORDER BY [정렬 기준])

• ROW_NUMBER() 뒤에 OVER 절이 오며, 두 가지 옵션을 괄호로 묶어 사용

• PARTITION BY [파티션 기준]
• 행을 기준으로 구분해 나누는 옵션
• 생략 가능하며, 생략할 경우 전체 결과 집합에 대해 순위 매김

• ORDER BY [정렬 기준]
• 순위를 매길 때 사용할 정렬 기준
• 반드시 지정해야 함
• 지정하지 않으면 에러 발생

3) ROW_NUMBER() 함수의 예 #1

• 전체 행에 대해 급여(SAL)가 높은 순으로 순위 매기는 함수(PARTITION BY 옵션 지정 안 함)

SELECT DEPTNO, ENAME, SAL, ROW_NUMBER() OVER(
																						 ORDER BY SAL DESC
																						 ) AS RANK
FROM EMP;

DEPTNO	ENAME	SAL	RANK
10	KING	5000	1
20	FORD	3000	2
20	SCOTT	3000	3
20	JONES	2975	4
30	BLAKE	2850	5
…	…	…	…

• 급여 값은 같은데 순위 다름 (ROW_NUMBER() 함수는 동일 순위 표시 불가 ❌)

• ROW_NUMBER() 함수는 같은 값을 가진 행들 사이의 순서를 데이터베이스 시스템에 의해 임의 결정됨
→ 동일한 ORDER BY 정렬 순서에 대해 동일한 결과의 순위 보장 못 함
→ 일관된 결과 얻기 위해 ORDER BY 절에 추가로 칼럼 지정해야 함
→ 이 때, 추가되는 칼럼을 ‘타이 브레이커(tie-breaker)’라 부름

• 형식

ROW_NUMBER() OVER (PARTITION BY [파티션 기준] ORDER BY [정렬 기준#1], [정렬 기준#2], ...)

• 예시

SELECT DEPTNO, ENAME, SAL, ROW_NUMBER() OVER (
																							ORDER BY SAL DESC, ENAME DESC
																							) AS RANK
FROM EMP;

DEPTNO	ENAME	SAL	RANK
10	KING	5000	1
20	SCOTT	3000	2
20	FORD	3000	3
20	JONES	2975	4
30	BLAKE	2850	5
…	…	…	…

4) ROW_NUMBER() 함수의 예 #2

• 부서별로 구분하고, 부서별로 급여(SAL)가 높은 순으로 순위를 매기는 함수
(PARTITION BY 옵션 지정)

SELECT DEPTNO, ENAME, SAL, ROW_NUMBER() OVER (
																							PARTITION BY DEPTNO
																							ORDER BY SAL DESC
																							) AS RANK
FROM EMP;

DEPTNO	ENAME	SAL	RANK
10	KING	5000	1
10	CLARK	2450	2
10	MILLER	1300	3
20	SCOTT	3000	1
20	FORD	3000	2
20	JONES	2975	3
20	ADAMS	1100	4
20	SMITH	800	5
30	BLAKE	2850	1
30	ALLEN	1600	2
30	TURNER	1500	3
30	MARTIN	1250	4
30	WARD	1250	5
30	JAMES	950	6

5) ROW_NUMBER() 함수의 예 #3

• 특정 부서에 대해서만 급여(SAL) 값이 높은 순으로 순위를 매기는 함수
(WHERE 절에서 특정 부서의 조건을 정의)

SELECT DEPTNO, ENAME, SAL, ROW_NUMBER() OVER (
																							ORDER BY SAL DESC
																							) AS RANK
FROM EMP
WHERE DEPTNO = 30;

DEPTNO	ENAME	SAL	RANK
30	BLAKE	2850	1
30	ALLEN	1600	2
30	TURNER	1500	3
30	WARD	1250	4
30	MARTIN	1250	5
30	JAMES	950	6

2️⃣ RANK()

1) RANK() 함수

• 동일한 값이 있을 때 동일한 순위 부여하고, 다음 순위 건너뜀

2) RANK() 함수의 형식

RANK() OVER (PARTITION BY [파티션 기준] ORDER BY [정렬 기준])

3) RANK() 함수의 예시

SELECT DEPTNO, ENAME, SAL, RANK() OVER (
																				ORDER BY SAL DESC
																				) AS RANK
FROM EMP;

3️⃣ DENSE_RANK()

1) DENSE_RANK() 함수

• 동일한 값이 있을 때 동일한 순위 부여하고, 다음 순위 건너뛰지 않고 연속된 순위를 매김

2) DENSE_RANK() 함수의 형식

DENSE_RANK() OVER (PARTITION BY [파티션 기준] ORDER BY [정렬 기준])

3) DENSE_RANK() 함수의 예시

SELECT DEPTNO, ENAME, SAL, DENSE_RANK() OVER (
																							ORDER BY SAL DESC
																							) AS RANK
FROM EMP;

3. 집계 함수

1️⃣ SUM()

1) SUM() 함수

• 지정된 칼럼의 숫자 값들의 합계 계산

• NULL 값은 무시됨
• NULL을 0으로 처리하고 싶다면 COALESCE 혹은 NVL 함수 사용해야 함

SUM(COALESCE(COLUMN_NAME, 0))
SUM(NVL(COLUMN_NAME,0))

2) SUM() 함수의 예

SELECT DEPTNO, SUM(SAL) AS DEPT_SAL_SUM
FROM EMP
GROUP BY DEPTNO;

SELECT DEPTNO, SUM(COALESCE(SAL,0)) AS DEPT_SAL_SUM
FROM EMP
GROUP BY DEPTNO;

SELECT DEPTNO, SUM(NVL(SAL,0)) AS DEPT_SAL_SUM
FROM EMP
GROUP BY DEPTNO;

※ COALESCE vs NVL

2️⃣ AVG()

1) AVG() 함수

• SQL에서 특정 칼럼의 평균 값 계산할 때 사용

• NULL 값 무시
• NULL 값을 0으로 취급하여 평균 값 계산하고 싶다면 COALESCE 또는 NVL 함수 사용

2) AVG() 함수의 예

SELECT DEPTNO, AVG(COALESCE(COMM, 0)) AS COMM_AVG
FROM EMP
GROUP BY DEPTNO;

3️⃣ COUNT()

1) COUNT() 함수

• 행의 개수 계산하는 집계 함수

• NULL 값은 무시할 수 있지만, 모든 행을 포함할 수도 있음

2) COUNT() 함수의 예

• COUNT(*)는 NULL을 포함

SELECT COUNT(*) AS TOTAL_SAL
FROM EMP;

TOTAL_SAL

14

• 특정 칼럼의 COUNT()는 기본적으로 NULL을 제외

SELECT COUNT(COMM) AS TOTAL_SAL
FROM EMP;

TOTAL_SAL

4

• 특정 칼럼의 NULL을 포함해서 COUNT하고 싶을 때

SELECT COUNT(COALESCE(COMM, 0)) AS TOTAL_SAL
FROM EMP;

TOTAL_SAL

14

4️⃣ MAX()

1) MAX() 함수

• 지정된 칼럼의 최댓값 반환

• NULL 값 무시

• 주로 수치 데이터, 날짜, 문자열 등 비교 가능한 값에 대해 적용

2) MAX() 함수의 예

SELECT DEPTNO, MAX(SAL) AS MAX_SAL
FROM EMP
GROUP BY DEPTNO
ORDER BY DEPTNO;

5️⃣ MIN()

1) MIN() 함수

• 지정된 칼럼의 최솟값 반환

• NULL 값 무시

• 주로 수치 데이터, 날짜, 문자열 등 비교 가능한 값에 대해 적용

2) MIN() 함수의 예

SELECT DEPTNO, MIN(SAL) AS MIN_SAL
FROM EMP
GROUP BY DEPTNO
ORDER BY DEPTNO;

6️⃣ 집계 함수 + OVER

1) 집계 함수 + OVER

• 집계 함수가 윈도우 함수로 작동하여 데이터의 특정 ‘윈도우’ 또는 ‘프레임’ 내에서 계산 수행

• OVER 절 : 집계 함수의 적용 범위 정의

2) 집계 함수 + OVER 예

SELECT EMPNO, DEPTNO, ENAME, SAL, AVG(SAL) OVER (
																								 PARTITION BY DEPTNO
																								 ORDER BY DEPTNO
																								 ) AS DEPT_SAL_AVG
FROM EMP;

4. 행 순서 관련 함수(오프셋 함수)

1️⃣ LAG(n)

1) LAG(n) 함수

• 주어진 행의 이전 행 값을 반환하는 윈도우 함수

• 데이터 분석과 시계열 데이터 처리에 매우 유용함

2) LAG(n) 함수의 형식

• OVER 절과 함께 사용되어 데이터를 파티션으로 나누고 정렬할 수 있음

LAG(칼럼명, [위치 n], [이전 행이 없을 때 반환할 값])
OVER (PARTITION BY [파티션 기준] ORDER BY [정렬 기준])

3) LAG(n) 함수의 예

SELECT DEPTNO, EMPNO, ENAME, SAL, LAG(SAL, 1, 0) OVER (
																											 ORDER BY EMPNO
																											 ) AS PRE_SAL
FROM EMP;

2️⃣ LEAD(n)

1) LEAD(n) 함수

• 주어진 행의 이후 행 값을 반환하는 윈도우 함수

• 데이터 분석과 시계열 데이터 처리에 매우 유용함

2) LEAD(n) 함수의 형식

• OVER 절과 함께 사용되어 데이터를 파티션으로 나누고 정렬할 수 있음

LEAD(칼럼명, [위치 n], [이후 행이 없을 때 반환할 값])
OVER (PARTITION BY [파티션 기준] ORDER BY [정렬 기준])

3) LEAD(n) 함수의 예

SELECT DEPTNO, EMPNO, ENAME, SAL, LEAD(SAL, 1, 0) OVER (
																												ORDER BY EMPNO
																												) AS NEXT_SAL
FROM EMP;

3️⃣ FIRST_VALUE()

1) FIRST_VALUE() 함수

• 데이터 집합 도는 파티션 내에서 첫 번째 값 반환

2) FIRST_VALUE() 함수의 형식

FIRST_VALUE(칼럼명) OVER (
												 PARTITION BY [파티션 기준]
												 ORDER BY [정렬 기준]
												 [WINDOW FRAME]  // 데이터 범위 지정 (생략 가능)
												 )

3) FIRST_VALUE() 함수의 예

SELECT DEPTNO, EMPNO, ENAME, SAL, FIRST_VALUE(SAL) OVER (
																												 PARTITION BY DEPTNO
																												 ORDER BY EMPNO
																												 ) AS DEPT_FIRST_SAL
FROM EMP;

4️⃣ LAST_VALUE()

1) LAST_VALUE() 함수

• 데이터 집합 도는 파티션 내에서 마지막 값 반환

2) LAST_VALUE() 함수의 형식

LAST_VALUE(칼럼명) OVER (
												PARTITION BY [파티션 기준]
												ORDER BY [정렬 기준]
												[WINDOW FRAME]  // 데이터 범위 지정 (생략 가능)
												)

3) LAST_VALUE() 함수의 예

SELECT DEPTNO, EMPNO, ENAME, SAL, LAST_VALUE(SAL) OVER (
																												PARTITION BY DEPTNO
																												ORDER BY EMPNO
																												) AS DEPT_LAST_SAL
FROM EMP;

• LAST_VALUE()는 기본적으로 현재 행까지만의 데이터를 고려함

• 아직 처리하지 않은 나머지 값 고려하기 위해서 범위를 명시해야 함 (➡️ 윈도우 함수의 프레임 절)

ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING

⬇️ 의미

ROWS                          // 모든 ROW에 대해
BETWEEN UNBOUNDED PRECEDING   // 파티션의 첫 번째 행부터
AND UNBOUNDED FOLLOWING       // 파티션의 마지막 행까지

• 프레임 절 적용

SELECT DEPTNO, EMPNO, ENAME, SAL,
			 LAST_VALUE(SAL) OVER (
														 PARTITION BY DEPTNO
														 ORDER BY EMPNO
														 ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
														 ) AS DEPT_LAST_SAL
FROM EMP;

5️⃣ NTH_VALUE(n)

1) NTH_VALUE(n) 함수

• 데이터 집합 또는 파티션 내에서 지정된 순서의 값 반환

2) NTH_VALUE(n) 함수의 형식

• OVER절과 함께 사용되어 데이터를 파티션으로 나누고 정렬할 수 있음

NTH_VALUE(칼럼명, n)
OVER (
			PARTITION BY [파티션 기준]
			ORDER BY [정렬 기준]
			[WINDOW FRAME]  // 데이터 범위 지정 (생략 가능)
			)

3) NTH_VALUE(n) 함수의 예

SELECT DEPTNO, EMPNO, ENAME, SAL,
			 NTH_VALUE(SAL, 2) OVER (
															 PARTITION BY DEPTNO
															 ORDER BY EMPNO
															 ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
															 ) AS SECOND_SAL
FROM EMP;

5. 비율 함수

1️⃣ COME_DIST()

1) CUME_DIST() 함수

• 누적 분포 함수

• 데이터 집합 내에서 특정 값 이하의 값들이 차지하는 비율 반환

• 0과 1 사이의 값 반환

2) CUME_DIST() 함수의 형식

CUME_DIST()
OVER (
			PARTITION BY [파티션 기준]
			ORDER BY [정렬 기준]
			)

3) CUME_DIST() 함수의 예

SELECT DEPTNO, EMPNO, ENAME, SAL, CUME_DIST() OVER (
																									  PARTITION BY DEPTNO
																									  ORDER BY SAL
																									  ) AS DEPT_CUME_DUST
FROM EMP;

2️⃣ PERCENT_RANK()

1) PERCENT_RANK() 함수

• 데이터 집합 내에서 각 값의 백분위 순위를 계산하여, 첫 번째 행의 백분위 순위는 0으로 시작해서 0과 1 사이의 값 반환

• 각 항목의 상대적 위치를 백분율로 쉽게 표현 가능

• PERCENT_RANK() = 순위 - 1 / 전체 행의 개수 - 1

• CUME_DIST()와의 차이점
• PERCENT_RANK() : 순위 기반 / 0부터 시작
• CUME_DIST() : 누적 분포 기반 / 0보다 큰 값부터 시작

2) PERCENT_RANK() 함수의 형식

PERCENT_RANK()
OVER (
			PARTITION BY [파티션 기준]
			ORDER BY [정렬 기준]
			)

3) PERCENT_RANK() 함수의 예

SELECT DEPTNO, EMPNO, ENAME, SAL, PERCENT_RANK() OVER (
																										   PARTITION BY DEPTNO
																										   ORDER BY SAL
																										  ) AS DEPT_PERCENT_RANK
FROM EMP;

3️⃣ NTILE(n)

1) NTILE(n) 함수

• 데이터 집합을 n개의 동일한 크기의 그룹으로 나누고, 각 행에 그룹 번호를 부여함

2) NTILE(n) 함수의 형식

NTILE(n)
OVER (
			PARTITION BY [파티션 기준]
			ORDER BY [정렬 기준]
			)

3) NTILE(n) 함수의 예

SELECT DEPTNO, EMPNO, ENAME, SAL, NTILE(3) OVER (
																								 PARTITION BY DEPTNO
																								 ORDER BY SAL
																								) AS DEPT_NTILE
FROM EMP;