Help us understand the problem. What is going on with this article?

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

More than 1 year has passed since last update.

対象読者

  • 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徹底指南書

wanko5296
SE. Views are my own. アウトプット先はこちらになりました→https://wannko5296.hatenablog.com/
https://wannko5296.hatenablog.com/
Why not register and get more from Qiita?
  1. We will deliver articles that match you
    By following users and tags, you can catch up information on technical fields that you are interested in as a whole
  2. you can read useful information later efficiently
    By "stocking" the articles you like, you can search right away