SQLで順位付け -SELECT文でランキングをつくる-

対象読者

• RANK関数が使えないDBMSを使用している方(Oracle, SQL Server, PostgreSQLはRANK関数により実現可能)
• SQLを使って頭の体操をしたい方(自分ならどのようなSQLを書くか考えてみてください)

順位付けの対象（平均年収）

中国・四国地方のデータを使ってみます。

県名	平均年収
愛媛県	388万円
岡山県	385万円
広島県	386万円
香川県	383万円
高知県	379万円
山口県	395万円
鳥取県	386万円
島根県	369万円
徳島県	387万円

DODA 平均年収ランキング2017（47都道府県の平均年収）をもとに作成

上記のデータから以下のような結果を出力するにはどうすればよいでしょう？

県名	平均年収	順位
愛媛県	388万円	2
岡山県	385万円	6
広島県	386万円	4
香川県	383万円	7
高知県	379万円	8
山口県	395万円	1
鳥取県	386万円	4
島根県	369万円	9
徳島県	387万円	3

順位付けSQL

SQL文

実行環境：MySQL 8.0.11

--県名　　：prefecture_name
--平均年収：average_income
SELECT
    i1.prefecture_name AS '県名'
    , i1.average_income AS '平均年収'
    , (SELECT count(i2.average_income)
        FROM income i2
        WHERE i1.average_income < i2.average_income) + 1 AS '順位'
FROM
    income i1
ORDER BY
    順位;

出力結果

山口がダントツの1位！鳥取はどうしたんでしょうか。

説明

SELECT句の3列目で"順位"を生成しています。主問合せ(i1)の各行に対して副問合せ(i2)を実行し、主問合せ(i1)の行の平均年収の値よりも大きい副問合せ(i2)内の行数をカウントします。

例えば、平均年収の一番高い山口県は、「WHERE i1.average_income < i2.average_income」を評価しても395万円より大きい値は出てこないため、副問合せのカウント結果は0件となります。ただ、0位というのは順位として正しくないため、カウント結果に+1して順位として出力する必要があります。

平均年収が同じデータが複数ある場合は同じ順位となり（鳥取県と広島県）、その次のデータ（岡山県）の順位は、「直前の順位＋直前の順位を持つデータの個数」となります。図でいうと、鳥取県と広島県が同じ4位であり、その次の岡山県は6位になっています。

おまけ：順位をテーブルで保持する

順位は他のカラム（この場合は平均年収）から求めてることのできる導出項目であるため、カラムとして保持することはあまりおすすめできないですが*1、何らかの理由で必要なケースもあると思うので書いておきます。

*1 導出項目をカラムとして保持する場合、導出元となるデータと導出項目との整合性が問題となります。例えば、導出項目を生成するのが30分間隔であれば、30分間はデータの不整合が起こりうるため注意が必要です。
ちなみに、整合性を確保する手段の一つとしてトリガーがあるので使用を検討するのもありです。
トリガーとは何か -使い所と問題点を考える-

実行環境：MySQL 8.0.11

SET SQL_SAFE_UPDATES = 0; --SQLの制限を解除する

UPDATE 
    income
    , (
        SELECT
            i1.income_id
            ,i1.prefecture_name AS '県名'
            , i1.average_income AS '平均年収'
            , (SELECT count(i2.average_income)
                FROM income i2
                WHERE i1.average_income < i2.average_income) + 1 AS '順位'
        FROM
            income i1
        ORDER BY
            順位
    ) income_rank
SET
    income.rank = income_rank.順位
WHERE
    income.income_id = income_rank.income_id;

最後に

RANK関数を採用しているDBMSが増えていることもあり、当記事のSQLを実際使うかといわれると微妙なところですが、「こういうSQLの使い方もあるのか」と思っていただければ幸いです。例えば、SELECT文の中でSELECTというのは他の目的のSQLにも応用できるかもしれません。応用できそうな用途を考えてみてください。

参考文献

達人に学ぶ SQL徹底指南書