[MSSQL]분석함수 정리 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

결과

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

결과

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

결과

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