[Oracle] RANK(), DENSE_RANK()

1. 순위(랭크) 함수

01) RANK()

RANK() : 중복 순위도 카운트

(예: 1, 2, 2, 4, 5…)

02) DENSE_RANK()

DENSE_RANK() : 중복 순위는 밀집하여(DENSE) 카운트

(예: 1, 2, 2, 3, 4…)

2. 예시 및 소스 (복뿟)

01) 테이블 생성

CREATE TABLE STUDENT_SCORE (
        NAME VARCHAR2(20) NOT NULL,
       SCORE NUMBER(3)    NULL
);

02) 데이터 삽입

INSERT INTO STUDENT_SCORE (NAME, SCORE)
SELECT '김철수', 100 FROM DUAL
UNION ALL
SELECT '이만수', 95  FROM DUAL
UNION ALL
SELECT '김지영', 95  FROM DUAL
UNION ALL
SELECT '양수민', 80  FROM DUAL
UNION ALL
SELECT '꽈뚜룹', 75  FROM DUAL
UNION ALL
SELECT '응우엔', 70  FROM DUAL
UNION ALL
SELECT '외계인', 5   FROM DUAL
UNION ALL
SELECT '민정수', 20  FROM DUAL
;

03) 실행 쿼리

SELECT NAME,
       SCORE,
       RANK() OVER (ORDER BY SCORE DESC)             RANK,
       DENSE_RANK() OVER (ORDER BY SCORE DESC) DENSE_RANK
  FROM STUDENT_SCORE
 ORDER BY SCORE DESC;