반응형
WINDOW 함수
: 행과 행간을 비교, 연산, 정의하기 위한 함수
WINDOW FUNCTION 기본 문법
SELECT WINDOW_FUNCTION(arguments) OVER (
[PARTITION BY 칼럼]
[ORDER BY 칼럼]
[WINDOWING 절]
)
FROM 테이블명;
WINDOW FUNCTION 종류
| 구분 | 함수 | 비교 |
| 순위 함수 | RANK, DENSE_RANK, ROW_NUMBER | |
| 일반 집계 함수 | SUM, MAX, MIN, AVG, COUNT | SQL서버에서는 OVER절 내 ORDER BY 지원X |
| 그룹 내 행 순서 함수 | FIRST_VALUE, LAST_VALUE, LAG, LEAD | SQL서버에서는 지원X |
| 그룹 내 비율 함수 | RATIO_TO_REPROT, PERCENT_RANK, CUME_DIST, NTILE | NTILE 제외 SQL서버에서는 지원X |
01 순위함수
1) RANK : 동일한 값에 대해서는 같은 순위 부여(중간 순위 비워둠)
SELECT JOB, ENAME, SALE, RANK() OVER (
PARTITION BY JOB
ORDER bY SAL DESC
) JOB_RANK
FROM EMP;
JOB ENAME SALE JOB_RANK
--------- ---------- ---------- ----------
ANALYST FORD 3000 1
ANALYST SCOTT 3000 1
CLERK MILLER 1300 1
CLERK ADAMS 1300 1
CLERK JAMES 950 3
CLERK SMITH 800 4
2) DENSE_RANK : 동일한 값에 대해 같은 순위 부여(중간 순위 비워두지 않음)
SELECT JOB, ENAME, SALE, DENSE_RANK() OVER (
PARTITION BY JOB
ORDER BY SALE DESC
) JOB_RANK
FROM EMP;
JOB ENAME SALE JOB_RANK
--------- ---------- ---------- ----------
ANALYST FORD 3000 1
ANALYST SCOTT 3000 1
CLERK MILLER 1300 1
CLERK ADAMS 1300 1
CLERK JAMES 950 2
CLERK SMITH 800 3
3) ROW_NUMBER : 동일 값이어도 고유한 순위 부여
SELECT JOB, ENAME, SALE, ROW_NUMBER() OVER (
PARTITION BY JOB
ORDER BY SALE DESC
) JOB_RANK
FROM EMP;
JOB ENAME SALE JOB_RANK
--------- ---------- ---------- ----------
ANALYST FORD 3000 1
ANALYST SCOTT 3000 2
CLERK MILLER 1300 1
CLERK ADAMS 1300 2
CLERK JAMES 950 3
CLERK SMITH 800 4
02 그룹 내 행 순서 함수
1) FIRST_VALUE : 파티션 별 윈도우에서 가장 먼저 나온 값(공동 등수 인정X), MIN함수와 동일
2) LAST_VALUE : 파티션 별 윈도우에서 가장 마지막에 나온 값(공동 등수 인정X), MAX함수와 동일
3) LAG : 이전 몇 번째 행의 값을 가져오는 함수.
LAG( 칼럼
, 가져올 n번째 앞의 행 : DEFAULT값은 1
, 가져올 행이 없을 경우 지정할 DEFAULT값)
/* HIREDATE 기준으로 정렬, 본인보다 입사일자가 하나 더 앞선 사원의 급여 출력 */
SELECT ENAME, HIREDATE, SALE, LAG(SALE) OVER (
ORDER BY HIREDATE
) PREV_SALE
FROM EMP
WHERE JOB = 'SALESMAN';
ENAME HIREDATE SALE PREV_SALE
---------- --------- ---------- ----------
ALLEN 20-FEB-81 1600
WARD 22-FEB-81 1250 1600
TURNER 08-SEP-81 1500 1250
MARTIN 28-SEP-81 1250 1500/* HIREDATE를 기준으로 정렬, 본인보다 입사일자가 두 개 더 앞선 사원의 급여 출력 */
/* 두 개 더 앞선 사원이 없을 경우 0 출력 */
SELECT ENAME, HIREDATE, SALE, LAG(SALE, 2, 0) OVER (
ORDER BY HIREDATE
) AS PREV_SALE
FROM EMP
WHERE JOB = 'SALESMAN';
ENAME HIREDATE SALE PREV_SALE
---------- --------- ---------- ----------
ALLEN 20-FEB-81 1600 0
WARD 22-FEB-81 1250 0
TURNER 08-SEP-81 1500 1600
MARTIN 28-SEP-81 1250 1250
4) LEAD : 이후 몇번째 행의 값을 가져오는 함수(LAG와 마찬가지로 인자 최대 3개 가짐)
/* HIREDATE 기준으로 정렬, 본인보다 HIREDATE가 하나 더 뒤인 날짜 출력)
/* 없는 경우 NULL */
SELECT ENAME, HIREDATE, LEAD(HIREDATE, 1) OVER (
ORDER BY HIREDATE
) NEXTHIRED
FROM EMP;
ENAME HIREDATE NEXTHIRED
---------- --------- ---------
SMITH 17-DEC-80 20-FEB-81
ALLEN 20-FEB-81 22-FEB-81
WARD 22-FEB-81 02-APR-81
JONES 02-APR-81 01-MAY-81
BLAKE 01-MAY-81 09-JUN-81
CLARK 09-JUN-81
03 그룹 내 비율 함수
1) RATIO_TO_REPORT : 파티션 내 전체 SUM에 대한 행별 칼럼 값의 백분율을 0~1사이값(소수점)으로 출력
/* 전체 급여에서 각각이 차지하는 비율 출력 */
SELECT ENAME, SALE, ROUND(RATIO_TO_REPORT(SAL) OVER(), 2) as R_R
FROM EMP
WHERE JOB = 'SALESMAN';
ENAME SALE R_R
---------- ---------- ----------
ALLEN 1600 .29
WARD 1250 .22
MARTIN 1250 .22
TURNER 1500 .27
2) PERCENT_RANK : 파티션 별로 가장 먼저 나오는 값을 0, 가장 마지막에 나오는 값을 1로 해서 행 순서 별 백분율 출력. 구간 나눠 백분율로 표시
/* DEPTNO를 기준으로 파티셔닝해서 순서 별 백분율 출력 */
SELECT DEPTNO, ENAME, SALE
, PERCENT_RANK() OVER (PARTITION BY DEPTNO ORDER BY SALE DESC) as P_R
FROM EMP;
DEPTNO ENAME SALE P_R
---------- ---------- ---------- ----------
10 KING 5000 0
10 CLARK 2450 .5
10 MILLER 1300 1
20 SCOTT 3000 0
20 FORD 3000 0
20 JONES 2975 .5
20 ADAMS 1100 .75
20 SMITH 800 1
30 BLAKE 2850 0
30 ALLEN 1600 .2
30 TURNER 1500 .4
30 MARTIN 1250 .6
30 WARD 1250 .6
30 JAMES 950 1
3) CUME_DIST : 파티션 별 전체 건수에서 현재 행보다 작거나 같은 건수에 대한 누적백분율 구한다.
/* DEPTNO 기준으로 파티셔닝해서 누적 백분율 출력 */
SELECT DEPTNO, ENAME, SALE
, CUME_DIST() OVER (PARTITION BY DEPTNO ORDER BY SALE DESC) as CUME_DIST
FROM EMP;
DEPTNO ENAME SALE CUME_DIST
---------- ---------- ---------- ----------
10 KING 5000 .333333333
10 CLARK 2450 .666666667
10 MILLER 1300 1
20 SCOTT 3000 .4
20 FORD 3000 .4
20 JONES 2975 .6
20 ADAMS 1100 .8
20 SMITH 800 1
30 BLAKE 2850 .166666667
30 ALLEN 1600 .333333333
30 TURNER 1500 .5
30 MARTIN 1250 .833333333
30 WARD 1250 .833333333
30 JAMES 950 1
4) NTILE : 파티션 별 전체 건수를 ARGUMENT값으로 N등분한 결과 출력; 등분하여 순위 매기기
/* 전체 건수를 ARGUMENT값(여기서는 4)으로 N등분한 결과 출력 */
/* 14를 4로 나누면 몫 3, 나머지 2, 나머지 2는 앞의 조부터 할당되기 때문에 4, 4, 3, 3으로 나눔 */
SELECT ENAME, SALE
, NTILE(4) OVER (ORDER BY SALE DESC) as QUAR_TILE
FROM EMP ;
ENAME SALE QUAR_TILE
---------- ---------- ----------
KING 5000 1
FORD 3000 1
SCOTT 3000 1
JONES 2975 1
BLAKE 2850 2
CLARK 2450 2
ALLEN 1600 2
TURNER 1500 2
MILLER 1300 3
WARD 1250 3
MARTIN 1250 3
ADAMS 1100 4
JAMES 950 4
SMITH 800 4반응형
