SQL의 윈도우 함수란 행과 행 간을 비교, 연산, 정의하기 위한 함수이다. 분석함수 또는 순위함수라고 하기도 한다. 다른 함수들처럼 중첩해서 사용할 수는 없지만 서브쿼리에서는 사용가능하다.
WINDOW FUNCTION 기본 문법
윈도우 함수에는 OVER 문구가 필수로 들어간다.
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 지원하지 않음 |
| 그룹 내 행 순서 함수 | FIRST_VALUE, LAST_VALUE, LAG, LEAD | SQL서버에서는 지원하지 않음 |
| 그룹 내 비율 함수 | RATIO_TO_REPROT, PERCENT_RANK, CUME_DIST, NTILE | NTILE 제외 SQL서버에서는 지원하지 않음 |
1. 순위 함수
1) RANK
ORDER BY를 포함한 쿼리문에서 특정 컬럼의 순위를 구하는 함수이다. PARTITION 내에서 순위를 구할 수도 있고 전체 데이터에 대한 순위를 구할 수도 있다. 동일한 값에 대해서는 같은 순위를 부여하며 중간 순위를 비운다.
예를 들어 동일한 값이 있는 경우 순위는 1,1,3,4,4,6 이런식인 셈
SELECT JOB, ENAME, SAL,
RANK() OVER (PARTITION BY JOB ORDER BY SAL DESC) JOB_RANK
FROM EMP;
JOB ENAME SAL 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
RANK와 작동법은 동일, 동일한 값에 대해서는 같은 순위를 부여하고 중간 순위를 비우지 않는다. 동일한 값이 있는 경우 순위는 1,1,2,3,3,4 이런 식
SELECT JOB, ENAME, SAL,
DENSE_RANK() OVER (PARTITION BY JOB ORDER BY SAL DESC) JOB_RANK
FROM EMP;
JOB ENAME SAL 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
RANK, DENSE_RANK는 동일한 값에 대해 동일 순위를 부여하지만 ROW_NUMBER은 동일한 값이어도 고유한 순위를 부여한다.
SELECT JOB, ENAME, SAL,
ROW_NUMBER() OVER (PARTITION BY JOB ORDER BY SAL DESC) JOB_RANK
FROM EMP;
JOB ENAME SAL 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
2. 일반 집계 함수
SUM: 합계
MAX: 최대값
MIN: 최소값
AVG: 평균
COUNT: 조건에 해당하는 컬럼 숫자 출력
3. 그룹 내 행 순서 함수
1) FIRST_VALUE
파티션별 윈도우에서 가장 먼저 나온 값을 구한다. 공동 등수를 인정하지 않고 처음 나온 행만 가져오며 MIN함수를 쓰는 것과 결과가 동일하다.
2) LAST_VALUE
파티션별 윈도우에서 가장 마지막에 나온 값을 구한다. 공동 등수를 인정하지 않고 마지막에 나온 행만 가져오며 MAX함수를 쓰는 것과 결과가 동일하다.
3) LAG
이전 몇 번째 행의 값을 가져오는 함수이다. 인자를 최대 3개까지 가진다.
두번째 인자는 몇 번째 앞의 행을 가져올지 결정하는 것이며 DEFAULT값은 1이다. 세번째 인자는 가져올 행이 없을 경우 DEFAULT값을 지정해주는 것으로 NVL이나 ISNULL함수의 기능과 동일하다.
SELECT ENAME, HIREDATE, SAL
, LAG(SAL) OVER (ORDER BY HIREDATE) as PREV_SAL
FROM EMP
WHERE JOB = 'SALESMAN';
ENAME HIREDATE SAL PREV_SAL
---------- --------- ---------- ----------
ALLEN 20-FEB-81 1600
WARD 22-FEB-81 1250 1600
TURNER 08-SEP-81 1500 1250
MARTIN 28-SEP-81 1250 1500
--HIREDATE를 기준으로 정렬하고 본인보다 입사일자가 하나 더 앞선 사원의 급여를 출력
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 20-FEB-81 1600 0
WARD 22-FEB-81 1250 0
TURNER 08-SEP-81 1500 1600
MARTIN 28-SEP-81 1250 1250
--HIREDATE를 기준으로 정렬하고 본인보다 입사일자가 두 개 더 앞선 사원의 급여를 출력
--두 개 더 앞선 사원이 없을 경우 0을 출력
4) LEAD
이후 몇 번째 행의 값을 가져오는 함수로 LAG와 마찬가지로 인자를 최대 3개까지 갖는다.
SELECT ENAME, HIREDATE
, LEAD(HIREDATE, 1) OVER (ORDER BY HIREDATE) as "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
--HIREDATE를 기준으로 정렬하고 본인보다 HIREDATE가 하나 더 뒤인 날짜를 출력
--없는 경우 NULL
4. 그룹 내 비율 함수
1) RATIO_TO_REPORT
파티션 내 전체 SUM값에 대한 행별 컬럼 값의 백분율을 소수점으로 출력한다. 결과값은 0~1 사이이며 개별 비율의 합을 구하면 1이다.
SELECT ENAME, SAL
, ROUND(RATIO_TO_REPORT(SAL) OVER (), 2) as R_R
FROM EMP
WHERE JOB = 'SALESMAN';
ENAME SAL R_R
---------- ---------- ----------
ALLEN 1600 .29
WARD 1250 .22
MARTIN 1250 .22
TURNER 1500 .27
--전체 급여에서 각각이 차지하는 비율 출력
2) PERCENT_RANK
파티션별로 가장 먼저 나오는 값을 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 .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
--DEPTNO를 기준으로 파티셔닝해서 순서별 백분율 출력
3) CUME_DIST
파티션별 전체건수에서 현재 행보다 작거나 같은 건수에 대한 누적백분율을구한다.
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 .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
--DEPTNO를 기준으로 파티셔닝해서 누적 백분율 출력
4) NTILE
파티션별 전체 건수를 ARGUMENT값으로 N등분한 결과를 출력한다.
SELECT ENAME, SAL
, NTILE(4) OVER (ORDER BY SAL DESC) as QUAR_TILE
FROM EMP ;
ENAME SAL 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
--전체건수를 ARGUMENT값(여기서는 4)으로 N등분한 결과 출력
--14를 4로 나누면 몫이 3 나머지가 2, 나머지2는 앞의 조부터 할당되기 때문에 4,4,3,3으로 나눔'SQL' 카테고리의 다른 글
| SQL 옵티마이저와 인덱스 기본 (0) | 2021.09.02 |
|---|---|
| 외부 조인) LEFT JOIN, RIGHT JOIN, FULL JOIN 차이 알아보기 (1) | 2021.08.18 |
| 오라클 DELETE, TRUNCATE, DROP 차이점 알아보기 (0) | 2021.08.15 |
| SQL CREATE TABLE로 테이블 생성하기 및 PK / FK 등 제약 조건 알아보기 (0) | 2021.08.14 |
| SQL 집계함수 - ROLLUP, CUBE, GROUPING SETS (3) | 2021.08.12 |
