WINDOW FUNCTION
SELECT WINDOW_FUNCTION (ARGUMENTS) OVER
( [PARTITION BY 컬럼] [ORDER BY 컬럼] [WINDOWING 절] )
FROM 테이블명 ; WINDOW_FUNCTION: 윈도우 함수ARGUMENTS(인수): 함수에 따라 0 ~ N개 인수가 지정될 수 있음OVER: 필수 포함PARTITION BY 절: 전체 집합을 기준에 의해 소그룹으로 나눌 수 있음ORDER BY 절: 어떤 항목에 대해 순위를 지정할 지 order by 절을 기술함
WINDOWING 절: WINDOWING 절은 함수의 대상이 되는 행 기준의 범위를 강력하게 지정 (sql server 에서는 지원하지 않음)
그룹 내 순위(RANK) 관련 함수
RANK
순위를 구하는 함수로 특정 범위(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;- 하나의 문장에
ORDER BY SAL DESC와PARTITION BY JOB의 조건이 충돌 났기 때문에 JOB별로는 정렬되지 않고,ORDER BY SAL DESC조건으로 정렬됨
DENSE_RANK
RANK와 흡사하지만, 동일한 순위를 하나의 건수로 취급한다.
SELECT JOB, ENAME, SAL,
RANK() OVER (ORDER BY SAL DESC) ALL_RANK,
DENSE_RANK() OVER (ORDER BY SAL DESC) DENSE_RANK
FROM EMP; - RANK는 1, 2, 3순위로 표기하지만, DENSE_RANK는 1, 1, 3 순위를 부여
(1위가 두개 이니 2위 없이 3위로 넘어감)
ROW_NUMBER
RANK, DENSE_RANK가 동일한 값에 대해서는 동일한 순위를 부여하는데 반해, ROW_NUMBER 는 동일한 값이라도 고유한 순위를 부여한다.
ROW_NUMBER 는 동일한 순위를 배제하기 위해 유니크한 순위를 정한다.
SELECT JOB, ENAME, SAL,
RANK() OVER (ORDER BY SAL DESC) ALL_RANK,
ROW_NUMBER() OVER (ORDER BY SAL DESC) ROW_NUMBER
FROM EMP;- 같은 값에 대해서 어떤 결과가 먼저 나올지 순서가를 정하고 싶다면 ORDER BY를 같이 기재하면 됨
그룹 내 집계(AGGREGATE) 관련 함수
SUM
SUM 함수를 이용해서 파티션별 윈도우의 합을 구할 수 있다.
사원들의 급여와 같은 매니저를 두고 있는 사원들의 급여 합을 구하는 예제
SELECT MGR, ENAME, SAL,
SUM(SAL) OVER (PARTITION BY MGR) MGR_SUM
FROM EMP;- OVER 절에 ORDER BY 절을 추가해서 파티션 내 데이터를 정렬하고 이전 급여 데이터까지의 누적값을 출력하는 예제
SELECT MGR, ENAME, SAL,
SUM(SAL) OVER (PARTITION BY MGR ORDER BY SAL RANGE UNBOUNDED PRECEDING) MGR_SUM
FROM EMP;
RANGE UNBOUNDED PRECEDING ; -- 현재 행을 기준으로 파티션 내의 첫 번째 행까지 범위를 지정MAX
파티션별 윈도우의 최대값을 구할 수 있다.
사원들의 급여와 같은 매니저를 두고 있는 사원들의 급여 중 최대값을 구하는 예제
SELECT MGR, ENAME, SAL,
MAX(SAL) OVER (PARTITION BY MGR) MGR_MAX
FROM EMP;INLINE VIEW를 이용해서 파티션별 최대값을 가진 행만 추출할 수도 있다.
SELECT MGR, ENAME, SAL
FROM (SELECT MGR, ENAME, SAL,
MAX(SAL) OVER (PARTITION BY MGR) IV_MAX_SAL
FROM EMP)
WHERE SAL = IV_MAX_SAL;MIN
파티션별 윈도우의 최소값을 구할 수 있다.
사원들의 급여와 같은 매니저를 두고 있는 사원을 입사일자 기준으로 정렬하고, 급여 최소값을 같이 구하는 예제
SELECT MGR, ENAME, HIREDEAT, SAL,
MIN(SAL) OVER (PARTITION BY MGR ORDER BY HIREDATE) MGR_MIN
FROM EMP;AVG
파티션별 통계값을 구할 수 있다.
같은 매니저를 두고 있는 사원들의 평균 급여를 구하되, 같은 매니저 내에서 자기 바로 앞의 사번과 바로 뒤의 사번인 직원을 대상으로만 하는 예제 (앞 줄 + 나 + 뒷 줄의 합을 3으로 나누는 형식. 만약 앞줄이 없다면 나 + 뒷 줄의 합을 2로 나누게 된다)
SELECT MGR, ENAME, HIREDEAT, SAL,
ROUND (AGV(SAL) OVER (PARTITION BY MGR ORDER BY HIREDATE)
ROW BETWEEN 1 PRECEDING AND 1 FOLLOWING)) MGR_AVG
FROM EMP;COUNT
사원들을 급여 기준으로 정렬하고, 본인 급여보다 50 이하 적거나, 150 이하로 많은 급여를 받는 인원수를 출력하는 예제
SELECT ENAME, =SAL,
COUNT(*) OVER (ORDER BY SAL
RANGE BETWEEN 50 PRECEDING AND 150 FOLLOWING) SIM_CNT
FROM EMP;RANGE BETWEEN 50 PRECEDING AND 150 FOLLOWING;:
현재 행의 급여값을 기준으로 금여가 -50 ~ +150 범위 내에 포함된 모든 행이 대상이 됨
range는 현재 행의 데이터 값을 기준으로 앞 뒤 데이터 값의 범위를 표시하는 것
그룹 내 행 순서 관련 함수
FIRST_VALUE
파티션별 윈도우에서 가장 먼저 나온 값을 구할 수 있다.
- SQL Server 에서는 지원하지 않음
- MIN 함수를 이용해도 같은 결과를 얻을 수 있음
부서별 직원들을 연봉이 높은 순서부터 정렬하고, 파티션 내에서 가장 먼저 나온 값을 출력하는 예제
SELECT DEPTNO, ENAME, SAL,
FIRST_VALUE(ENAME) OVER (PARTITION BY DEPTNO ORDER BY SAL DESC
ROWS UNBOUNDED PRECEDING) DEPT_RICH
FROM EMP; - 같은 급여를 받는 사람이 있다면 정렬을 지정해야 함
- FIRST_VALUE는 공동 등수를 인정하지 않고 처음 나온 행을 처리하기 때문
정렬을 추가한 예제 (같은 급여가 있다면 이름 오름차순으로)
SELECT DEPTNO, ENAME, SAL,
FIRST_VALUE(ENAME) OVER (PARTITION BY DEPTNO ORDER BY SAL DESC, ENAME ASC
ROWS UNBOUNDED PRECEDING) DEPT_RICH
FROM EMP; LAST_VALUE
파티션별 윈도우에서 가장 먼저 나중에 나온 값을 구할 수 있다.
- sql server 에서는 지원하지 않음
- MAX 함수를 이용해도 같은 결과를 얻을 수 있음
부서별 직원들을 연봉이 높은 순서부터 정렬하고, 파티션 내에서 가장 먼저 나중에 나온 값을 출력하는 예제
SELECT DEPTNO, ENAME, SAL,
LAST_VALUE(ENAME) OVER (PARTITION BY DEPTNO ORDER BY SAL DESC
ROW BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) DEPT_POOR
FROM EMP; ROW BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
현재 행을 포함해서 파티션 내의 마지막 행까지의 범위를 지정
LAG
파티션별 윈도우에서 이전 몇 번째 행의 값을 가져올 수 있다.
- sql server 에서는 지원하지 않음
직원들을 입사일자가 빠른 기준으로 정렬하고, 본인보다 입사일자가 한 명 앞선 사원의 급여를 본이의 급여와 함께 출력하는 예제
SELECT ENAME, HIREDATE, SAL,
LAG(SAL) OVER (ORDER BY HIREDATE) 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
4개의 행이 선택되었다.- LAG 함수는 3개의 인수까지 사용할 수 있음
LAG(SAL, 2, 0):- 두번째 인수는 몇 번째 앞의 행을 가져올지 결정하는 것이고 디폴트는 1.
(여기서는 2을 지정했으니까 2번째 앞에 있는 행을 가져오는 것) - 세번째 인수는 파티셧 첫 번째 행의 경우 가져올 데이터가 없어 NULL 값이 들어오는데, 이 경우 다른 값으로 바꾸어줄 수 있음 (NVL/ ISNULL과 같은 기능)
- 두번째 인수는 몇 번째 앞의 행을 가져올지 결정하는 것이고 디폴트는 1.
LAG(SAL, 2, 0): 두 행 앞의 급여를 가져오고, 가져올 값이 없으면 0으로 처리.
SELECT ENAME, HIREDATE, SAL,
LAG(SAL,2,0) OVER (ORDER BY HIREDATE) 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 1250
4개의 행이 선택되었다.LEAD
파티션별 윈도우에서 이후 몇 번째 행의 값을 가져올 수 있다.
- sql server 에서는 지원하지 않음
직원들을 입사일자가 빠른 기준으로 정렬하고, 바로 다음에 입사한 인력의 입사일자를 함께 출력하는 예제
SELECT ENAME, HIREDATE,
LEAD(HIREDATE) OVER (ORDER BY HIREDATE) 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
4개의 행이 선택되었다.- LAG 처럼 LEAD 함수도 3개의 인수까지 사용할 수 있음
그룹 내 비율 관련 함수
CUME_DIST
파티션별 윈도우의 전체건수에서 현재 행보다 작거나 같은 건수에 대한 누적백분율을 구한다.
- sql server 에서는 지원하지 않음
같은 부서 소속 사원들의 집합에서 본인의 급여가 누적 순서상 몇 번재 위치쯤에 있는지 0~1 사이의 값으로 출력하는 예제
SELECT DEPTNO, ENAME, SAL,
CUME_DIST() OVER (PARTITION BY DEPTNO ORDER BY SAL DESC) CUME_DIST
FROM EMP ;PERCENT_RANK
파티션별 함수를 이용해서 파티션별 윈도우에서 제일 먼저 나오는 것을 0으로, 제일 늦게 나오는 것을 1로 하여, 행의 순서별 백분율을 구한다. (값이 아니라 행의 순서별 백분율)
- sql server 에서는 지원하지 않음
같은 부서 소속 사원들의 집합에서 본인의 급여가 순서상 몇 번째 위치해 있는지 0과 1 사이의 값으로 출력하는 예제
SELECT ENAME, SAL,
PERCENT_RANK() OVER (PARTITION BY DEPTNO ORDER BY SAL DESC) 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
14개의 행이 선택되었다.- DEPTNO 10의 경우, 3건 이므로 구간은 2.
0과 1 사이를 2개의 구간으로 나누면 0, 0.5, 1이 됨 - DEPTNO 20의 경우 5건 이고, 구간은 4.
0과 1 사이를 4개 구간으로 나누면 0, 0.25, 0.5, 0.75, 1이 됨 - DEPTNO 30의 경우 6건 이고, 구간은 5.
0과 1 사이를 5개 구간으로 나누면 0, 0.2, 0.4, 0.6, 0.8, 1이 됨
NTILE
파티션별 전체 건수를 ARGUMENT 값으로 N등분한 결과를 구할 수 있다.
전체 사원을 급여가 높은 순서로 정렬하고, 급여를 기준으로 4개 그룹으로 분류한다.
SELECT ENAME, SAL,
NTILE(4) OVER (ORDER BY SAL DESC) QUAR_TILE
FROM EMP ;
[실행 결과]
DEPTNO ENAME SAL QUAR_TILE
------ ------- ---- --------
10 KING 5000 1
10 FORD 3000 1
10 SCOT 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
14개의 행이 선택되었다.NTILE(4)의 의미는 14명의 팀원을 4개 조로 나눈다는 의미- 14명을 4개의 집합으로 나누면 몫이 3, 나머지가 2
- 나머지 두 명의 앞의 조부터 할당됨
4명 + 4명 + 3명 + 3명 으로 조를 나누게 됨
RATIO_TO_REPORT
파티션 내 전체 SUM(컬럼) 값에 대한 행별 컬럼 값의 백분율을 소수점으로 구할 수 있다.
- 결과값은 > 0 & <= 1 의 범위를 가짐
- 개별 ratio 의 합을 구하면 1이 됨
- sql server 에서는 지원하지 않음
세일즈맨 대상으로, 전체 급여에서 본인이 차지하는 비율을 구하는 예제
SELECT ENAME, SAL,
ROUND (RATIO_TO_REPORT(SAL) OVER (), 2) P_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)
4개의 행이 선택되었다.