mysql - RANK 문법

예제 데이터 쿼리문

DROP DATABASE IF EXISTS pokemon;
CREATE DATABASE pokemon;
USE pokemon;
CREATE TABLE mypokemon (
number INT,
name VARCHAR(20),
type VARCHAR(10),
attack INT,
defense INT,
capture_date DATE
);

INSERT INTO mypokemon (number, name, type, attack, defense, capture_date)
VALUES (10, 'caterpie', 'bug', 30, 35, '2019-10-14'),
(25, 'pikachu', 'electric', 55, 40, '2018-11-04'),
(26, 'raichu', 'electric', 90, 55, '2019-05-28'),
(125, 'electabuzz', 'electric', 83, 57, '2020-12-29'),
(133, 'eevee', 'normal', 55, 50, '2021-10-03'),
(137, 'porygon', 'normal', 60, 70, '2021-01-16'),
(152, 'chikoirita', 'grass', 49, 65, '2020-03-05'),
(153, 'bayleef', 'grass', 62, 80, '2022-01-01');

RANK

• RANK() OVER(ORDER BY [컬럼이름]) 형식으로 사용
• 항상 ORDER BY와 함께 사용
• select절에 사용하며 정렬된 순서를 붙인 새로운 컬럼을 보여줌.

-- 예시 쿼리
SELECT name, attack, 
RANK() OVER (ORDER BY attack DESC) AS attack_rank
FROM pokemon.mypokemon;

결과

 

RANK / DENSE_RANK / ROW_NUMBER()

사용 형식은 셋 모두 같다.

SELECT name, attack,
RANK() OVER (ORDER BY attack DESC) AS rank_rank,
DENSE_RANK() OVER (ORDER BY attack DESC) AS rank_dense_rank,
ROW_NUMBER() OVER (ORDER BY attack DESC) AS rank_row_number
FROM mypokemon;

3가지 함수의 차이