윈도우 함수(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
