반응형
안녕하세요.
IT 업무를 하다 보면 리포트 성격의 Summary 정보를 요청하는 경우가 많습니다.
이럴 때 유용하게 사용되는 오라클 함수가 Analytic Function 즉, 분석함수입니다.함수 입니다.
ANSI/ISOSQL 표준은 WINDOW FUNCTION이라고 하며 Oracle 분석 함수는 행 그룹을 기반으로
집계 값을 계산하고 각 그룹에 대해 여러 행을 리턴합니다.
대표적으로 많이 사용하는 WINDOW 함수와 데이터베이스 별 지원현황은 다음과 같습니다.
| 구분 | 종류 | 비고 |
| 순위(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에서만 지원되는 함수(현업에서 유용). |
* OVER 문구가 키워드로 필수 포함된다.
SELECT WINDOW_FUNCTION (ARGUMENTS)
OVER ( [PARTITION BY 칼럼]] [ORDER BY 절] [WINDOWING 절] )
FROM 테이블 명;
BETWEEN 사용 타입
ROWS | RANGE BETWEEN UNBOUNDED PRECEDING | CURRENT ROW | VALUE_EXPR
PRECEDING/FOLLOWING AND UNBOUNDED FOLLOWING | CURRENT ROW |
VALUE_EXPR PRECEDING/FOLLOWING
BETWEEN 미사용 타입
ROWS | RANGE UNBOUNDED PRECEDING | CURRENT ROW | VALUE_EXPR PRECEDING* WINDOW_FUNCTION : 기존에 사용하던 함수도 있고, 새롭게 WINDOW 함수용으로 추가된 함수도 있다.
- ARGUMENTS (인수) : 함수에 따라 0 ~ N개의 인수가 지정될 수 있다.
* PARTITION BY 절 : 전체 집합을 기준에 의해 소그룹으로 나눌 수 있다.
* ORDER BY 절 : 어떤 항목에 대해 순위를 지정할 지 ORDER BY 절을 기술한다.
* WINDOWING 절 : WINDOWING 절은 함수의 대상이 되는 행 기준의 범위를 강력하게 지정할 수 있다.
ROWS는 물리적인 결과 행의 수를,
RANGE는 논리적인 값에 의한 범위를 나타내는데, 둘 중의 하나를 선택해서 사용할 수 있다.
다만, WINDOWING 절은 SQL Server에서는 지원하지 않는다. 실습에 사용하는 DEPT, EMP 데이터 등록은 (https://meyouus.tistory.com/50) 참고하세요.
1. 그룹 내 순위 함수.
1-1. Rank 함수
RANK 함수는 ORDER BY를 포함한 QUERY 문에서 특정 항목(칼럼)에 대한 순위를 구하는 함수이다.
RANK() 함수는 중복 순위 다음은 해당 개수만큼 증가하여 반환
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.
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.
1-2. DENSE_RANK 함수
DENS_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.
1-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.
2. 집계 함수
2-1. 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.
2-2. MAX 함수
파티션별 조건에 해당하는 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.
2-3. MIN 함수
파티션별 조건에 해당하는 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.
2-4. AVG 함수
파티션별 조건에 해당하는 평균값 반환
SELECT MGR, ENAME, HIREDATE, SAL
/*
EMP 테이블에서 같은 매니저를 두고 있는 사원들의 평균 SALARY를 구하는데,
조건은 같은 매니저 내에서 자기 바로 앞의 사번과 바로 뒤의 사번인 직원만을 대상으로 한다
*/
, 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
2-5. COUNT 함수
파티션별 조건에 해당하는 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. 그룹 내 행 순서 함수
3-1. FIRST_VALUE 함수
파티션 별 윈도에서 가장 먼저 나온 값을 구한다.
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-2. LAST_VALUE 함수
파티션별 윈도우에서 가장 나중에 나온 값을 구한다.
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-3. LAG 함수
파티션별 윈도우에서 이전 몇 번째 행의 값을 가져올 수 있다.
-- 직원들을 입사일자가 빠른 기준으로 정렬을 하고, 본인보다 입사일자가 한 명 앞선 사원의 급여를 본인의 급여와 함께 출력한다.
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
3-4. LEAD 함수
파티션별 윈도우에서 이후 몇 번째 행의 값을 가져올 수 있다.
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.- LAG, LEAD 함수는 3개의 ARGUMENTS 까지 사용할 수 있는데,
- 두 번째 인자는 몇 번째 후의 행을 가져올지 결정하는 것이고 (DEFAULT 1),
- 세 번째 인자는 예를 들어 파티션의 마지막 행의 경우 가져올 데이터가 없어 NULL 값이 들어오는데
- 이 경우 다른 값으로 바꾸어 줄 수 있다. 결과적으로 NVL이나 ISNULL 기능과 같다
4. 그룹 내 비율 함수
4-1. RATIO_TO_REPORT 함수
RATIO_TO_REPORT 함수를 이용해 파티션 내 전체 SUM(칼럼) 값에 대한 행별 칼럼 값의 백분율을
소수점으로 구할 수 있다.
결과 값은 > 0 & <= 1 의 범위를 가진다.
개별 RATIO의 합을 구하면 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
4-2. PERCENT_RANK 함수
- PERCENT_RANK 함수를 이용해 파티션 별 윈도에서 제일 먼저 나오는 것을 0으로,
- 제일 늦게 나오는 것을 1로 하여, 값이 아닌 행의 순서별 백분율을 구한다.
- 결과 값은 >= 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
4-3. CUME_DIST 함수
CUME_DIST 함수를 이용해 파티션 별 윈도의 전체 건수에서 현재 행보다 작거나 같은 건수에 대한 누적 백분율을 구한다.
결과 값은 > 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 .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.
4-4. 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.
참고자료 : http://wiki.gurubee.net/pages/viewpage.action?pageId=26744104
반응형
'IT > 데이터베이스' 카테고리의 다른 글
| ORA-01542 오류 '테이블스페이스가 오프라인' 입니다. (0) | 2019.12.07 |
|---|---|
| DBeaver 연결 오류 Can't create driver instance for Oracle #4798 (0) | 2019.12.07 |
| DBeaver 단축키 모음 (2) | 2019.12.03 |
| 오라클 EMP, DEPT 테이블 데이터 생성하기 (0) | 2019.12.02 |
| Oralce User(사용자) Tablespace 생성 삭제 권한 부여 (0) | 2019.11.29 |
최근댓글