윈도우 함수(WINDOW 함수) 란?

윈도우 함수는 SQL에서 특정 데이터셋의 '윈도우'나 '프레임'에 대해 집계, 순위 및 분석 작업을 수행할 수 있는 강력한 도구이다. 이 함수들은 일반 집계 함수와 비슷하지만, 결과를 그룹화하지 않고 원본 데이터의 구조를 유지하면서 각 행에 대한 계산 결과를 반환할 수 있다는 차이점이 있다. 윈도우 함수는 주로 데이터의 특정 순위를 정하거나, 이동 평균을 계산하거나, 각 그룹 내에서의 행의 위치를 결정하는 등의 작업에 사용된다. 분석 함수 (ANALYTIC FUNCTION) 나 순위 함수 (RANK FUNCTION) 로도 알려져 있는 윈도우 함수는 데이터웨어하우스에서 발전한 기능이다.

복잡하거나 자원을 많이 사용하는 튜닝 기법들을 대체할 수 있는 DBMS의 새로운 기능은 튜닝 관점에서도 최적화된 방법이므로 적극적으로 활용할 필요가 있다. 같은 결과가 나오는 변형된 튜닝 문장보다는 DBMS 벤더에서 최적화된 자원을 사용하도록 만들어진 새로운 기능을 사용하는 것이 일반적으로 더욱 효과가 좋기 때문이다.

WINDOW 함수는 기존에 사용하던 집계 함수도 있고, 새로이 WINDOW 함수 전용으로 만들어진 기능도 있다. 그리고 WINDOW 함수는 다른 함수와는 달리 중첩(NEST)해서 사용하지는 못하지만 서브쿼리에서는 사용할 수 있다.

 

 

윈도우 함수 문법

SELECT WINDOW_FUNCTION (ARGUMENTS) OVER ( 
    [PARTITION BY column1, column2, ...] 
    [ORDER BY column1 [ASC|DESC], column2 [ASC|DESC], ...]
    [WINDOWING 절]
)
FROM TABLE;

 

WINDOWING 절

  • 함수의 대상이 되는 행 기준의 범위를 강력하게 지정할 수 있다.
  • 주로 ROWS 또는 RANGE 키워드를 사용하여 지정하며, 파티션 내의 데이터에 대해 어떻게 창(window)을 설정할지 정의한다.
  • ROWS는 물리적인 결과 행의 수를, RANGE는 논리적인 값에 의한 범위를 나타내는데, 둘 중의 하나를 선택해서 사용할 수 있다.
  • 다만, WINDOWING 절은 SQL Server에서는 지원하지 않는다.

 

 

 

RANK 함수

  • ORDER BY 를 포함한 쿼리문에서 특정 항목 칼럼 에 대한 순위를 구하는 함수
  • 특정 범위 (PARTITION) 내에서 순위를 구할 수도 있고 전체 데이터에 대한 순위를 구할 수도 있다.
  • 동일한 값에 대해서는 동일한 순위를 부여하게 된다.
SELECT 	JOB, ENAME, SAL,
	RANK() OVER (ORDER BY SAL DESC) ALL_RANK,
	RANK() OVER (PARTITION BY JOB ORDER BY SAL DESC) JOB_RANK
FROM EMP;
JOB		ENAME	SAL	ALL_RANK	JOB_RANK
---		------	---	---------	---------
PRESIDENT 	KING 	5000 	1 		1
ANALYST 	FORD 	3000 	2 		1
ANALYST 	SCOTT 	3000 	2 		1
MANAGER 	JONES 	2975 	4 		1
MANAGER 	BLAKE 	2850 	5 		2
MANAGER 	CLARK 	2450 	6 		3
SALESMAN 	ALLEN 	1600 	7 		1
SALESMAN 	TURNER 	1500 	8 		2
CLERK 		MILLER 	1300 	9 		1
SALESMAN 	WARD 	1250 	10 		3
SALESMAN 	MARTIN 	1250 	10 		3
CLERK 		ADAMS 	1100 	12 		2
CLERK 		JAMES 	950 	13 		3
CLERK 		SMITH 	800 	14 		4

 

 

 

DENSE_RANK 함수

  • RANK 함수와 같지만, 차이점은 동일한 등수가 나온만큼 카운트를 더하지 않고 연속적인 순위를 배정한다.
SELECT 	JOB, ENAME, SAL,
	RANK() OVER (ORDER BY SAL DESC) RANK,
	DENSE_RANK() OVER (ORDER BY SAL DESC) DENSE_RANK
FROM EMP;
JOB		ENAME	SAL	RANK 	DENSE_RANK
---		------ 	--- 	----- 	-----------
PRESIDENT 	KING 	5000 	1 	1
ANALYST 	FORD 	3000 	2 	2
ANALYST 	SCOTT 	3000 	2 	2
MANAGER 	JONES 	2975 	4 	3
MANAGER 	BLAKE 	2850 	5 	4
MANAGER 	CLARK 	2450 	6 	5
SALESMAN 	ALLEN 	1600 	7 	6
SALESMAN 	TURNER 	1500 	8 	7
CLERK 		MILLER 	1300 	9 	8
SALESMAN 	WARD 	1250 	10 	9
SALESMAN 	MARTIN 	1250 	10 	9
CLERK 		ADAMS 	1100 	12 	10
CLERK 		JAMES 	950 	13 	11
CLERK 		SMITH 	800 	14 	12

 

 

 

ROW_NUMBER 함수

  • RANK나 DENSE_RANK 함수가 동일한 값에 대해서는 동일한 순위를 부여하는데 반해, 동일한 값이라도 고유한 순위를 연속적으로 부여한다.
SELECT 	JOB, ENAME, SAL,
	RANK() OVER (ORDER BY SAL DESC) RANK,
	ROW_NUMBER() OVER (ORDER BY SAL DESC) ROW_NUMBER
FROM EMP;
JOB		ENAME	SAL	RANK	ROW_NUMBER
---		------	---	-----	-----------
PRESIDENT 	KING 	5000 	1 	1
ANALYST 	FORD 	3000 	2 	2
ANALYST 	SCOTT 	3000 	2 	3
MANAGER 	JONES 	2975 	4 	4
MANAGER 	BLAKE 	2850 	5 	5
MANAGER 	CLARK 	2450 	6 	6
SALESMAN 	ALLEN 	1600 	7 	7
SALESMAN 	TURNER 	1500 	8 	8
CLERK 		MILLER 	1300 	9 	9
SALESMAN 	WARD 	1250 	10 	10
SALESMAN 	MARTIN 	1250 	10 	11
CLERK 		ADAMS 	1100 	12 	12
CLERK 		JAMES 	950 	13 	13
CLERK 		SMITH 	800 	14 	14

 

 

 

SUM 함수

예제 - 1

SELECT 	MGR, ENAME, SAL, 
	SUM(SAL) OVER (PARTITION BY MGR) MGR_SUM
FROM EMP;
MGR	ENAME	SAL	MGR_SUM
----	------	---	---------
7566 	FORD 	3000 	6000
7566 	SCOTT 	3000 	6000
7698 	JAMES 	950 	6550
7698 	ALLEN 	1600 	6550
7698 	WARD 	1250 	6550
7698 	TURNER 	1500 	6550
7698 	MARTIN 	1250 	6550
7782 	MILLER 	1300 	1300
7788 	ADAMS 	1100 	1100
7839 	BLAKE 	2850 	8275
7839 	JONES 	2975 	8275
7839 	CLARK 	2450 	8275
7902 	SMITH 	800 	800
	KING 	5000 	5000

 

 

예제 - 1

SELECT 	MGR, ENAME, SAL,
	SUM(SAL) OVER (
    		PARTITION BY MGR ORDER BY SAL RANGE UNBOUNDED PRECEDING
	) as MGR_SUM
FROM EMP

RANGE UNBOUNDED PRECEDING

현재 행을 기준으로 파티션 내의 첫 번째 행까지의 범위를 지정한다. 현재 행에서 지정된 방향(여기서는 시작부터 현재 행까지)으로 데이터의 범위를 지정하는 데 사용되는데, 이 구문을 사용하면 현재 행을 포함하여 그 이전의 모든 행을 계산 범위로 포함한다.

 

RANGE UNBOUNDED PRECEDING
정렬 기준의 값이 같은 모든 행을 포함하여 계산한다. 
즉, 동일한 값에 대해 경계가 확장된다.
ROWS UNBOUNDED PRECEDING
물리적인 행의 수를 기준으로 하여 계산한다. 
즉, 동일한 값이어도 상관없이 순서대로 지정된 행만큼만 포함한다.

MGR	ENAME	SAL	MGR_SUM
----	------	---	---------
7566 	SCOTT 	3000 	6000
7566 	FORD 	3000 	6000

7698 	JAMES 	950 	950
7698 	WARD 	1250 	3450
7698 	MARTIN 	1250 	3450
7698 	TURNER 	1500 	4950
7698 	ALLEN 	1600 	6550

7782 	MILLER 	1300 	1300

7788 	ADAMS 	1100 	1100

7839 	CLARK 	2450 	2450
7839 	BLAKE 	2850 	5300
7839 	JONES 	2975 	8275

7902 	SMITH 	800 	800
	KING 	5000 	5000

 

 

 

MAX / MIN 함수

  • 파티션별 윈도우의 최대/최소값을 구할 수 있다.
  • 다음은 INLINE VIEW를 이용해 파티션별 최대값을 가진 행만 추출하는 예제이다.
SELECT MGR, ENAME, SAL
FROM (
	SELECT 	MGR, ENAME, SAL, 
		MAX(SAL) OVER (PARTITION BY MGR) as IV_MAX_SAL
	FROM EMP
    )
WHERE SAL = IV_MAX_SAL ;
MGR	ENAME	SAL
----	------	---
7566 	FORD 	3000
7566 	SCOTT 	3000
7698 	ALLEN 	1600
7782 	MILLER 	1300
7788 	ADAMS 	1100
7839 	JONES 	2975
7902 	SMITH 	800
	KING 	5000

 

 

 

AVG 함수

SELECT 	MGR, ENAME, HIREDATE, SAL,
	ROUND (AVG(SAL) OVER (
    		PARTITION BY MGR ORDER BY HIREDATE
		ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING)) as MGR_AVG
FROM EMP;

ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING

현재 행을 기준으로 파티션 내에서 앞의 한 건, 현재 행, 뒤의 한 건을 범위로 지정한다.

MGR	ENAME	HIREDATE	SAL	MGR_AVG
----	------	---------	---	---------
7566 	FORD 	1981-12-03 	3000 	3000
7566 	SCOTT 	1987-07-13 	3000 	3000

7698 	ALLEN 	1981-02-20 	1600 	1425
7698 	WARD 	1981-02-22 	1250 	1450
7698 	TURNER 	1981-09-08 	1500 	1333
7698 	MARTIN 	1981-09-28 	1250 	1233
7698 	JAMES 	1981-12-03 	950 	1100

7782 	MILLER 	1982-01-23 	1300 	1300
7788 	ADAMS 	1987-07-13 	1100 	1100
7839 	JONES 	1981-04-02 	2975 	2913
7839 	BLAKE 	1981-05-01 	2850 	2758
7839 	CLARK 	1981-06-09 	2450 	2650
7902 	SMITH 	1980-12-17 	800 	800
	KING 	1981-11-17 	5000 	5000

 

 

 

COUNT 함수

SELECT 	ENAME, SAL, 
	COUNT(*) OVER (
    		ORDER BY SAL
		RANGE BETWEEN 50 PRECEDING AND 150 FOLLOWING) as SIM_CNT
FROM EMP;

RANGE BETWEEN 50 PRECEDING AND 150 FOLLOWING

현재 행의 급여값을 기준으로 급여가 -50 에서 +150 의 범위 내에 포함된 모든 행이 대상이 된다.

ENAME	SAL	SIM_CNT	(범위 값)
------	---	---	-------
SMITH 	800 	2 	( 750~ 950)
JAMES 	950 	2 	( 900~1100)
ADAMS 	1100 	3 	(1050~1250)
WARD 	1250 	3 	(1200~1400)
MARTIN 	1250 	3 	(1200~1400)
MILLER 	1300 	3 	(1250~1450)
TURNER 	1500 	2 	(1450~1650)
ALLEN 	1600 	1 	(1550~1750)
CLARK 	2450 	1 	(2400~2600)
BLAKE 	2850 	4 	(2800~3000)
JONES 	2975 	3 	(2925~3125)
SCOTT 	3000 	3 	(2950~3100)
FORD 	3000 	3 	(2950~3100)
KING 	5000 	1 	(4950~5100)

 

 

 

FIRST_VALUE 함수

  • 파티션별 윈도우에서 가장 먼저 나온 값을 구한다.
  • SQL Server 에서는 지원하지 않는 함수이다.
  • MIN 함수를 활용하여 같은 결과를 얻을 수도 있다.
SELECT 	DEPTNO, ENAME, SAL, 
	FIRST_VALUE(ENAME)
	OVER (
    		PARTITION BY DEPTNO ORDER BY SAL DESC
		ROWS UNBOUNDED PRECEDING
        ) as DEPT_RICH
FROM EMP;

ROWS UNBOUNDED PRECEDING

현재 행을 기준으로 파티션 내의 첫 번째 행까지의 범위를 지정한다. (현재 행부터 파티션의 시작점까지 모든 행을 참조)

DEPTNO	ENAME	SAL	DEPT_RICH
------	------	---	---------
10 	KING 	5000 	KING
10 	CLARK 	2450 	KING
10 	MILLER 	1300 	KING
20 	SCOTT  	3000 	SCOTT
20 	FORD  	3000 	SCOTT
20 	JONES 	2975 	SCOTT
20 	ADAMS 	1100 	SCOTT
20 	SMITH 	800 	SCOTT
30 	BLAKE 	2850 	BLAKE
30 	ALLEN 	1600 	BLAKE
30 	TURNER 	1500 	BLAKE
30 	MARTIN 	1250 	BLAKE
30 	WARD 	1250 	BLAKE
30 	JAMES 	950 	BLAKE

 

 

 

LAST_VALUE 함수

  • 파티션별 윈도우에서 가장 나중에 나온 값을 구한다.
  • SQL Server 에서는 지원하지 않는 함수이다.
  • MAX 함수를 활용하여 같은 결과를 얻을 수도 있다.
SELECT 	DEPTNO, ENAME, SAL,
	LAST_VALUE(ENAME) OVER (
    		PARTITION BY DEPTNO ORDER BY SAL DESC
		ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) as DEPT_POOR
FROM EMP;

ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING

현재 행을 포함해서 파티션 내의 마지막 행까지의 범위를 지정한다.

DEPTNO	ENAME	SAL	DEPT_POOR
------	------	---	----------
10 	KING 	5000 	MILLER
10 	CLARK 	2450 	MILLER
10 	MILLER 	1300 	MILLER
20 	SCOTT 	3000 	SMITH
20 	FORD 	3000 	SMITH
20 	JONES 	2975 	SMITH
20 	ADAMS 	1100 	SMITH
20 	SMITH 	800 	SMITH
30 	BLAKE 	2850 	JAMES
30 	ALLEN 	1600 	JAMES
30 	TURKER 	1500 	JAMES
30 	MARTIN 	1250 	JAMES
30 	WARD 	1250 	JAMES
30 	JAMES 	950 	JAMES

 

 

 

LAG / LEAD함수

  • 파티션별 윈도우에서 이전/이후 몇 번째 행의 값을 가져올 수 있다.
  • SQL Server 에서는 지원하지 않는 함수이다.

예제 - 1

SELECT 	ENAME, HIREDATE, SAL, 
	LAG(SAL) OVER (ORDER BY HIREDATE) as PREV_SAL
FROM EMP
WHERE JOB = 'SALESMAN' ;
ENAME	HIREDATE	SAL	MGR_MIN
-----	--------	---	---------
ALLEN 	1981-02-20 	1600
WARD 	1981-02-22 	1250 	1600
TURNER 	1981-09-08 	1500 	1250
MARTIN 	1981-09-28 	1250 	1500

 

예제 - 2

SELECT 	ENAME, HIREDATE, SAL,
	LAG(SAL, 2, 0) OVER (ORDER BY HIREDATE) as PREV_SAL
FROM EMP
WHERE JOB = 'SALESMAN'

LAG(SAL, 2, 0)

두 행 앞의 SALARY를 가져오고 가져올 값이 없는 경우는 0으로 처리한다.

ENAME	HIREDATE	SAL	PREV_SAL
-----	--------	---	--------
ALLEN 	1981-02-20 	1600 	0
WARD 	1981-02-22 	1250 	0
TURNER 	1981-09-08 	1500 	1600
MARTIN 	1981-09-28 	1250 	1250

 

예제 - 3

SELECT 	ENAME, HIREDATE,
	LEAD (HIREDATE, 1) OVER (ORDER BY HIREDATE) as "NEXTHIRED"
FROM EMP;
ENAME	HIREDATE	NEXTDATE
-----	--------	--------
ALLEN 	1981-02-20 	1981-02-22
WARD 	1981-02-22 	1981-04-02
TURNER 	1981-09-08 	1981-09-28
MARTIN 	1981-09-28

 

 

 

 

< 그룹 내 비율 함수 >

 

RATIO_TO_REPORT 함수

  • 파티션 내 전체 SUM(칼럼)값에 대한 행별 칼럼 값의 백분율을 소수점으로 구할 수 있다.
  • 결과 값은 >0 & <=1 의 범위를 가진다.
  • 그리고 개별 RATIO 의 합을 구하면 1이 된다.
  • SQL Server 에서는 지원하지 않는 함수이다.
SELECT 	ENAME, SAL, 
	ROUND(RATIO_TO_REPORT(SAL) OVER (), 2) as R_R
FROM EMP
WHERE JOB = 'SALESMAN';
ENAME	SAL	R_R
-----	---	----
ALLEN 	1600 	0.29 (1600 / 5600)
WARD 	1250 	0.22 (1250 / 5600)
TURNER 	1250 	0.22 (1250 / 5600)
MARTIN 	1500 	0.27 ( 500 / 5600)

 

 

 

 

PERCENT_RANK 함수

  • 파티션별 윈도우에서 제일 먼저 나오는 것을 0으로, 제일 늦게 나오는 것을 1로 하여, 값이 아닌 행의 순서별 백분율을 구한다.
  • 결과 값은 >=0 & <=1 의 범위를 가진다.
  • SQL Server 에서는 지원하지 않는 함수이다.
SELECT 	DEPTNO, ENAME, SAL,
	PERCENT_RANK() OVER (
    		PARTITION BY DEPTNO ORDER BY SAL DESC
	) as P_R
FROM EMP;
DEPTNO	ENAME	SAL	P_R
------	-----	---	---
10 	KING 	5000 	0
10 	CLARK 	2450 	0.5
10 	MILLER 	1300 	1
20 	SCOTT 	3000 	0
20 	FORD 	3000 	0
20 	JONES 	2975 	0.5
20 	ADAMS 	1100 	0.75
20 	SMITH 	800 	1
30 	BLAKE 	2850 	0
30 	ALLEN 	1600 	0.2
30 	TURKER 	1500 	0.4
30 	MARTIN 	1250 	0.6
30 	WARD 	1250 	0.6
30 	JAMES 	950 	1

 

 

 

 

 

CUME_DIST 함수

  • 파티션별 윈도우의 전체건수에서 현재 행보다 작거나 같은 건 수에 대한 누적백분율을 구한다.
  • 결과 값은 >0 & <=1의 범위를 가진다.
  • SQL Server 에서는 지원하지 않는 함수이다.
SELECT 	DEPTNO, ENAME, SAL,
	CUME_DIST() OVER (
    		PARTITION BY DEPTNO ORDER BY SAL DESC
	) as CUME_DIST
FROM EMP;
DEPTNO	ENAME	SAL	CUME_DIST
------	-----	---	----------
10 	KING 	5000 	0.3333
10 	CLARK 	2450 	0.6667
10 	MILLER 	1300 	1.0000

20 	SCOTT 	3000 	0.4000
20 	FORD 	3000 	0.4000
20 	JONES 	2975 	0.6000
20 	ADAMS 	1100 	0.8000
20 	SMITH 	800 	1.0000

30 	BLAKE 	2850 	0.1667
30 	ALLEN 	1600 	0.3333
30 	TURKER 	1500 	0.5000
30 	MARTIN 	1250 	0.8333
30 	WARD 	1250 	0.8333
30 	JAMES 	950 	1.0000

 

 

 

 

NTILE 함수

  • 파티션별 전체 건수를 ARGUMENT 값으로 N등분한 결과를 구할 수 있다.
SELECT 	ENAME, SAL, 
	NTILE(4) OVER (ORDER BY SAL DESC) as QUAR_TILE
FROM EMP
DEPTNO	ENAME	SAL	QUAR_TILE
------	-----	---	----------
10 	KING 	5000 	1
10 	FORD 	3000 	1
10 	SCOTT 	3000 	1
20 	JONES 	2975 	1

20 	BLAKE 	2850 	2
20 	CLARK 	2450 	2
20 	ALLEN 	1600 	2
20 	TURNER 	1500 	2

30 	MILLER 	1300 	3
30 	WARD 	1250 	3
30 	MARTIN 	1250 	3

30 	ADAMS 	1100 	4
30 	JAMES 	950 	4
30 	SMITH 	800 	4

'DataBase > SQLD' 카테고리의 다른 글

옵티마이저(Optimizer)  (1) 2024.04.28
절차형 SQL  (0) 2024.04.27
그룹함수(Group Function)  (0) 2024.04.26
서브쿼리(Subquery)  (2) 2024.04.26
계층형 질의(Hierarchical Query) - Oracle  (0) 2024.04.26

+ Recent posts

티스토리툴바