# Oracle Analytic Functions : Oracle, Analytic, Functions
#### # 참고주소
- [http://blog.naver.com/minis24?Redirect=Log&logNo=80100582246](http://blog.naver.com/minis24?Redirect=Log&logNo=80100582246)
- [http://rahxephon.tistory.com/1208](http://rahxephon.tistory.com/1208)
### # 분석함수
- 분석함수는 그룹함수가 아니다. 현재 보여지는 RAW데이터를 이용하여 데이터를 분석하는 함수이다.
```sql
WITH EMPLOYEES AS (
SELECT '60' DID, '103' EID, 9000 SAL FROM DUAL UNION ALL
SELECT '60' DID, '104' EID, 3000 SAL FROM DUAL UNION ALL
SELECT '60' DID, '105' EID, 4800 SAL FROM DUAL UNION ALL
SELECT '60' DID, '106' EID, 4800 SAL FROM DUAL UNION ALL
SELECT '60' DID, '107' EID, 4200 SAL FROM DUAL
)
SELECT
DID,
EID,
SAL,
LEAD(SAL,1) OVER(ORDER BY SAL DESC) AS LEAD ,
LAG (SAL,1) OVER(ORDER BY SAL DESC) AS LAG ,
ROW_NUMBER() OVER (PARTITION BY EID ORDER BY SAL DESC ) AS ROW_NUMBER1 ,
ROW_NUMBER() OVER (ORDER BY SAL DESC ) AS ROW_NUMBER2 ,
RANK() OVER (ORDER BY SAL DESC ) AS RNK ,
DENSE_RANK() OVER (ORDER BY SAL DESC ) AS DENSERNK ,
PERCENT_RANK() OVER (ORDER BY SAL DESC ) AS PERCENTRNK ,
CUME_DIST() OVER (ORDER BY SAL DESC ) AS CUME_DIST ,
NTILE(3) OVER (ORDER BY SAL DESC ) AS NTILE ,
TO_CHAR(RATIO_TO_REPORT (SAL) OVER ( ),'9,999.9999') AS RATIO
FROM EMPLOYEES
```
```
DID EID SAL LEAD LAG ROW_NUMBER1 ROW_NUMBER2 RNK DENSERNK PERCENTRNK CUME_DIST NTILE RATIO
--- --- ---------------------- ---------------------- ---------------------- ---------------------- ---------------------- ---------------------- ---------------------- ---------------------- ---------------------- ---------------------- -----------
60 104 3000 4200 1 5 5 4 1 1 3 .1163
60 107 4200 3000 4800 1 4 4 3 0.75 0.8 2 .1628
60 105 4800 4800 9000 1 2 2 2 0.25 0.6 1 .1860
60 106 4800 4200 4800 1 3 2 2 0.25 0.6 2 .1860
60 103 9000 4800 1 1 1 1 0 0.2 1 .3488
5 rows selected
```
```
■ ROW_NUMBER : ROW_NUMBER () OVER ( [query_partition_clause] order_by_clause )
▶ 행의 수 계산 (파티션 내에서 각 행에 대해 1로 시작하여 정렬되어 정의되는 유일한 수를 할당)
■ RANK () : RANK () OVER ( [query_partition_clause] order_by_clause)
▶ 중복순위를 카운팅 한 순위
■ DENSE_RANK () : DENSE_RANK () OVER ( [query_partition_clause] order_by_clause)
▶ 중복순위를 카운팅 안한 순위
■ PERCENT_RANK () : PERCENT_RANK () OVER ([query_partition_clause] order_by_clause )
▶ 그룹 수에 대한 값의 순위 퍼센트를 계산, 범위 : 0 ＜ PERCENT_RANK ＜ 1
■ CUME_DIST () : CUME_DIST ( ) OVER ( [query_partition_clause] order_by_clause )
▶ 누적 분포 (전체 중 특정한 값의 위치를 계산), 범위 : 0 ＜ CUME_DIST ＜ 1
■ NTILE () : NTILE (expr) OVER ( [query_partition_clause] order_by_clause )
▶ expr 분위수 계산 expr은 1,2,3,4 구간을 나눌 수를 적용한다.
■ RATIO_TO_REPORT : RATIO_TO_REPORT ( expr ) OVER ( [query_partition_clause] )
▶ 비율값을 출력
■ 비교 : {LAG | LEAD} ( value_expr [, offset] [, default] ) OVER ( [query_partition_clause] order_by_clause )
LAG : 현재 위치보다 더 앞선 행 접근
LEAD : 현재 위치 이후 주어진 행 접근
- 현재 행으로부터 지정한 수 만큼 다른 행에 접근하여 값 검색
- 이를 이용하면 Self Join의 사용이 불필요하다 -> 상당한 Performance 향상
- 전년 대비 지역별 올해 매출
```
### # 윈도우 함수
※ 분석함수 중에서 윈도우절을 사용하는 함수를 윈도우 함수라고 한다.
윈도우절은 분석함수중에서 일부만 사용할 수가 있다.
```
■ 윈도우란?
- PARTITION BY 그룹을 디테일하게 나눈 그룹.
- PARTITION BY 절에의한 그룹을 디테일하게 그룹핑.
■ 사용할 수 있는 윈도우함수
# AVG
# COUNT
# FIRST_VALUE
# MAX
# MIN
# STDDEV
# SUM
■ 문법
윈도우함수(컬럼명1,컬럼명2,....컬럼명n) OVER(
PARTITION BY 표현식
ORDER BY 표현식 [ASC|DESC]
ROWS | RANGE
BETWEEN UNBOUNDED PRECEDING | PRECEDING | CURRENT ROW AND UNBOUNDED FOLLOWING | CURRENT ROW
)
● ROWS : 부분 집합인 윈도우 크기를 물리적인 단위로 행 집합을 지정
● RANGE : 논리적인 주소에의해 행 집합을 지정?
● BETWEEN ~ AND : 윈도우의 시작과 끝 위치를 지정
● UNBOUNDED PRECEDING : 윈도우의 시작위치가 첫번째 로우
● UNBOUNDED FOLLOWING : 윈도우의 시작위치가 마지막 로우
● CURRENT ROW : 윈도우의 시작위차가 현재 로우
```
----
```sql
WITH EMPLOYEES AS (
SELECT '60' DID, '103' EID, 9000 SAL FROM DUAL UNION ALL
SELECT '60' DID, '104' EID, 6000 SAL FROM DUAL UNION ALL
SELECT '60' DID, '105' EID, 4800 SAL FROM DUAL UNION ALL
SELECT '60' DID, '106' EID, 4800 SAL FROM DUAL UNION ALL
SELECT '60' DID, '107' EID, 4200 SAL FROM DUAL
)
SELECT EID, SAL,
SUM(SAL) OVER( PARTITION BY DID ORDER BY EID ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING ) WIN1,
SUM(SAL) OVER( PARTITION BY DID ORDER BY EID ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW ) WIN2,
SUM(SAL) OVER( PARTITION BY DID ORDER BY EID ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING ) WIN3
FROM EMPLOYEES
;
```
```
EID SAL WIN1 WIN2 WIN3
--- ---------------------- ---------------------- ---------------------- ----------------------
103 9000 28800 9000 28800
104 6000 28800 15000 19800
105 4800 28800 19800 13800
106 4800 28800 24600 9000
107 4200 28800 28800 4200
5 rows selected
BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING : 윈도우의 시작위치부터 윈도우의 마지막 로우 전체 합계
BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW : 윈도우의 시작위치부터 현재로우까지의 누적 합계
BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING : 현재로우부터 마지막로우까를 윈도우로 설정한 합계.
```
```sql
WITH EMPLOYEES AS (
SELECT '60' DID, '103' EID, 9000 SAL FROM DUAL UNION ALL
SELECT '60' DID, '104' EID, 6000 SAL FROM DUAL UNION ALL
SELECT '60' DID, '105' EID, 4800 SAL FROM DUAL UNION ALL
SELECT '60' DID, '106' EID, 4800 SAL FROM DUAL UNION ALL
SELECT '60' DID, '107' EID, 4200 SAL FROM DUAL
)
SELECT EID, SAL,
SUM(SAL) OVER( PARTITION BY DID ORDER BY EID ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING ) WIN1,
SUM(SAL) OVER( PARTITION BY DID ORDER BY EID ROWS BETWEEN 1 PRECEDING AND CURRENT ROW ) WIN2,
SUM(SAL) OVER( PARTITION BY DID ORDER BY EID ROWS BETWEEN CURRENT ROW AND 1 FOLLOWING ) WIN3
FROM EMPLOYEES
;
```
```
EID SAL WIN1 WIN2 WIN3
--- ---------------------- ---------------------- ---------------------- ----------------------
103 9000 15000 9000 15000
104 6000 19800 15000 10800
105 4800 15600 10800 9600
106 4800 13800 9600 9000
107 4200 9000 9000 4200
5 rows selected
BETWEEN 1 PRECEDING AND 1 FOLLOWING : 현재로우의 한개 이전부터 한개 다음까지의 합계
BETWEEN 1 PRECEDING AND CURRENT ROW : 현재로우의 한개 이전부터 현재로우까지의 합계
BETWEEN CURRENT ROW AND 1 FOLLOWING : 현재로우 부터 다음로우까지의 합계
```
#### ■ 예제 -- 각 부서에서 최저급여보다 큰 사원만 나오게
- 방법1
```sql
with emp as (
select level empno, dbms_random.string('u',10) as ename,
(mod(level-1,5)+1) * 1000000 sal, (mod(level-1,3)+1)*10 deptno,
trunc(sysdate + mod(level-1,3)+1) as hire_date
from dual connect by level ＜= 10
order by hire_date
)
select b.* from (
select deptno, min(sal) min_sal
from emp
group by deptno
) a, emp b
where b.sal > a.min_sal
and b.deptno = a.deptno;
;
```
- 방법2
```sql
with emp as (
select level empno, dbms_random.string('u',10) as ename,
(mod(level-1,5)+1) * 1000000 sal, (mod(level-1,3)+1)*10 deptno,
trunc(sysdate + mod(level-1,3)+1) as hire_date
from dual connect by level ＜= 10
order by hire_date
)
select
*
from (
select
a.*,
min(sal) over(partition by a.deptno) min_sal
from emp a
) b
where b.sal >b.min_sal
;
```
#### ■ 예제 -- range이용
```sql
with emp as (
select level empno, dbms_random.string('u',10) as ename,
(mod(level-1,5)+1) * 1000000 sal, (mod(level-1,3)+1)*10 deptno,
trunc(sysdate + mod(level-1,3)+1) as hire_date
from dual connect by level ＜= 10
order by hire_date
)
select e.*, sum(sal) over(order by hire_date rows between unbounded preceding and current row) as accum_sal,
sum(sal)over(order by hire_date range between interval '1' day preceding and interval '1' day following) as interval_sal,
sum(sal)over(order by hire_date range between interval '1' day preceding and interval '1' day current row) as current_sal,
sum(sal) over() total_sum
from emp e;
```
```
■ NULLS FIRST
ORDER BY NULLS FIRST : 널을 앞에 표시.
■ NULLS LAST
ORDER BY NULLS LAST : 널을 뒤에 표시.
■ KEEP : 확실히 다시 알아봐야함.
집합함수 KEEP (
DENSE_RANK FIRST ORDER BY
expr [DESC|ASC][NULL{FIRST|LAST}],...)
```
```sql
WITH EMPLOYEES AS (
SELECT '60' DID, '103' EID, 9000 SAL FROM DUAL UNION ALL
SELECT '60' DID, '104' EID, 6000 SAL FROM DUAL UNION ALL
SELECT '60' DID, '105' EID, 4800 SAL FROM DUAL UNION ALL
SELECT '60' DID, '106' EID, 4800 SAL FROM DUAL UNION ALL
SELECT '60' DID, '107' EID, 4200 SAL FROM DUAL
)
SELECT EID, SAL,
MAX(SAL) KEEP( DENSE_RANK FIRST ORDER BY DID ) WIN1
FROM EMPLOYEES
;
```
'database' 카테고리의 다른 글
| SQL 이란 ? (0) | 2019.08.13 |
|---|---|
| Oracle Analytic Functions 누적합계 (0) | 2019.08.12 |
| PostgresQL (0) | 2019.07.29 |
| Oracle MERGE INTO (0) | 2019.07.19 |
| 유용한_쿼리 SQL - Comment, PK, INDEX (0) | 2019.07.16 |
댓글