SQL 함수: 순위 함수

ROW_NUMBER, RANK, DENSE_RANK, NTILE 함수.

전체 데이터에 순위를 부여할 수도 있고, PARTITION 옵션을 사용해 지정 그룹에 따라 그룹 내 순위를 부여할 수 있다.

 

유일값으로 순위 부여하기: ROW_NUMBER 함수

모든 행에 유일값으로 순위를 부여한다. 같은 순위의 경우 정렬 순서에 따라 순위를 부여한다. 

ROW_NUMBER 함수의 기본 형식

ROW_NUMBER ()             OVER([PARTION BY 열, ..., [n] order by  열)

SELECT symbol, sector,

ROUND(close_price, 0),

ROW_NUMBER() OVER (ORDER BY ROUND(close_price, 0) DESC)

FROM nasdaq_company

해석: nasdaq_company 테이블에서 symbol, sector, close_price 열을 선택해 출력하는데, close_price는 소수첫째자리에서 반올림한 결과이고, close_price 가격이 높은 순서대로 정렬해 순위를 정해 출력하겠다.

결과

symbol                              sector                       (열 이름 없음)            (열 이름 없음)
BTC-USD                         NULL                         60776                              1
^DJI                                   NULL                         36328                              2
YM=F                                NULL                         34697                              3
^N225                               NULL                         29535                              4
NAV                             Capital Goods              18600                              5

 

다음은 close_price 값이 같으면 symbol 값을 오름차순으로 정렬해 symbol  값이 낮은 쪽에 높은 순위를 부여한 쿼리이다.

SELECT symbol, sector,

ROUND(close_price, 0),

ROW_NUMBER() OVER (ORDER BY ROUND(close_price, 0) DESC, symbol ASC)

FROM nasdaq_company

결과

symbol                          sector                        (열 이름 없음)                    (열 이름 없음)
LRCX                           Technology                            611                            61
GHC                             Miscellaneous                      608                            62
ZBRA                           Technology                            608                            63
AVGO                          Technology                            559                            64

 

그룹별로 순위를 부여하려면 PARTITION 문을 사용해야 한다. 

SELECT symbol, sector,

ROUND(close_price, 0),

ROW_NUMBER() OVER (PARTITION  BY sector ORDER BY ROUND(close_price, 0) DESC, symbol ASC)

FROM nasdaq_company

결과

symbol                          sector                          (열 이름 없음)                          (열 이름 없음)
HYMCW                     Basic Industries                          0                                                   220
HYMCZ                      Basic Industries                          0                                                   221
ITP                             Basic Industries                          0                                                   222
NAV                          Capital Goods                          18600                                                   1
NVR                          Capital Goods                          5089                                                   2
TSLA                        Capital Goods                          1222                                                   3

같은 순위 개수 고려해 순위 부여하기: RANK 함수

값은 순위일 때 같은 값을 부여한다. 1순위가 3개면 다음 순위는 2가 아니라 4가 된다. 

RANK 함수의 기본 형식

RANK ()             OVER([PARTION BY 열, ..., [n] order by  열)

SELECT symbol, sector,

ROUND(close_price, 0),

RANK() OVER (ORDER BY ROUND(close_price, 0) DESC)

FROM nasdaq_company

결과

symbol                          sector                              (열 이름 없음)                                             (열 이름 없음)
SYNA                           Technology                                   249                                                     211
CVCO                          Basic Industries                          248                                                     212
HCA                             Health Care                                 248                                                     212
IQV                               Health Care                                 247                                                     214
MSI                               Technology                                  247                                                     214
W                                  Consumer Services                   246                                                     216

 

같은 순위 개수 무시하고 순위 부여하기:  DENSE_RANK 함수

값은 순위 개수를 무시한다. 1순위가 3개면 다음 순위는 2가 된다. 

DENSE_RANK 함수의 기본 형식

DENSE_RANK ()             OVER([PARTION BY 열, ..., [n] order by  열)

SELECT symbol, sector,

ROUND(close_price, 0),

DENSE_RANK() OVER (ORDER BY ROUND(close_price, 0) DESC)

FROM nasdaq_company

결과

symbol                         sector                                                     (열 이름 없음)                           (열 이름 없음)
EL                                 Consumer Non-Durables                           350                                                     111
MA                                Miscellaneous                                             349                                                     112
ODFL                           Transportation                                             349                                                     112
ABMD                           Health Care                                                345                                                     113
SEDG                           Technology                                                  344                                                     114
SBAC                           Consumer Services                                   344                                                     114
SAIA                             Transportation                                             343                                                     115

 

그룹화 해 순위 부여하기:  NTILE 함수

인자로 지정한 값만큼 데이터 행을 그룹화해 그룹별 순위를 부여한다. 각 그룹은 1부터 순위가 매겨지며 순위는 각 행의 순위가 아닌 행이 속한 그룹의 순위다. 

NTILE 함수의 기본 형식

NTILE (integer_expression)             OVER([PARTION BY 열, ..., [n] order by  열)

SELECT symbol, sector,

ROUND(close_price, 0),

NTILE(1000) OVER (ORDER BY ROUND(close_price, 0) DESC)

FROM nasdaq_company

결과

symbol                          sector                       (열 이름 없음)                          (열 이름 없음)
BTC-USD                   NULL                          60776                                                 1
^DJI                             NULL                          36328                                                 1
YM=F                          NULL                          34697                                                 1
^N225                         NULL                          29535                                                 1
NAV                            Capital Goods          18600                                                  1
^IXIC                           NULL                          15972                                                  1
NQ=F                         NULL                          15487                                                   1
^FTSE                        NULL                          7304                                                    1
NVR                           Capital Goods           5089                                                    1
^GSPC                      NULL                          4698                                                    2
ES=F                         NULL                          4464                                                    2 
ETH-USD                  NULL                          4289                                                    2

 

전체 8918행을 1000개 행으로 나누어서 각 행이 속한 그룹의 순위를 부여한 것이다. NTILE 함수는 전체 행을 균등하게 나누어 어떠한 그룹을 생성해야 할 때 사용하기 좋다. 전체 행 수가 행의 개수로 정확하게 나누어 떨어지지 않으면 나머지  행은 마지막 그룹에 할당된다.