오라클 윈도우 함수

[ 출처 : http://evian87.tistory.com/ ]

* 집합 함수

UNION   : 합집합(중복행 합쳐짐)
UNION ALL : 합집합(중복행 그대로 표시)
INTERSECT : 교집합
EXCEPT : 차집합

윈도우 함수는 크게 4가지로 구분할 수 있습니다.
첫번째, 그룹내 순위 함수인 RANK, DENSE_RANK, ROW_NUMBER 가 있어요.
두번째, 그룹내 집계 함수인 SUM, MAX, MIN, AVG, COUNT 함수가 있습니다.
세번째, 그룹내 행 순서 함수인 FIRST_VALUE, LAST_VALUE, LAG, LEAD 함수가 있고
네번쨰, 그룹내 비율 관련 함수로 CUME_DIST, PERCENT_RANK, NTILE, RATIO_TO_REPORT 가 있어요.

1.순위함수
1) RANK 함수 : 순위함수(동일 순위가 있을 경우 중복수만큼 순위를 건너 뛴다.
2) DENSE_RANK 함수 : 동일한 순위를 하나의 건수로 취급(중복과 상관없이1부터 순차로지정)

사용방법 :
  - rank() over (order by sal desc) all_rank : sal의 역순으로 정렬하여 순위 지정
  - rank() over (partition by job order by sal desc) job_rank : sal의 역순으로 정렬하여 순위 지정하고 job에 대한 그룹 순위 지정
 - dense_rank() over (order by sal desc) dense_rank : sal의 역순으로 정렬하여 순위지정

2. 집계 함수
1) SUM 함수 : 파티션별 윈도우의 합
  - ange unbounded preceding : 현재 행을 기준으로 파티션 내의 첫번째 행까지의 범위 지정

사용방법 :
 - sum(sal) over (partition by mgr order by sal range unbounded preceding) mgr_sum :현재ROW까지의 누적
 - sum(sal) over (partition by mgr) mgr_sum : 그룹 누적값

2) MAX 함수 : 파티션별 윈도우의 최대값 / MIN 함수 : 파티션별 윈도우의 최소값

사용방법
 - max(sal) over (partition by mgr) max
 - min(sal) over (partition by mgr) min

3) AVG 함수 : 파티션별 ROWS 윈도우를 이용해 원하는 조건에 맞는 데이터에 대한 통계값 구함

사용방법
  - max(sal) over (partition by mgr) max
  - min(sal) over (partition by mgr) min
  - round(avg(sal) over (partition by mgr order by hiredate rows between 1 preceding and 1 following)) mgr_avg

4) COUNT 함수

사용방법
 - count(sal) over (order by sal range between 50 preceding and 150 following) count

3. 그룹 내 행순서 함수
1) FIRST_VALUE 함수 : 파티션별 윈도우에서 가장 먼저 나온 값 출력, 공동 등수 인정X, 처음 나온 행만 처리.
- rows unbounded preceding : 현재 행을 기준으로 파티션 내의 첫번째 행까지의 범위 지정

사용방법
 - first_value(ename) over (partition by deptno order by sal desc rows unbounded preceding) dept_rich

2) LAST_VALUE 함수 : 파티션별 윈도우에서 가장 나중에 나온 값 출력 (=MAX함수)
- rows unbounded preceding : 현재 행을 기준으로 파티션 내의 첫번째 행까지의 범위 지정
- rows between current row and unbounded following : 현재 행 포함해서 파티션 내의 마지막 행까지의 범위 지정

사용방법
 - LAST_VALUE(ename) over (partition by deptno order by sal desc rows between current row and unbounded following) dept_rich

3) LAG 함수 : 파티션별 윈도우에서 이전 몇번째 행까지의 값 가져올 수 있다.

사용방법
 - lag(sal) over (order by hiredate) prev_sal
 - lag(sal, 2) over (order by hiredate) prev_sal2

4) LEAD 함수 : 파티션별 윈도우에서 이후 몇번째 행의 값을 가져올 수 있다.

사용방법
 - lag(sal) over (order by hiredate) lag
 - lead(hiredate, 1) over (order by hiredate) lead

4. 그룹 내 비율 함수
1) RATIO_TO_REPORT 함수 : 파티션 내 전체 SUM(컬럼)에 대한 행별 컬럼 값의 백분율을 소수점으로 구할 수 있다.

사용방법
 - round(ratio_to_report(sal) over (), 2) ratio

2) PERCENT_RANK 함수 : 값이 아닌 행의 순서별 백분율 구한다.

사용방법
 - percent_rank() over(partition by deptno order by sal desc) percent_Rank

3) CUME_DIST 함수 : 파티션별 윈도우의 전체건수에서 현재 행보다 작거나 같은 건수에 대한 누적백분율 구한다.

사용방법
 - round(cume_dist() over (partition by deptno order by sal desc)

4) NTILE 함수 : 파티션별 전체 건수를 ARGUMENT 값으로 N등분한 결과를 구할 수 있다

사용방법
 - ntile(4) over (order by sal desc) ntile