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

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

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

順位を付けるSQL

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

順位の取得方法

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

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

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

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

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

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

1回のINSERT(インサートSQL)で複数行のレコードを一括挿入(追加)する
SQLServerやMySQLなどのデータベースで、テーブルにレコードをINSERT文使用して追加するには、通常は以下のように記述します。 ...

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

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

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

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

実行結果

スコア75の順位の取得結果

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

SELECTで順位を付ける

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

実行結果

全レコードの順位の取得結果

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

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

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

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

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

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

実行結果

順位が重複している場合の取得結果

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

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

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

取得結果

順位が飛ばないDENSE_RANK形式の取得結果

MySQLでの順位の取得

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

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

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

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

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