RANK()함수 널(NULL) 제외하고 카운팅하기

다음과 같은 TEST 테이블에 자료가 있다고 하자.

이때 널 값을 제외하고 석차(랭킹)을 구하고 싶을 때 

다음과 같은 다양한 상황을 고려해야 한다.

 
SELECT ID, NAME, SCORE [성적], GRADE [등급],   
  RANK() OVER (ORDER BY SCORE ) [r1_오름_널도카운팅], 
  RANK() OVER (ORDER BY SCORE DESC) [r2_내림_널도카운팅], 
  CASE WHEN SCORE IS NULL THEN NULL ELSE RANK() OVER (ORDER BY SCORE ) END [r3_오름_문제됨],
  CASE WHEN SCORE IS NULL THEN NULL ELSE RANK() OVER (ORDER BY SCORE DESC) END [r4_내림_정상],
  CASE WHEN SCORE IS NULL THEN NULL ELSE RANK() OVER (ORDER BY CASE WHEN SCORE IS NULL THEN 1 ELSE 0 END, SCORE ) END [r5_오름_정상],
  CASE WHEN SCORE IS NULL THEN NULL ELSE RANK() OVER (ORDER BY CASE WHEN SCORE IS NULL THEN 1 ELSE 0 END, SCORE DESC) END [r6_내림_정상]
FROM TEST
ORDER BY ID

오름차순 구문

OVER (ORDER BY CASE WHEN SCORE IS NULL THEN 1 ELSE 0 END, SCORE ) 

해석해 보면

SCORE 가 널일 경우 OVER(ORDER BY 1 ASC, SCORE)

널이 아닐 경우 OVER(ORDER BY 0 ASC, SCORE)

ASC 는 생략되어 있다.

결국 오름차순일 경우 널이 아닌 경우(0)가 먼저 나오고 널인 경우가 나중에 나온다.

마찬가지로 내림차순의 경우도

SCORE 가 널일 경우 OVER(ORDER BY 1 ASC, SCORE DESC)

널이 아닐 경우 OVER(ORDER BY 0,ASC, SCORE DESC)

로 해석되므로 널은 제외하고 카운팅된다.

오름차순은 꼭 위 사항을 고려해야한다. 널(NULL)이 아스키코드가 제일 빠르므로

내림차순의 경우는 OVER 절 안에 CASE 문은 없어도 된다.