row_number()over

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

 

[출처] ROW_NUMBER() OVER / RANK() OVER / DENSE_RANK() OVER - 분석용 함수|작성자 행복한오뚜기

 

 

 

 

join (=inner join)

left join(=left outer join)