[ORACLE] ROW_NUMBER, RANK, DENSE_RANK, 분석용함수 정리

★ ROW_NUMBER() OVER- 특정 컬럼 기준으로 순위정하기(행번호 부여하기)

 

 

SELECT T.MAIN_CODE      , T.GOODS_SEQ      , T.OFFER_MASTER_SEQ      , MODEL_CD      , (ROW_NUMBER() OVER(PARTITION BY T.MAIN_CODE ORDER BY T.MAIN_CODE, T.GOODS_SEQ, T.OFFER_MASTER_SEQ)) RANK --  , (ROW_NUMBER() OVER(PARTITION BY 중복조회컬럼 ORDER BY 정렬컬럼1, 정렬컬럼2, ...))   FROM TMP_TABLE T

 

결과

MAIN_CODE     GOODS_SEQ    OFFER_MASTER_SEQ     MODEL_CD    RANK

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

1                  1                 1                             AAA            1

1                  1                 2                             AAA            2

1                  2                 4                             BBB            3

1                  2                 5                             BBB            4

2                  1                 1                             AAA            1

2                  1                 4                             AAA            2

2                  2                 5                             BBB            3

2                  2                 6                             BBB            4

2                  3                 7                             CCC            5

2                  3                 9                             CCC            6

 

 

★ 특정 column의 값을 기준으로 레코드의 순서정하여 정렬하기.

TEAM_CD     SCORE     PALY_DATE

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

AAA            90          2010/08/01

AAA            50          2010/08/02

AAA            60          2010/08/03

AAA            50          2010/08/04

BBB            50          2010/08/01

BBB            90          2010/08/02

BBB            95          2010/08/03

BBB            100         2010/08/04

 

방법 1. RANK() OVER

 

SELECT T.TEAM_CD, T.SCORE, RANK() OVER(ORDER BY SCORE DESC) RANK, T.PLAY_DATE   FROM TMP_TABLE T

 

결과

TEAM_CD     SCORE     RANK     PALY_DATE

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

BBB            100        1          2010/08/04

BBB            95          2          2010/08/03

AAA            90          3          2010/08/01

BBB            90          3          2010/08/02

AAA            60          5          2010/08/03

AAA            50          6          2010/08/02

AAA            50          6          2010/08/04

BBB            50          6          2010/08/01

 

방법 2. ROW_NUMBER() OVER

 

SELECT T.TEAM_CD, T.SCORE, ROW_NUMBER() OVER(ORDER BY SCORE DESC) RANK, T.PLAY_DATE   FROM TMP_TABLE T

 

결과

TEAM_CD     SCORE     RANK     PALY_DATE

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

BBB            100        1          2010/08/04

BBB            95          2          2010/08/03

AAA            90          3          2010/08/01

BBB            90          4          2010/08/02

AAA            60          5          2010/08/03

AAA            50          6          2010/08/02

AAA            50          7          2010/08/04

BBB            50          8          2010/08/01

 

방법 3. DENSE_RANK() OVER

 

SELECT T.TEAM_CD, T.SCORE, DENSE_RANK() OVER(ORDER BY SCORE DESC) RANK, T.PLAY_DATE   FROM TMP_TABLE T

 

결과

TEAM_CD     SCORE     RANK     PALY_DATE

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

BBB            100        1          2010/08/04

BBB            95          2          2010/08/03

AAA            90          3          2010/08/01

BBB            90          3          2010/08/02

AAA            60          4          2010/08/03

AAA            50          5          2010/08/02

AAA            50          5          2010/08/04

BBB            50          5          2010/08/01

 

 

 

★ 분석용 함수

RANK - 해당값에 대한 우선순위를 결정 (중복 우선순위 허용)

DENSE_RANK - 해당값에 대한 우선순위를 결정 (중복 우선순위 허용 안함)

ROW_NUMBER - 조건을 만족하는 모든 행의 번호를 제공

CUME_DIST - 분산값

PERCENT_RANK - 백분율

NTILE(n) - 전체 데이터 분포를 n-Buckets으로 나누어 표시

FIRST_VALUE - 정렬된 값중에서 첫번째 값을 반환.

LAST_VALUE - 정렬된 값중에서 마지막 값을 반환.

 

★ OVER() 에 사용되는 OPTION

1. PARTITION BY
2. ORDER BY DESC
3. NULLS FIRST : NULL 데이터를 먼저 출력.
4. NULLS LAST : NULL 데이터를 나중에 출력.