Oracle 분석함수 활용법

분석 함수

RANK() OVER( [PARTITION BY <VALUE EXPRESS>]

                              ORDER BY <VALUE EXPRESS> [ASC | DESC]

                              [NULLIS FIRST | NULLS LAST]

                            )

 

활용 예문

SELECT 사원이름, 사원번호, 평균급여, 부서번호 같은 등수 7등이 2명 있다면

 

RANK() OVER (PARTITION BY 부서번호 DENS_RANK()는 8등이 되며

                              ORDER BY 평균급여 ASC) AS 순위 RANK()는 9등이 된다

FROM 사원테이블

GROUP BY DEPT_CODE, EMP_ID, EMP_NAME;

 

OVER – 해당함수가 QUERY의 RESULT SET을 이용하여 동작하는 함수라는 표시

 

( ) – 안에 기술된 ORDER BY는 RESULT SET에 순위를 부여할 때 정렬기준을 표시 해주는 부분

 

ORDER BY – 각 PARTITION 내에서 DATA 각 어떤 값을 기준으로 정렬될 것인가를 지정

 

[NULLIS FIRST | NULLS LAST] – NULL이 포함된 ROW가 순서상 제일 앞이나 제일 뒤에 위치할 것인지 지정

 

 

PARTITION BY의 기능

전체 SELECT 문에서 GROUP BY ROLLUP으로 특정 칼럼을 ID, 부서번호를 부서별의 소계와 ID별 소계가 출력이된다

그렇게 출력된 소계들을 다시 순위를 줄수 있는데

그 방법은 RANK() OVER (PARTITION BY 에 GROUPING 으로 ID 와 부서번호를 각 각 묶어주면 소계를 따로 묶어서 출력할 수 있다

 

또 한, SELECT 문에서는 모든 데이터가 RESULT SET이 일어나고 그 값으로 PARTITION으로 분류한다

MAX(점수), AVG(점수)등을 이용하고 OVER (PARTITION BY 이름)으로 묶는다면 개인별 최고 점수가 출력된다

쉽게말해 구분마다 GROUP BY 하여 컬럼에 값을 담는다라고 생각하면된다

ROLLUP과 CUBE 설명은 다음에 다시 하겟다

 

 

CUME_DIST - 관련 DATSET안에서 해당 ROW의 위치를 0에서 1까지의 값으로 표시 해준다.

 

PERCENT_RANK - 순위가 0에서 1까지의 값으로 지정되지만 소수점도 같이 지정된다

 

NTILE(N) - N이라는 숫자를 지정하면 그 숫자만큼 순위를 지정할 수 있다

                      예로 10명의 사원에서 N을 2로 지정했다면 1 다섯개와 2 다섯 개가 지정된다

 

ROW_NUMBER() - PARTITION 내의 ROW들에 순서대로 UNIQUE한 일련번호를 부여한다