오라클 분석함수

 

 

 

FIRST_VALUE(expr) OVER(   )	정렬된 값들 중 첫 번째 값을 반환한다.
COUNT(expr) OVER(  )
SUM(expr) OVER ()
LAST_VALUE(expr) OVER
RANK() OVER
ROW_NUMBER() OVER
DENSE_RANK() OVER

 

 

 

 

 

 

 

 

 

예제 테이블 SQL 쿼리

CREATE TABLE TEST
(
 A VARCHAR(10),
 B VARCHAR(10)
);

INSERT INTO TEST VALUES ('2','516958');
INSERT INTO TEST VALUES ('1','123458');
INSERT INTO TEST VALUES ('1','458512');
INSERT INTO TEST VALUES ('2','468521');
INSERT INTO TEST VALUES ('2','735196');
INSERT INTO TEST VALUES ('1','794528');

COMMIT;

RANK()함수와 ROW_NUMBER()함수 정의

RANK() 정의

less..

 

RANK()함수는 레코드단위로 순차적으로 순위(1부터 출력)을 부여하고 레코드단위로
같은값에 대해서는 동일한 순위를 부여한다.

PARTITION BY 를 사용하면 전체를 한그룹으로 보는것이 아니라 PARTITION BY 에 사용된
컬럼을 기준으로 다르게 그룹을 나누어 순위를 부여한다.
오라클 8i부터 지원하는 분석함수입니다.

아래는 순위입니다.
순위는 동점자가 있을 수 있고 공동 순위가 있다면 중간에 비는 숫자도 있겠죠.

RANK()  사용예제

SELECT A,B, RANK() OVER(order by A,B) from TEST;

결과

1 123458 1
1 123458 1
1 458512 3
1 794528 4
2 468521 5
2 516958 6
2 735196 7

SELECT A,B, RANK() OVER(PARTITION BY A ORDER BY A,B) from TEST;

결과

1 123458 1
1 123458 1
1 458512 3
1 794528 4
2 468521 1
2 516958 2
2 735196 3

※ OVER() 함수는 필수입니다.

※ OVER() 함수안에 PARTITION BY 는 미필수지만 ORDER BY는 필수이다.

※ 보통 OVER() 함수안에 ORDER BY 절에는 출력컬럼을 모두 써준다.

※ PARTITION BY 을 사용하면 출력결과를 하나의 그룹으로 보는것이 아니라 PARTITION BY 에 사용된 컬럼을 기준으로 그룹을 나누어지게 된다.

(순위도 그룹별로 별개로 1부터 부여지게 된다.)

less..

ROW_NUMBER() 정의

less..

 

ROW_NUMBER()는 레코드단위로 동일한값이라도 매번 새로운 순위를 부여한다.

ROW_NUMBER() 함수는 각 PARTITION 내에서 ORDER BY절에 의해 정렬된 순서로 유일한 값을 돌려주는 함수이며 ROWNUM 과는 관계가 없습니다.

오라클 8i부터 지원하는 분석함수입니다.

위에거는 순번이구요
순번은 유일한 값이구요,

ROW_NUMBER() 기본예제

SELECT A, ROW_NUMBER() OVER(ORDER BY A,B) FROM TEST;

결과

1 123458 1
1 123458 2
1 458512 3
1 794528 4
2 468521 5
2 516958 6
2 735196 7

SELECT A, B, ROW_NUMBER() OVER(PARTITION BY A ORDER BY A,B) FROM TEST;

결과

1 123458 1
1 123458 2
1 458512 3
1 794528 4
2 468521 1
2 516958 2
2 735196 3

※ OVER() 함수는 필수입니다.

※ OVER() 함수안에 PARTITION BY 는 미필수지만 ORDER BY는 필수이다.

※ PARTITION BY 을 사용하면 PARTITION BY 에 사용된 컬럼을 기준으로 서로 별개로 1부터 순위를 매기게 됩니다.

ROW_NUMBER() 응용예제

A ,B 그룹별 A,B의 출력을 상위 2개만 출력하기

SELECT A,B FROM
(
 SELECT A, B,
 ROW_NUMBER() OVER(PARTITION BY A ORDER BY B) rn
 FROM TEST
)
WHERE rn <= 2;

또는

SELECT A,B FROM
(
 SELECT A,B,
 ROW_NUMBER() OVER(PARTITION BY A ORDER BY A,B) RM FROM TEST GROUP BY A,B
) WHERE RM <= 2;

결과

1 123458
1 123458
2 468521
2 516958

A 그룹별 A출력을 상위 1개만 출력하기

SELECT A FROM
(
 SELECT A,
 ROW_NUMBER() OVER(ORDER BY A) rn
 FROM TEST GROUP BY A
)
WHERE rn <= 1;

-------------------------------------------------------------------------

row_number()함수 예제3

직업별로 급여 합계를 계산해서 급여 합계가 많은 순으로 가장 많은 직업 3개만 출력하는 예제 입니다.

-- 일반적인 SQL방법

SELECT job, sal FROM
(
SELECT job, SUM(sal) sal FROM emp
GROUP BY job
ORDER BY sal DESC
)
WHERE rownum < 4;

JOB               SAL
---------- ----------
MANAGER         33925
ANALYST          6000
PRESIDENT        5000

-- Analytic function ROW_NUMBER()을 사용하는 방법

SELECT job, sal FROM
(
SELECT job, SUM(sal) sal, ROW_NUMBER() OVER (ORDER BY SUM(sal) DESC) num
FROM emp
GROUP BY job
)
WHERE num < 4;

JOB               SAL
---------- ----------
MANAGER         33925
ANALYST          6000
PRESIDENT        5000