Oracle 윈도우 함수(WINDOW FUNCTION)/집계함수(AGGREGATE FUNCTION)

윈도우 함수(Window Function) 기본 문법

① Oracle에서의 분석 & 순위 함수인 윈도우 함수는 크게 5가지 그룹으로 분류된다.

② 윈도우 함수 사용 시 OVER문구가 키워드로 필수 포함된다.

③ WINDOW 함수는 다른 함수와는 달리 중첩(NEST)해서 사용하지는 못하지만, 서브쿼리(Sub Query)에서는 사용할 수 있다.

1) Window Function Syntax

SELECT WINDOW_FUNCTION(argument) OVER( [partition by 칼럼] [order by 절] [window 절] )

FROM 테이블명;

윈도우 함수(Window Function) 종류

1) 그룹 내 순위 관련 함수

① RANK : 동일한 값에 대해서는 동일한 순위를 부여 예) 1 → 2 → 2 → 4 → 5 (기준 칼럼값이 동일하다는 전제 하에 공동 2등)

② DENSE_RANK : 동일한 순위를 하나의 등수로 취급(누적된 순위를 부여가능) 예) 1 → 2 → 2 → 3 → 4

※ RANK와 DENSE_RANK의 공통점은 둘 다 동일한 값은 동일한 순위를 부여한다는 것.

③ ROW_NUMBER : 동일한 값이라도 고유한 순위를 부여 → 동일한 순위를 인정하지 않을 때 사용. Oracle의 경우 rowid가 적은 행이 먼저 나온다.

SELECT JOB, SAL, DENSE_RANK() OVER(order by SAL DESC) DENSE_RANK, ROW_NUMBER() OVER(order by SAL DESC) ROW_NUMBER

FROM 테이블명;

2) 그룹 내 일반 집계(Aggregate) 관련 함수

- 일반적으로 많이 사용하는 SUM, MAX, AVG, COUNT 함수 등

- 집계함수는 where절에 올 수 없음.

- 집계함수는 기본적으로 null값을 가진 행을 제외하고 수행한다. 예들 들어 null을 사칙연산하면 값은 무조건 null이기 때문에 집계함수는 내부적으로 null을 제외하고 계산을 수행함.

① count(column) : null값을 제외한 행의 수를 출력.

② count(*) : null값을 포함한 행의 수 출력. 와일드카드인 애스터리스크(*)는 전체칼럼을 의미하는데, 전체칼럼이 null인 행은 존재할 수 없기 때문에, 결국 전체 행의 수 출력.

③ sum(column) : null값을 제외한 합계를 출력.

3) 그룹내 행 순서 관련 함수

- FIRST_VALUE : 파티션별 윈도우에서 가장 먼저 나온 값을 구함.

- LAST_VALUE : 파티션별 윈도우에서 가장 나중에 나온 값을 구함.

- LAG : 이전 원하는 번째 행의 값을 가져옴.

- LEAD : 이후 원하는 번째 행의 값을 가져옴.

4) 그룹 내 비율 관련 함수

- RATIO_TO_REPORT : 파티션 내 전체 SUM(칼럼)값에 대한 행별 칼럼 값의 백분율을 소수점으로 구함.

- PERCENT_RANK : 파티션별 윈도우에서 제일 먼저 나오는 것을 0으로, 제일 늦게 나오는 것을 1로 하여, 값이 아닌 행의 순서별 백분율로 표현.

- NTILE (쿼리결과값을 N등분한 결과를 숫자값으로 얻을 수 있음)

Ntile(4) over (order by ‘SAL’ DESC) → ‘급여’를 기준으로 4개의 그룹의 분류.

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

5) 그룹 내 선형 분석을 포함한 통계 분석 관련 함수

- CORR 등등