[MySQL8] RANK, ROW_NUMBER

기획서 스펙이 너무나도 복잡해서 쿼리로 커버되지 않을것만 같았지만, 시니어 개발자 분이 요런 키워드를 알려주셨다.
RANK, ROW_NUMBER
음. 나도 2년차 개발자지만 지금까지 MySQL 쿼리를 짜면서 마주해본적이 없던 키워드라 간단히 공부해보았다. 시니어개발자분은 Oracle DB 경험이 많으셔서 제안을 주셨다고 했다.
원래 MySQL에는 해당 기능을 제공하지 않았다가, 버전 8부터 제공한다고 했고, 마침 우리 DB 가 버전 8이기에 시도해볼 수 있게 되었다.
그러면 하나씩 알아가보자.

일단 공통적으로, 이들은 쿼리 결과에 대한 순번을 매기는데 사용된다.

• order by 된 결과대로 각 row_num을 구하고 싶을 때 사용한다.
• 첫번째 row를 1로 시작하여, +1씩 증가하며 결과가 도출된다.

RANK()

• 설명
  • order by 를 거친 후 order by 의 결과 순서대로 값을 반환해준다.
  • 사실 partition by도 사용할 수 있지만, 요건 ROW_NUMBER()를 설명하면서 같이 다루겠음. (간단히 얘기하자면, 성격은 약간 group by랑 유사함)
  • 예를 들어 각각 cost 값이 6, 5, 2, 2, 4 인 행이 5개 있었고, 여기서 rank() over (order by cost)를 했다고 가정해보자.
  • 그러면 cost, rank() pair는 각각 아래와 같이 잡힌다.
    • <6, 4>, <5, 3>, <2, 1>, <2, 1>, <4, 2>
  • 같은 element인 아이에 대해서는 같은 우선순위가 매겨지지만, 다음 element에 대해서 +1 순위로 매겨진다.

DENSE_RANK()

• 설명
  • 일반 rank()와의 차이점은 같은 우선 순위 다음 element에 대한 처리 방식 이다.
  • 예를 들어 각각 cost 값이 6, 5, 2, 2, 4 인 행이 5개 있었고, 여기서 dense_rank() over (order by cost)를 했다고 가정해보자.
  • 그러면 cost, dense_rank() pair는 각각 아래와 같이 잡힌다.
    • <6, 5>, <5, 4>, <2, 1>, <2, 1>, <4, 3>
  • 같은 element인 아이에 대해서는 같은 우선순위가 매겨지지만, 다음 element에 대해서는 +1 순위가 아닌, 건너뛰는 방식으로 매겨진다.
• 사용 방식

select id,
    amount,
    rank() over (order by amount desc) as ranking 
from ex_card

select id,
    amount,
    dense_rank() over (order by amount desc) as ranking 
from ex_card

ROW_NUMBER()

• 문법
  - ROW_NUMBER() OVER(PARTITION BY [그룹핑할 컬럼] ORDER BY [정렬할 컬럼])
  - PARTITION BY는 선택, ORDER BY는 필수
• partition by를 추가하게 되면, partition by에 주어진 컬럼들을 기준으로 ROW_NUM이 각각 새롭게 매겨진다.
  • 예를들어 SELECT ROW_NUMBER() OVER(PARTITION BY T1.JOB ORDER BY T1.JOB, T1.ENAME) 라고 했다면, 각 JOB마다 새롭게 1번 순번이 매겨지며 결과가 도출된다. JOB A에 대해서 1번부터, JOB B에 대해서 1번부터, ..
  • 즉, 전체 order by를 한 뒤, partition by를 통해 특정 column으로의 grouping으로 부터 각각에 대한 최적 순서도 알 수 있는 것이다.
  • 요 예시는 https://tychejin.tistory.com/201 를 보면 좋을듯.
• 설명
  • 일반 rank()와의 차이점은 같은 우선 순위에 대한 처리 방식 이다.
  • 예를 들어 각각 cost 값이 6, 5, 2, 2, 4 인 행이 5개 있었고, 여기서 row_number() over (order by cost)를 했다고 가정해보자.
  • 그러면 cost, dense_rank() pair는 각각 아래와 같이 잡힌다.
    • <6, 5>, <5, 4>, <2, 1>, <2, 2>, <4, 3>
  • 같은 element인 아이에 대해서도 반드시 distinct한 우선순위가 매겨진다. (같은 우선순위의 element에 대해서 누가 더 높은 row_number()가 나올지는 알 수 없다.

• 참고 자료
  • https://pearlluck.tistory.com/642
  • https://tychejin.tistory.com/201
  • https://crosstheline.tistory.com/104
  • https://docu94.tistory.com/72
  • https://coding-factory.tistory.com/443