WINDOW FUNCTION 개요
기존 관계형 데이터베이스는 칼럼 간의 연산, 비교, 연결, 집합에 대한 집계는 쉽게 처리할 수 있었지만, 행 간의 관계를 정의하거나 비교, 연산하는 것은 어려웠다. 이를 해결하기 위해 PL/SQL, SQL/PL, T-SQL, PRO*C 같은 절차형 프로그램을 작성하거나 복잡한 SQL 문을 사용해야 했다. 이러한 문제를 해결하기 위해 도입된 함수가 바로 WINDOW FUNCTION이다.
WINDOW FUNCTION은 행 간의 관계를 쉽게 정의할 수 있도록 만들어진 함수로, 복잡한 프로그램을 하나의 SQL 문으로 간단하게 해결할 수 있게 한다. 분석 함수(Analytic Function)나 순위 함수(Rank Function)로도 알려져 있으며, 데이터웨어하우스에서 발전한 기능이다. 이 함수는 ANSI/ISO SQL 표준에서 WINDOW FUNCTION이라는 용어를 사용하며, SQL의 중요한 기능 중 하나로 평가받고 있다.
WINDOW FUNCTION 종류
WINDOW FUNCTION은 크게 다섯 그룹으로 분류된다. 하지만 다섯 번 째 함수는 통계에 특화되있으므로, 여기서 다루지 않는다.
- 순위 관련 함수 (RANK Functions)
- RANK, DENSE_RANK, ROW_NUMBER: 대부분의 DBMS에서 지원된다.
- 행의 순위를 매기거나 순번을 부여하는 데 사용된다.
- 집계 함수 (AGGREGATE Functions)
- SUM, MAX, MIN, AVG, COUNT: 기존의 집계 함수들이 포함되며, 대부분의 DBMS에서 지원된다.
- SQL Server는 집계 함수에서 ORDER BY 구문을 지원하지 않는다.
- 행 순서 관련 함수 (Order-Related Functions)
- FIRST_VALUE, LAST_VALUE, LAG, LEAD: Oracle에서 주로 지원되며, 행의 순서에 따라 값을 가져오는 데 유용하다.
- 비율 관련 함수 (Ratio Functions)
- CUME_DIST, PERCENT_RANK, NTILE, RATIO_TO_REPORT: ANSI/ISO SQL 표준과 일부 DBMS에서 지원된다. 데이터의 비율이나 백분위를 계산하는 데 사용된다.
통계 분석 함수 (Statistical Functions)CORR,COVAR_POP,COVAR_SAMP,STDDEV,STDDEV_POP,STDDEV_SAMP,VARIANCE,VAR_POP,VAR_SAMP,REGR_*: 통계 분석에 특화된 함수들로, 데이터의 분산, 표준 편차, 회귀 분석 등을 수행한다.
WINDOW FUNCTION SYNTAX
WINDOW FUNCTION의 문법은 다음과 같다.
SELECT WINDOW_FUNCTION(ARGUMENTS) OVER (
[PARTITION BY 칼럼]
[ORDER BY 절]
[WINDOWING 절]
)
FROM 테이블 명;- WINDOW_FUNCTION: 기존의 집계 함수 또는 WINDOW 함수 전용 함수가 올 수 있다.
- ARGUMENTS: 함수에 따라 0 ~ N개의 인수가 지정될 수 있다.
- PARTITION BY 절: 집합을 기준에 따라 소그룹으로 나눈다.
- ORDER BY 절: 순위를 지정할 항목을 기술한다.
- WINDOWING 절: 함수의 대상이 되는 행 기준의 범위를 지정한다. ROWS는 물리적인 결과 행의 수를, RANGE는 논리적인 값에 의한 범위를 나타낸다. SQL Server에서는 WINDOWING 절을 지원하지 않는다.
WINDOWING 절의 BETWEEN 사용 타입
- ROWS | RANGE BETWEEN:
- UNBOUNDED PRECEDING: 처음 행부터
- CURRENT ROW: 현재 행까지
- VALUE_EXPR PRECEDING/FOLLOWING: 지정된 값 이전/이후 행까지
- UNBOUNDED FOLLOWING: 마지막 행까지
WINDOWING 절의 BETWEEN 미사용 타입
- ROWS | RANGE:
- UNBOUNDED PRECEDING: 처음 행부터
- CURRENT ROW: 현재 행까지
- VALUE_EXPR PRECEDING: 지정된 값 이전 행까지
그룹 내 순위 함수
1️⃣ RANK 함수
RANK 함수는 특정 항목에 대해 순위를 구하는 함수이다. ORDER BY 절을 통해 지정한 순서에 따라 순위를 매기며, PARTITION BY 절을 통해 특정 그룹 내에서만 순위를 매길 수도 있다. 동일한 값에 대해서는 동일한 순위를 부여한다.
1. 예제
사원 데이터에서 급여가 높은 순서와 직무별로 급여가 높은 순서를 같이 출력하는 예제이다.
SELECT
JOB,
ENAME,
SAL,
RANK() OVER (ORDER BY SAL DESC) AS ALL_RANK,
RANK() OVER (PARTITION BY JOB ORDER BY SAL DESC) AS 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 4FORD와 SCOTT, WARD와 MARTIN은 동일한 급여이므로 동일한 순위를 부여받는다. PARTITION BY 절을 사용해 JOB 별로 급여 순위를 구할 수 있다.
2️⃣ DENSE_RANK 함수
DENSE_RANK 함수는 RANK 함수와 유사하지만, 동일한 순위를 하나의 건수로 취급하여 다음 순위가 건너뛰지 않는다.
예제
사원 데이터에서 급여가 높은 순서와, 동일한 순위를 하나의 등수로 간주한 결과를 같이 출력하는 예제이다.
SELECT
JOB,
ENAME,
SAL,
RANK() OVER (ORDER BY SAL DESC) AS RANK,
DENSE_RANK() OVER (ORDER BY SAL DESC) AS 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 12FORD와 SCOTT, WARD와 MARTIN은 동일한 급여이므로 동일한 순위를 부여받지만, DENSE_RANK는 건너뛰지 않고 순위를 매긴다. 예를 들어, FORD와 SCOTT 다음 순위는 RANK 함수에서는 4등이지만, DENSE_RANK 함수에서는 3등이다.
3️⃣ ROW_NUMBER 함수
ROW_NUMBER 함수는 동일한 값이라도 고유한 순위를 부여한다.
예제
사원 데이터에서 급여가 높은 순서와, 동일한 순위를 인정하지 않는 순위를 같이 출력하는 예제이다.
SELECT
JOB,
ENAME,
SAL,
RANK() OVER (ORDER BY SAL DESC) AS RANK,
ROW_NUMBER() OVER (ORDER BY SAL DESC) AS 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 14FORD와 SCOTT, WARD와 MARTIN은 동일한 급여이므로 RANK에서는 동일한 순위를 부여하지만, ROW_NUMBER에서는 고유한 순위를 부여한다. 동일한 값을 가진 행의 순서는 고유한 순위를 부여하기 위해 추가적인 ORDER BY 조건을 지정할 수도 있다.
일반 집계 함수
집계 함수는 SQL에서 데이터를 그룹화하여 요약된 정보를 제공하는 데 사용된다. 여기서는 대표적인 집계 함수인 SUM, MAX, MIN, AVG, COUNT에 대해 설명한다.
1️⃣ SUM 함수
SUM 함수는 숫자 데이터의 합을 계산하는 함수이다. PARTITION BY 절을 사용하면 특정 그룹 내에서 합계를 계산할 수 있다.
예제1
사원들의 급여와 같은 매니저를 두고 있는 사원들의 급여 합을 구하는 쿼리이다.
SELECT
MGR,
ENAME,
SAL,
SUM(SAL) OVER (PARTITION BY MGR) AS 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예제2
ORDER BY 절을 추가하여 파티션 내 데이터를 정렬하고 이전 급여 데이터까지의 누적값을 출력하는 쿼리이다.
SELECT
MGR,
ENAME,
SAL,
SUM(SAL) OVER (PARTITION BY MGR ORDER BY SAL RANGE UNBOUNDED PRECEDING) AS MGR_SUM
FROM EMP;-- 실행 결과
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 50002️⃣ MAX 함수
MAX 함수는 특정 그룹 내에서 최대값을 구하는 함수이다.
예제1
사원들의 급여와 같은 매니저를 두고 있는 사원들의 급여 중 최대값을 구하는 쿼리이다.
SELECT
MGR,
ENAME,
SAL,
MAX(SAL) OVER (PARTITION BY MGR) AS MGR_MAX
FROM EMP;-- 실행 결과
MGR ENAME SAL MGR_MAX
---- ------ ---- -------
7566 FORD 3000 3000
7566 SCOTT 3000 3000
7698 JAMES 950 1600
7698 ALLEN 1600 1600
7698 WARD 1250 1600
7698 TURNER 1500 1600
7698 MARTIN 1250 1600
7782 MILLER 1300 1300
7788 ADAMS 1100 1100
7839 BLAKE 2850 2975
7839 JONES 2975 2975
7839 CLARK 2450 2975
7902 SMITH 800 800
KING 5000 5000예제2
파티션별 최대값을 가진 행만 추출하는 쿼리이다.
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 50003️⃣ MIN 함수
MIN 함수는 특정 그룹 내에서 최소값을 구하는 함수이다.
예제1
SELECT
MGR,
ENAME,
HIREDATE,
SAL,
MIN(SAL) OVER (PARTITION BY MGR ORDER BY HIREDATE) AS MGR_MIN
FROM EMP;MGR ENAME HIREDATE SAL MGR_MIN
---- ------ ---------- ---- -------
7566 FORD 1981-12-03 3000 3000
7566 SCOTT 1987-07-13 3000 3000
7698 ALLEN 1981-02-20 1600 1600
7698 WARD 1981-02-22 1250 1250
7698 TURNER 1981-09-08 1500 1250
7698 MARTIN 1981-09-28 1250 1250
7698 JAMES 1981-12-03 950 950
7782 MILLER 1982-01-23 1300 1300
7788 ADAMS 1987-07-13 1100 1100
7839 JONES 1981-04-02 2975 2975
7839 BLAKE 1981-05-01 2850 2850
7839 CLARK 1981-06-09 2450 2450
7902 SMITH 1980-12-17 800 800
KING 1981-11-17 5000 50004️⃣ AVG 함수
AVG 함수는 특정 그룹 내에서 평균값을 구하는 함수이다. ROWS 윈도우를 이용하여 특정 조건에 맞는 데이터의 평균값을 계산할 수 있다.
예제
같은 매니저를 두고 있는 사원들의 평균 급여를 구하는데, 조건은 같은 매니저 내에서 자기 바로 앞의 사번과 바로 뒤의 사번인 직원만을 대상으로 하는 쿼리이다.
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;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 50005️⃣ COUNT 함수
COUNT 함수는 특정 그룹 내에서 행의 개수를 세는 함수이다. ROWS 윈도우를 이용하여 특정 조건에 맞는 데이터의 개수를 계산할 수 있다.
예제
사원들을 급여 기준으로 정렬하고, 본인의 급여보다 50 이하로 적거나 150 이하로 많은 급여를 받는 인원수를 출력하는 쿼리이다.
SELECT
ENAME,
SAL,
COUNT(*) OVER (ORDER BY SAL RANGE BETWEEN 50 PRECEDING AND 150 FOLLOWING) AS SIM_CNT
FROM EMP;ENAME SAL SIM_CNT
------ ---- -------
SMITH 800 2
JAMES 950 2
ADAMS 1100 3
WARD 1250 3
MARTIN 1250 3
MILLER 1300 3
TURNER 1500 2
ALLEN 1600 1
CLARK 2450 1
BLAKE 2850 4
JONES 2975 3
SCOTT 3000 3
FORD 3000 3
KING 5000 1ADAMS의 경우 자신의 급여 1100을 기준으로 -50에서 +150 범위 내에 해당하는 다른 직원의 급여인 JAMES(950), ADAMS(1100), WARD(1250) 총 3명의 데이터를 포함한다.
그룹 내 행 순서 함수
행 순서 함수는 파티션 내에서 특정 행의 값을 찾거나 행 간의 상대적인 위치를 기반으로 값을 검색하는 데 사용된다. 대표적인 함수로는 FIRST_VALUE, LAST_VALUE, LAG, LEAD 함수가 있다.
1️⃣ FIRST_VALUE 함수
FIRST_VALUE 함수는 파티션별 윈도우에서 가장 먼저 나온 값을 구하는 함수이다. 이 함수는 SQL Server에서는 지원되지 않는다. MIN 함수를 사용하여 유사한 결과를 얻을 수 있다.
예제1
부서별로 직원들을 연봉이 높은 순서로 정렬하고, 파티션 내에서 가장 먼저 나온 값을 출력하는 쿼리이다.
SELECT
DEPTNO,
ENAME,
SAL,
FIRST_VALUE(ENAME) OVER (PARTITION BY DEPTNO ORDER BY SAL DESC ROWS UNBOUNDED PRECEDING) AS DEPT_RICH
FROM EMP;-- 실행 결과
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예제2
동일한 값을 가진 FIRST_VALUE를 처리하기 위해 ORDER BY 정렬 조건을 추가한 쿼리이다.
SELECT
DEPTNO,
ENAME,
SAL,
FIRST_VALUE(ENAME) OVER (PARTITION BY DEPTNO ORDER BY SAL DESC, ENAME ASC ROWS UNBOUNDED PRECEDING) AS RICH_EMP
FROM EMP;DEPTNO ENAME SAL RICH_EMP
------ ------ ---- -------
10 KING 5000 KING
10 CLARK 2450 KING
10 MILLER 1300 KING
20 FORD 3000 FORD
20 SCOTT 3000 FORD
20 JONES 2975 FORD
20 ADAMS 1100 FORD
20 SMITH 800 FORD
30 BLAKE 2850 BLAKE
30 ALLEN 1600 BLAKE
30 TURNER 1500 BLAKE
30 MARTIN 1250 BLAKE
30 WARD 1250 BLAKE
30 JAMES 950 BLAKE2️⃣ LAST_VALUE 함수
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;-- 실행 결과
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 TURNER 1500 JAMES
30 MARTIN 1250 JAMES
30 WARD 1250 JAMES
30 JAMES 950 JAMES3️⃣ LAG 함수
LAG 함수는 파티션별 윈도우에서 이전 몇 번째 행의 값을 가져오는 함수이다. 이 함수도 SQL Server에서는 지원되지 않는다.
예제1
직원들을 입사일자가 빠른 순서로 정렬하고, 본인보다 입사일자가 한 명 앞선 사원의 급여를 본인의 급여와 함께 출력하는 쿼리이다.
SELECT
ENAME,
HIREDATE,
SAL,
LAG(SAL) OVER (ORDER BY HIREDATE) AS PREV_SAL
FROM EMP
WHERE JOB = 'SALESMAN';ENAME HIREDATE SAL PREV_SAL
------- ---------- ---- --------
ALLEN 1981-02-20 1600
WARD 1981-02-22 1250 1600
TURNER 1981-09-08 1500 1250
MARTIN 1981-09-28 1250 1500예제2
LAG 함수의 세 번째 인자를 사용하여 파티션의 첫 번째 행의 경우 NULL 값을 대체할 수 있는 쿼리이다.
SELECT
ENAME,
HIREDATE,
SAL,
LAG(SAL, 2, 0) OVER (ORDER BY HIREDATE) AS PREV_SAL
FROM EMP
WHERE JOB = 'SALESMAN';-- 실행 결과
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 12504️⃣ LEAD 함수
LEAD 함수는 파티션별 윈도우에서 이후 몇 번째 행의 값을 가져오는 함수이다. 이 함수도 SQL Server에서는 지원되지 않는다.
예제
직원들을 입사일자가 빠른 순서로 정렬하고, 바로 다음에 입사한 인력의 입사일자를 함께 출력하는 쿼리이다.
SELECT
ENAME,
HIREDATE,
LEAD(HIREDATE, 1) OVER (ORDER BY HIREDATE) AS NEXTHIRED
FROM EMP;-- 실행 결과
ENAME HIREDATE NEXTHIRED
------- ---------- ----------
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 LEAD 함수는 세 번째 인자를 사용하여 파티션의 마지막 행의 경우 NULL 값을 대체할 수 있다.
그룹 내 비율 함수
비율 함수는 데이터의 백분율을 계산하거나 순위별로 비율을 구하는 데 사용된다. 여기서는 대표적인 함수인 RATIO_TO_REPORT, PERCENT_RANK, CUME_DIST, NTILE에 대해 설명한다.
1️⃣ RATIO_TO_REPORT
RATIO_TO_REPORT 함수는 파티션 내 전체 SUM(칼럼) 값에 대한 행별 칼럼 값의 백분율을 소수점으로 구한다. 결과 값은 0보다 크고 1 이하의 범위를 가진다. 개별 RATIO의 합은 1이 된다. SQL Server에서는 지원되지 않는다.
예제
JOB이 SALESMAN인 사원들을 대상으로 전체 급여에서 본인이 차지하는 비율을 출력하는 쿼리이다.
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)
MARTIN 1250 0.22 (1250 / 5600)
TURNER 1500 0.27 (1500 / 5600)전체 급여는 1600 + 1250 + 1250 + 1500 = 5600이며, 각 사원의 급여 비율이 계산된다. 개별 RATIO의 합은 1이 된다.
아래도 같은 쿼리문이 된다.
SELECT
ENAME,
JOB,
SAL,
ROUND(RATIO_TO_REPORT(SAL) OVER (PARTITION BY JOB), 2) AS R_R
FROM EMP
WHERE JOB = 'SALESMAN';위도 같은 쿼리문이다!
2️⃣ PERCENT_RANK 함수
PERCENT_RANK 함수는 파티션별 윈도우에서 제일 먼저 나오는 것을 0으로, 제일 늦게 나오는 것을 1로 하여 값이 아닌 행의 순서별 백분율을 구한다. 결과 값은 0 이상 1 이하의 범위를 가진다. SQL Server에서는 지원되지 않는다.
예제
같은 부서 소속 사원들의 집합에서 본인의 급여가 순서상 몇 번째 위치에 있는지 0과 1 사이의 값으로 출력하는 쿼리이다.
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 TURNER 1500 0.4
30 MARTIN 1250 0.6
30 WARD 1250 0.6
30 JAMES 950 1각 부서 내의 사원들의 급여 순서에 따라 백분율을 계산한다. 동일한 급여를 가진 사원들은 같은 순위로 처리된다.
3️⃣ CUME_DIST 함수
CUME_DIST 함수는 파티션별 윈도우의 전체 건수에서 현재 행보다 작거나 같은 건수에 대한 누적 백분율을 구한다. 결과 값은 0보다 크고 1 이하의 범위를 가진다. SQL Server에서는 지원되지 않는다.
예제
같은 부서 소속 사원들의 집합에서 본인의 급여가 누적 순서상 몇 번째 위치에 있는지 0과 1 사이의 값으로 출력하는 쿼리이다.
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 TURNER 1500 0.5000
30 MARTIN 1250 0.8333
30 WARD 1250 0.8333
30 JAMES 950 1.0000각 부서 내의 사원들의 급여 순서에 따라 누적 백분율을 계산한다. 동일한 급여를 가진 사원들은 같은 순위로 처리된다.
4️⃣ NTILE 함수
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이 예제에서 NTILE(4)은 14명의 팀원을 4개 조로 나눕니다. 전체 14명을 4개의 그룹으로 나누면 3명씩 4개의 그룹이 된다. 이 경우 나머지 2명은 앞의 그룹부터 할당됩니다. 결과적으로 4명, 4명, 3명, 3명으로 나누게 된다.
