그룹내 순위결정하는 함수 - ROW_NUMBER() ,RANK() ,DENSE_RANK()

 

 

 

|Oracle 그룹내 순위결정하는 함수

 

 

select ROW_NUMBER() over(partition by [그룹핑할 컬럼] order by [정렬할 컬럼])

         ,RANK()                over(partition by [그룹핑할 컬럼] order by [정렬할 컬럼])

         ,DENSE_RANK()    over(partition by [그룹핑할 컬럼] order by [정렬할 컬럼])

from 테이블명;

 

 

 

위의 3개 함수들은 그룹을 짓고, 그 안에서의 순위를 결정한다.

 

 

 

세가지 함수는 약간의 차이가 있다.

 

 

ROW_NUMBER() : 1등이 두명이어도 1,2등으로 나눈다.

 

RANK()             : 1등이 두명이면 그 다음 순위는 3등이 된다.

 

DENSE_RANK()   : 1등이 두명이면 그 다음 순위는 2등이 된다.

 

 

 

 

각 함수들의 사용에 대해서 아래 예제 로 확인해보면 개념잡기가 쉬울것이다.

 

 

EX) 예제용 테이블 생성 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() 함수는 레코드단위로 순차적으로 순위(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부터 부여지게 된다.)

 

 

 

* 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부터 순위를 매기게 됩니다.

 

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

EX)

  SELECT app_year
        ,dept
        ,pnt_grp_cd
        ,final_point1
        ,rank
,dense_rank() OVER(PARTITION BY PNT_GRP_CD ORDER BY (FINAL_POINT1) DESC ) AS SUM_RANK
FROM tr_subject_dept
WHERE APP_YEAR = '2008'
  AND PNT_GRP_CD LIKE '%'
  AND NVL(DEL_YN,'N') <> 'Y'

  

결과

    APP_YEAR DEPT PNT_GRP_CD FINAL_POINT1 RANK SUM_RANK
 1 2008 NGF0 DG01 100.00 1 1
 2 2008 NY00 DG01 100.00 1 1
 3 2008 NJ00 DG01 100.00 1 1
 4 2008 NGH0 DG01 100.00 1 1
 5 2008 NGD0 DG01 100.00 1 1
 6 2008 Z900 DG01 91.50 2 2
 7 2008 ZZ00 DG01 76.50 3 3
 8 2008 Z910 DG01 66.00 4 4
 9 2008 NGB0 DG02 104.00 1 1
10 2008 ND91 DG02 92.00 2 2
11 2008 NG90 DG02 80.00 3 3
12 2008 NG50 DG02 68.00 4 4
13 2008 NG30 DG02 66.00 5 5
14 2008 ND30 DG02 0.00 6 6
15 2008 NAF1 DG02 0.00 6 6
16 2008 ND70 DG02 0.00 6 6
17 2008 ND50 DG02 0.00 6 6
18 2008 NG70 DG02 0.00 6 6
19 2008 N500 DG03 85.00 1 1
20 2008 NAB1 DG03 0.00 2 2
21 2008 NA70 DG03 0.00 2 2
22 2008 N530 DG03 0.00 2 2
23 2008 N550 DG03 0.00 2 2
24 2008 NA30 DG03 0.00 2 2
25 2008 NA50 DG03 0.00 2 2
26 2008 NA90 DG03 0.00 2 2
27 2008 NAD0 DG03 0.00 2 2
28 2008 0000 DG04 69.00 1 1
29 2008 N330 DG04 54.00 2 2
30 2008 0100 DG04 46.00 3 3
31 2008 0500 DG04 40.00 4 4
32 2008 0110 DG04 40.00 4 4
33 2008 N300 DG04 34.00 5 5
34 2008 N100 DG04 34.00 5 5
35 2008 N391 DG04 28.00 6 6
36 2008 N350 DG04 28.00 6 6
37 2008 N370 DG04 28.00 6 6