SQLでRANK関数を使わずにデータに順位を付ける [MySQL]

データベースがSQLServerであればTransact-SQL(T-SQL)にRANK関数が用意されているので、取得するデータに対して簡単に順位を付けることができますが、MySQLなどの他のデータベースでは順位を取得する関数などが用意されていないことがあります。

そんな時にSQLで順位を取得する方法を紹介します。

1 順位を付けるSQL
2 SELECTで順位を付ける
3 順位を飛ばさずに順位を付ける
4 MySQLでの順位の取得

順位を付けるSQL

RANK関数などを使わずに順位を取得する方法として、順位をつけるデータを一旦順位をつける順番（昇順または降順）にすべて取得して、それをFetchなどの方法で番号をつけていくという方法もありますが、ここではSQLのSELECTステートメントのみで取得する方法で実装していきます。

順位の取得方法

取得するデータ内の各レコードの順位をつけるには、順位をつける基準となる得点やスコアなどの値を参照して、自分自身のレコードが持つ値よりも得点やスコアなどが低いまたは高いレコードの数をCOUNT関数で取得します。COUNT関数で取得した値が自分自身のレコードの順位となる値より小さい値になるので、これに対して1を加算することで、自分自身のレコードの順位を取得することができます。

例えばゴルフの成績を保持する「スコア」列がある「ゴルフ」テーブルがあり、スコアの高いものから順位を付ける場合をSQLで表現するなら

SELECT COUNT(*) FROM ゴルフ
WHERE ゴルフ.スコア < 自分自身のレコードのスコア

1 2	SELECT COUNT(*) FROM ゴルフ WHERE ゴルフ.スコア < 自分自身のレコードのスコア

になり、自分自身のレコードのより高いスコアのレコードの件数が取得できます。
この取得した値に1を足せば自分自身のレコードの順位になります。

サンプルテーブルとデータ

次のSQLは上記のゴルフテーブルのCREATEとINSERTのSQLです。

-- サンプルテーブルの作成とデータの作成
-- テーブルの作成
CREATE TABLE ゴルフ
(
    プレイヤー NVARCHAR(20),
    スコア INT
);
-- データの挿入
INSERT INTO ゴルフ
    (プレイヤー, スコア)
VALUES
    ('プレイヤー1', 76),
    ('プレイヤー2', 68),
    ('プレイヤー3', 72),
    ('プレイヤー4', 78),
    ('プレイヤー5', 81);

-- サンプルテーブルの作成とデータの作成

-- テーブルの作成

CREATE TABLE ゴルフ

(

プレイヤー NVARCHAR(20),

スコア INT

);

-- データの挿入

INSERT INTO ゴルフ

(プレイヤー, スコア)

VALUES

('プレイヤー1', 76),

('プレイヤー2', 68),

('プレイヤー3', 72),

('プレイヤー4', 78),

('プレイヤー5', 81);

上記のゴルフテーブルへのデータのインサートは、複数のレコードを1回のSQLで一括挿入する形式で記述しています。
1回のINSERTで複数行のレコードをテーブルに挿入する方法については、以下の記事を参照してください。

特定のスコアの順位を取得する

ゴルフテーブルに対して特定のスコアの順位を取得するSQLは次のようになります。

-- 特定のスコアの順位を取得するSQLのサンプル
-- 順位を取得するSELECT
SELECT
    COUNT(*) AS UNDER
FROM
    ゴルフ
WHERE
    スコア < 75;

-- 特定のスコアの順位を取得するSQLのサンプル

-- 順位を取得するSELECT

SELECT

COUNT(*) AS UNDER

FROM

ゴルフ

WHERE

スコア < 75;

上記のSQLを実行した結果は、スコアが75より小さいレコードが2件なので「2」が返ってきます。スコアが75の場合の順位は3番目になるので、上記のSQLのCOUNT関数の結果に1加算すれば順位を取得することができます。

-- 特定のスコアの順位を取得するSQLのサンプル
-- 順位を取得するSELECT
SELECT
    COUNT(*) + 1 AS RANK
FROM
    ゴルフ
WHERE
    スコア < 75;

-- 特定のスコアの順位を取得するSQLのサンプル

-- 順位を取得するSELECT

SELECT

COUNT(*) + 1 AS RANK

FROM

ゴルフ

WHERE

スコア < 75;

上記のSQLで順位の「3位」が取得できます。

実行結果

順位を付ける方法は分かったので、後は順位を付ける対象となるすべてのレコードを取得するSELECTステートメントの中でどのように順位を付けるかです。

SELECTで順位を付ける

順位を付ける対象となるすべてのレコードをSELECTステートメントで取得しつつ、それぞれのレコードに順位を付けるようにするにはSELECT句の中に順位を取得するSELECT句を記述します。

-- SELECTステートメントで各レコードの順位を取得する
SELECT
    プレイヤー,
    スコア,
    (SELECT COUNT(*) + 1 FROM ゴルフ AS GOLF WHERE GOLF.スコア < ゴルフ.スコア) AS RANK
FROM
    ゴルフ
ORDER BY
	スコア;

-- SELECTステートメントで各レコードの順位を取得する

SELECT

プレイヤー,

スコア,

(SELECT COUNT(*) + 1 FROM ゴルフ AS GOLF WHERE GOLF.スコア < ゴルフ.スコア) AS RANK

FROM

ゴルフ

ORDER BY

スコア;

実行結果

順位を取得することが確認できます。

順位を飛ばさずに順位を付ける

上記のSQLでは同じスコアのプレイヤーが複数いた場合には同じ順位が付けられます。そして次にくるプレイヤーの順位は同順位のプレイヤーの数を足した順位まで飛ぶことになります。
例えば、1位のスコアが68で1人、2位のスコアが72が2人いた場合、次の順位は3位ではなく4位になります。

プレイヤーの順位を重複させるためにデータを再作成します。

-- 一旦データの削除
DELETE FROM ゴルフ;
-- データの挿入
INSERT INTO ゴルフ
    (プレイヤー, スコア)
VALUES
    ('プレイヤー1', 72),
    ('プレイヤー2', 68),
    ('プレイヤー3', 72),
    ('プレイヤー4', 78),
    ('プレイヤー5', 81);

-- 一旦データの削除

DELETE FROM ゴルフ;

-- データの挿入

INSERT INTO ゴルフ

(プレイヤー, スコア)

VALUES

('プレイヤー1', 72),

('プレイヤー2', 68),

('プレイヤー3', 72),

('プレイヤー4', 78),

('プレイヤー5', 81);

上記のSQLではスコアが72で2位のプレイヤー（プレイヤー1とプレイヤー3）を作成しています。

データを再作成してSQLを実行すると結果は次のようになります。

実行結果

順位が飛ばないようにするにはどうすればいいでしょうか。

順位を取得するSQLのFROM句の部分を次のように変更することで、順位が飛ばない形式（DENSE_RANK形式）でレコードごとの順位を取得することができます。

-- 順位が飛ばない形式で取得した順位
SELECT
    プレイヤー,
    スコア,
    (
    SELECT COUNT(*) + 1
    /*
     * DISTINCTキーワードを使用してFROM句の
     * ゴルフテーブルのスコアが重複しないようにする
     */
    FROM (SELECT DISTINCT スコア FROM ゴルフ) AS GOLF
    WHERE GOLF.スコア < ゴルフ.スコア
    ) AS RANK
FROM
    ゴルフ
ORDER BY
    スコア;

-- 順位が飛ばない形式で取得した順位

SELECT

プレイヤー,

スコア,

(

SELECT COUNT(*) + 1

* DISTINCTキーワードを使用してFROM句の

* ゴルフテーブルのスコアが重複しないようにする

FROM (SELECT DISTINCT スコア FROM ゴルフ) AS GOLF

WHERE GOLF.スコア < ゴルフ.スコア

) AS RANK

FROM

ゴルフ

ORDER BY

スコア;

FROM句でゴルフテーブルを参照していた部分をサブクエリにし、DISTINCTキーワードを使ってスコアが重複しないようにします。
重複がなくなったスコアの一覧に対して各レコードのスコアを照合して、それぞれのレコードが持つスコアより小さいスコアのレコードをカウントして1を足します。
上記のSQLを実行すると以下のように、順位が飛ばない結果を取得することができます。

取得結果

MySQLでの順位の取得

最後にMySQLで変数を利用して順位を取得する方法を紹介しておきます。

まずはSQLServerのDENSE_RANK関数のような結果となるSQLです。

-- DENSE_RANK形式の順位付けSQL
SET @rank=0, @previous_score=0;
SELECT
    CASE
        WHEN @previous_score = スコア THEN @rank
        ELSE @rank:=@rank+1
        END AS RANK,
    プレイヤー,
    @previous_score:=スコア AS スコア
FROM
    ゴルフ
ORDER BY
    スコア;

-- DENSE_RANK形式の順位付けSQL

SET @rank=0, @previous_score=0;

SELECT

CASE

WHEN @previous_score = スコア THEN @rank

ELSE @rank:=@rank+1

END AS RANK,

プレイヤー,

@previous_score:=スコア AS スコア

FROM

ゴルフ

ORDER BY

スコア;

@rankが順位を保持する変数になります。@previous_scoreが前のスコアを保持する変数になります。
ORDER BY句で指定した順番にレコードが評価されます。
順位(RANK)はスコアが変わった場合にのみ1加算されるようにCASE式で分岐しています。

次にSQLServerのROW_NUMBER関数のような結果となるSQLです。

-- ROW_NUMBER形式の順位付けSQL
SET @rank = 0;
SELECT
    @rank:=@rank + 1 AS RANK,
    プレイヤー,
    スコア 
FROM
    ゴルフ
ORDER BY
    スコア;

-- ROW_NUMBER形式の順位付けSQL

SET @rank = 0;

SELECT

@rank:=@rank + 1 AS RANK,

プレイヤー,

スコア

FROM

ゴルフ

ORDER BY

スコア;

@rankが順位を保持する変数になります。
ORDER BY句で指定した順番に順位(RANK)が毎レコード1加算されます。