윈도우 함수(WINDOW FUNCTION)
- WINDOW FUNCTION 종류
구분 종류 종류 순위(RANK) 관련 RANK, DENSE_RANK, ROW_NUMBER 대부분 지원 집계(AGGREGATE) 관련 SUM, MAX, MIN, AVG, COUNT SQL Server 경우 Over절 내 Orderby 지원 못함 순서 관련 함수 FIRST_VALUE, LAST_VALUE, LAG, LEAD ORACLE 만 지원 그룹 내 비율 관련 함수 CUME_DIST, PERCENT_RANK, NTILE, RATIO_TO_REPORT PERCENT_RANK 함수는 ANSI/ISO SQL 표준과 Oracle DBMS에서 지원하고 있으며, NTILE 함수는 ANSI/ISO SQL 표준에는 없지만, Oracle, SQL Server에서 지원하고 있다. RATIO_TO_REPORT 함수는 Oracle에서만 지원되는 함수(현업에서 유용). 선형분석을 포함한 통계분석 함수 CORR, COVAR_POP, COVAR_SAMP, STDDEV, STDDEV_POP, STDDEV_SAMP, VARIANCE, VAR_POP, VAR_SAMP, REGR_(LINEAR REGRESSION), REGR_SLOPE, REGR_INTERCEPT, REGR_COUNT, REGR_R2, REGR_AVGX, REGR_AVGY, REGR_SXX, REGR_SYY, REGR_SXY 특화되어있으므로 생략
그룹 내 순위함수.
3.1 RANK 함수
- RANK 함수는 ORDER BY를 포함한 QUERY 문에서 특정 항목(칼럼)에 대한 순위를 구하는 함수이다.
- 이때 특정 범위(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 14 rows selected. ------------------------------------------------------------------------------------ | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ------------------------------------------------------------------------------------ | 0 | SELECT STATEMENT | | 14 | 364 | 5 (40)| 00:00:01 | | 1 | WINDOW SORT | | 14 | 364 | 5 (40)| 00:00:01 | | 2 | WINDOW SORT | | 14 | 364 | 5 (40)| 00:00:01 | | 3 | TABLE ACCESS STORAGE FULL| EMP | 14 | 364 | 3 (0)| 00:00:01 | ------------------------------------------------------------------------------------ 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 1100 2 CLERK JAMES 950 3 CLERK SMITH 800 4 MANAGER JONES 2975 1 MANAGER BLAKE 2850 2 MANAGER CLARK 2450 3 PRESIDENT KING 5000 1 SALESMAN ALLEN 1600 1 SALESMAN TURNER 1500 2 SALESMAN MARTIN 1250 3 SALESMAN WARD 1250 3 14 rows selected.
3.2 DENSE_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 14 rows selected.
3.3 ROW_NUMBER 함수
- 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 14 rows selected.
일반 집계 함수
3.4 SUM 함수
- SUM 함수를 이용해 파티션별 윈도우의 합을 구할 수 있다.
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 14 rows selected. 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 5000 14 rows selected. (SQL Server의 경우 집계 함수의 경우 OVER 절 내의 ORDER BY 절을 지원하지 않는다.)
3.5 MAX 함수
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 14 rows selected. 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 8 rows selected.
3.6 MIN 함수
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 03-DEC-81 3000 3000 7566 SCOTT 09-DEC-82 3000 3000 7698 ALLEN 20-FEB-81 1600 1600 7698 WARD 22-FEB-81 1250 1250 7698 TURNER 08-SEP-81 1500 1250 7698 MARTIN 28-SEP-81 1250 1250 7698 JAMES 03-DEC-81 950 950 7782 MILLER 23-JAN-82 1300 1300 7788 ADAMS 12-JAN-83 1100 1100 7839 JONES 02-APR-81 2975 2975 7839 BLAKE 01-MAY-81 2850 2850 7839 CLARK 09-JUN-81 2450 2450 7902 SMITH 17-DEC-80 800 800 KING 17-NOV-81 5000 5000 14 rows selected.
3.7 AVG 함수
- EMP 테이블에서 같은 매니저를 두고 있는 사원들의 평균 SALARY를 구하는데,
- 조건은 같은 매니저 내에서 자기 바로 앞의 사번과 바로 뒤의 사번인 직원만을 대상으로 한다.
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 03-DEC-81 3000 3000 7566 SCOTT 09-DEC-82 3000 3000 7698 ALLEN 20-FEB-81 1600 1425 7698 WARD 22-FEB-81 1250 1450 7698 TURNER 08-SEP-81 1500 1333 7698 MARTIN 28-SEP-81 1250 1233 7698 JAMES 03-DEC-81 950 1100 7782 MILLER 23-JAN-82 1300 1300 7788 ADAMS 12-JAN-83 1100 1100 7839 JONES 02-APR-81 2975 2913 7839 BLAKE 01-MAY-81 2850 2758 7839 CLARK 09-JUN-81 2450 2650 7902 SMITH 17-DEC-80 800 800 KING 17-NOV-81 5000 5000
3.8 COUNT 함수
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 1 14 rows selected.
그룹 내 행 순서 함수
3.9 FIRST_VALUE 함수
- 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; 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 14 rows selected. 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 BLAKE 14 rows selected.
3.9 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 JAMES 14 rows selected.
3.9 LAG 함수
- LAG 함수를 이용해 파티션별 윈도우에서 이전 몇 번째 행의 값을 가져올 수 있다. SQL Server에서는 지원하지 않는 함수이다.
직원들을 입사일자가 빠른 기준으로 정렬을 하고, 본인보다 입사일자가 한 명 앞선 사원의 급여를 본인의 급여와 함께 출력한다. 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 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
- LAG 함수는 3개의 ARGUMENTS 까지 사용할 수 있는데,
- 두 번째 인자는 몇 번째 앞의 행을 가져올지 결정하는 것이고 (DEFAULT 1),
- 세 번째 인자는 예를 들어 파티션의 첫 번째 행의 경우 가져올 데이터가 없어 NULL 값이 들어오는데
- 이 경우 다른 값으로 바꾸어 줄 수 있다. 결과적으로 NVL이나 ISNULL 기능과 같다.
3.10 LEAD 함수
- LEAD 함수를 이용해 파티션별 윈도우에서 이후 몇 번째 행의 값을 가져올 수 있다.
- 참고로 SQL Server에서는 지원하지 않는 함수이다.
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 08-SEP-81 TURNER 08-SEP-81 28-SEP-81 MARTIN 28-SEP-81 17-NOV-81 KING 17-NOV-81 03-DEC-81 JAMES 03-DEC-81 03-DEC-81 FORD 03-DEC-81 23-JAN-82 MILLER 23-JAN-82 09-DEC-82 SCOTT 09-DEC-82 12-JAN-83 ADAMS 12-JAN-83 14 rows selected.
- LEAD 함수는 3개의 ARGUMENTS 까지 사용할 수 있는데,
- 두 번째 인자는 몇 번째 후의 행을 가져올지 결정하는 것이고 (DEFAULT 1),
- 세 번째 인자는 예를 들어 파티션의 마지막 행의 경우 가져올 데이터가 없어 NULL 값이 들어오는데
- 이 경우 다른 값으로 바꾸어 줄 수 있다. 결과적으로 NVL이나 ISNULL 기능과 같다
그룹 내 비율 함수
3.11 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 .29 WARD 1250 .22 MARTIN 1250 .22 TURNER 1500 .27
3.11 PERCENT_RANK 함수
- 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 .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 14 rows selected.
3.11 CUME_DIST 함수
- 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 .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 14 rows selected.
3.11 NTILE 함수
- 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 14 rows selected.
'ORACLE > SQL' 카테고리의 다른 글
| SQL trace _ 참조 (0) | 2019.10.02 |
|---|---|
| 복수행 함수 (그룹 함수) (0) | 2018.12.13 |
| PLSQL - 다차원 콜렉션 (0) | 2018.01.24 |
| 피벗 (행->열) (0) | 2017.12.22 |
| BULK COLLECT (0) | 2017.08.17 |