분석함수 정리 partition by, rank() over, row_number() over, dense_rank() over 출처: http://ralf79.tistory.com/194 [진형아빠이야기]

오랜만에 쿼리를 사용하게 되면서 예전에는 잘 사용하지 않앗던 분석함수들을 사용하게 되어서 찾아본 정보들을 정리해봅니다. 

초록지붕의 앤이라는 분의 포스트를 참조해서 쿼리만 보기 좋게 변경해보았습니다.

http://annehouse.tistory.com/416

ROW_NUMBER() OVER : 그룹을 만들고 각 그룹별로 행번호 부여하기

 

 SELECT A.CODE

            , A.SEQ

            , A.MASTER_SEQ

            , A.CD

            , (ROW_NUMBER() OVER(PARTITION BY A.CODE ORDER BY A.CODE, A.SEQ, A.MASTER_SEQ)) RANK

    FROM TABLE A

 

CODE        SEQ    MASTER_SEQ     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

 

 

다음의 테이블 정보를 정렬하는 방법들을 알아보도록 하겠습니다.

CD         SCORE          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.CD

            , T.SCORE

            , RANK() OVER(ORDER BY SCORE DESC) RANK

            , T.DATE

  FROM TABLE T

 

rank는 같은 순서는 동일한 값으로 나온다.

결과

CD        SCORE     RANK         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.CD

            , T.SCORE

            , ROW_NUMBER() OVER(ORDER BY SCORE DESC) RANK

            , T.DATE

   FROM TABLE T

ROW_NUMBER는 같은 값이라도 다른 숫자가 나온다.

 

결과

CD          SCORE     RANK         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.CD

            , T.SCORE

            , DENSE_RANK() OVER(ORDER BY SCORE DESC) RANK

            , T.DATE

    FROM TABLE T

dense_rank도 같은 값이면 동일 랭크가 나온다

 

결과

CD           SCORE     RANK     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 데이터를 나중에 출력.

조금 수정
출처: http://ralf79.tistory.com/194 [진형아빠이야기]