[윈도우 함수] RANK() OVER()

쿼리문 작성 시 '순위'를 매겨야 할 때가 있다. 예를 들면, A라는 class 내에서 수학성적 1등이 누구인지? 혹은 내가 운영하는 책방에서 판매량이 1위인 책이 무엇인지? 등을 알아보고 싶을 때.

이 때, 당연히 MAX()를 사용해도 된다. "SELECT MAX(sales) FROM book_sales ORDER BY sales DESC LIMIT 1;" 이렇게.

그러나 만약 section 별로 순위를 알고 싶다면?

예를 들어, 책 판매량을 "장르 별" 로 알고싶다면??? 나는 자기계발 분야, 소설 분야, 경제 분야 등 이렇게 장르 별로 판매량이 알고 싶은데... 이 땐 전체 전체 sales 중에서 max를 사용해도 장르 별 판매량 1위는 알 수가 없다. 이 때 사용하는 것이 바로 윈도우 함수인 RANK()!

 

1️⃣ RANK() OVER (PARTITION BY ~ ORDER BY)

아래와 같이 책 id, 책 이름, 책의 장르명, 장르 아이디, 판매량을 담고 있는 테이블이 있다고 가정하자.

|  book_id  |  book_name   |genre_name  |  genre_id  | sales |
|-----------|--------------|------------|------------|-------|
|     1     |game of throne|   novel    |      10    |  800  |
|     2     |dead economics| economics  |      30    |  400  |
|     3     |Leading change| business   |      40    |  100  |
|     4     |     chacha   |   novel    |      10    |  300  |
|     5     |     cake     |   cook     |      80    |   10  |

 

이 때 장르별 판매량 1순위를 알고 싶어 RANK() 함수를 쓴다면??

아래와 같은 코드가 나온다.

여기서 RANK() OVER(PARTITION BY ~~ ORDER BY ~~ ) 라는 쿼리를 쓴다.

이때 PARTITION BY에는 각 순위를 매기고 싶은 섹션을 넣으면 된다. "나는 이 섹션 별로 순위를 매겨볼게" 하는 것이다. 지금은 genre_id가 되겠지! genre_name도 당연 된다.

그 뒤 ORDER BY 는 섹션별로 순서를 매길 때 "어떤 컬럼을 기준으로 매길 것인지?" 를 말하는 것이다.

나는 지금 판매량 순위를 알고싶다. 판매량이 많은 순위!

그래서 여기서는 ORDER BY sales를 썼고 뒤에 DESC는 판매량(sales)가 많은 책 순서대로 랭킹을 매겨주세요~ 라는 것.

SELECT *, RANK() OVER(PARTITION BY genre_id ORDER BY sales DESC) as rnk
FROM tb_book;

 

그렇다면 결과는 아래와 같이 나올 것이다.

|  book_id  |  book_name   |genre_name  |  genre_id  | sales | rnk |
|-----------|--------------|------------|------------|-------|-----|
|     1     |game of throne|   novel    |      10    |  800  |  1  |
|     4     |     chacha   |   novel    |      10    |  300  |  2  |
|     2     |dead economics| economics  |      30    |  400  |  1  |
|     3     |Leading change| business   |      40    |  100  |  1  |
|     5     |     cake     |   cook     |      80    |   10  |  1  |

 

그런데 만약 여기서 섹션 별로 랭킹이 1위인 애들만 보고싶다면?

"WHERE rnk = 1"만 붙여주면 끝!

SELECT *, RANK() OVER(PARTITION BY genre_id ORDER BY sales DESC) as rnk
FROM tb_book
WHERE rnk = 1;

 

2️⃣RANK() 🆚 DENSE_RANK()

rank()함수는 크게 두가지로 갈린다.

RANK()와 DENSE_RANK().   두가지의 차이점을 알아보도록 하자.

아래와 같은 표가 있다고 가정.

|  val  |
|-------|
|   10  |
|   20  |
|   20  |
|   30  |
|   40  |

 

이 때, RANK와 DENSE_RANK를 적용해보면! (둘 다 사용법은 똑같음)

SELECT
  val,
  RANK() OVER (ORDER BY val) AS rank,
  DENSE_RANK() OVER (ORDER BY val) AS dense_rank
FROM your_table;

 

결과는 아래와 같이 나온다.

|  val  |  rank  |  dense_rank  |
|-------|--------|--------------|
|   10  |   1    |      1       |
|   20  |   2    |      2       |
|   20  |   2    |      2       |
|   30  |   4    |      3       |
|   40  |   5    |      4       |

 

즉, RANK함수는 동일한 값은 같은 순위를 부여한다. 그리고 다음 순위는 중복된 값의 개수만큼 건너 뛴 "4"를 부여한다.

val에서는 20이라는 값이 2개다. 따라서 20에 2위라는 순위를 동일하게 부여하고, 그 다음 순위인 30이라는 val에는 3위가 아닌 4위를 부여한다.

 

DENSE_RANK는 중복된 값에 동일한 순위를 부여하는 것은 RANK와 동일하다. 그러나 중복된 값의 개수만큼 건너뛰지 않고 연속적으로 순위를 부여한다.