오라클 분석함수 -- 순위함수

예제 테이블

CREATE TABLE TEST(

A VARCHAR2(10),

B VARCHAR2(10)

);

INSERT INTO TEST VALUES ('2','516958');

INSERT INTO TEST VALUES ('1','123458');

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');

---

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

RANK() 정의

RANK() 함수는 레코드 단위로 순차적으로 순위 (1부터 출력)를 부여하고 

레코드 단위로 같은 값에 대해서는 동일한 순위를 부여한다.

PARTIRION BY 를 사용하면 전체를 한 그룹으로 보는것이 아니라 PARTITION  BY 에 사용된

컬럼을 지준으로 다르게 그룹을 나누어 순위를 부여한다.

오라클 8i 부터 지원하는 분석함수

공동순위가 있으므로 순위에 비는 숫자가 생깁니다.

중복 값을 허용 하면서 비는 숫자를 없애려면

DENSE_RANK() 사용하면 된다.

RANK() 사용 예제

SELECT A, B, RANK() OVER(ORDER BY A, B) 순위 FROM TEST;

전체

A           B         순위

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;

PARTITION BY  적용  A 컬럼을 기준으로 그룹을 나눔

A           B         순위

1   123458      1

1   123458      1

1   458512      3

1   794528      4

2   468521      1

2   516958      2

2   735196      3

---

ROW_NUMBER() 정의

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

ROW_NUMBER() 함수는 각 PARTITION 내에서 ORDER BY 절에 의해 

정렬된 순서로 유일한 값을 돌려주는 함수이면 ROWNUM 과는 관계가 없습니다.

오라클 8i 부터 지원하는 분석 함수

중복 순위가 없고 모든 값은 유일값을 가지게 된다.

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

전체

A           B         순번

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;

PARTITION BY  적용  A 컬럼을 기준으로 그룹을 나눔

A           B         순번

1   123458     1

1   123458     2

1   458512     3

1   794528     4

2   468521     1

2   516958     2

2   735196     3

---

NTILE () 정의

NTILE 함수는 쿼리의 결과를 N개의 그룹으로 분류하는 기능을 제공

SELECT A, B,

NTILE(2) OVER (ORDER BY A) 그룹1,

NTILE(3) OVER (ORDER BY A) 그룹2,

NTILE(5) OVER (ORDER BY A) 그룹3

FROM TEST;

결과 값 : 

A	B	그룹1	그룹2	그룹3
1	794528	1	1	1
1	123458	1	1	1
1	458512	1	1	2
1	123458	1	2	2
2	735196	2	2	3
2	516958	2	3	4
2	468521	2	3	5

---

* 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) 전체 FROM TEST) 

WHERE 전체 <= 2;

결과 값 :

A        B

1 123458

1 123458

2 468521

2 516958

---

SELECT A, B FROM (

SELECT A, B, ROW_NUMBER() OVER (PARTITION BY A ORDER BY A, B) "중복 제외"

FROM TEST GROUP BY A, B) WHERE "중복 제외" <= 2;

결과 값 :

A        B

1 123458

1 458512

2 468521

2 516958

---

ROW_NUMBER  응용예제 2

직업별로 급여 합계를 계산해서 급여 합계가 많은 순으로 가장 많은 직업 3개만 출력하기

ROW_NUMBER 사용 안할시

SELECT JOB, "급여 합계" FROM 

(

SELECT JOB, SUM(SAL) "급여 합계" FROM EMP

GROUP BY JOB

ORDER BY "급여 합계" DESC

) 

WHERE ROWNUM < 4;

ROW_NUMBER  사용

SELECT JOB, "급여 합계" FROM

(

SELECT JOB, SUM(SAL) "급여 합계", 

ROW_NUMBER() OVER (ORDER BY SUM(SAL) DESC) NUM

FROM EMP

GROUP BY JOB

)

WHERE NUM < 4;

결과 값 : 

    JOB          결과 합계

MANAGER        8275

ANALYST        6000

SALESMAN        5600

---

PARTITION BY

PARTITION BY는 select한 결과를 특정 column을 기준으로 나누는 역할을 한다. 여기서는 userid를 기준으로 partition을 나눴다. 그리고 나뉜 partition은 score column을 기준으로 내림차순으로 정렬을 하고, 정렬된 내용을 RANK() OVER를 통해 순서를 매긴다. 마지막으로 한 번 더 select하여 상위 3개를 가져온다

---