MySQLで種類や分類でランク付けする方法

考え方

別項目にして出現順でも上手くいくこともありますが、リファレンスマニュアルには次の記載がありました。

SELECT などのほかのステートメントでは、予想した結果が得られることもありますが、これは保証されません。次のステートメントでは、MySQL が最初に @a を評価し、続いて 2 番目の割り当てを行います。
SELECT @a, @a:=@a+1, ...;
ただし、ユーザー変数を含む式の評価の順序は、定義されていません。

https://dev.mysql.com/doc/refman/5.6/ja/user-variables.html より引用。

複数のユーザー定義変数を1項目でまとめて変更するには、上記ページの User Comments に CONCAT のトリックがあったので、それを発展させ、CONCATと、IFもしくはCASE文を使ってみます。

関数によるユーザー変数を含む式の評価
SELECT
    IF( CONCAT( @idx := @idx + 1, @a1 := c1, @a2 := c1, ... ) = '', null, @idx ) AS rank
FROM t, (SELECT @idx := 0, @a1 := '', @a2 := '', ...) AS rowInfo
ORDER BY
    c1 ASC

次の3点に注目してください。

  • 対象となるテーブル(例では t)は、1度しか読まない。
  • ユーザー定義変数への設定を、CONCATの引数として複数指定可能。
  • ORDER BYでランクの順序を指定。

実践

元となるテーブルの作成

テーブルの作成とデータ投入
-- DROP TABLE hoge_table;
CREATE TABLE hoge_table (
  id        INT         NOT NULL AUTO_INCREMENT,
  syurui    VARCHAR(45) NOT NULL,
  namae     VARCHAR(45) NOT NULL,
  atai      INT         NOT NULL,
PRIMARY KEY (id));

INSERT INTO hoge_table (syurui, namae, atai) VALUES ('種類A', '名前1', '1');
INSERT INTO hoge_table (syurui, namae, atai) VALUES ('種類A', '名前5', '5');
INSERT INTO hoge_table (syurui, namae, atai) VALUES ('種類A', '名前9', '9');
INSERT INTO hoge_table (syurui, namae, atai) VALUES ('種類B', '名前2', '2');
INSERT INTO hoge_table (syurui, namae, atai) VALUES ('種類B', '名前3', '8');
INSERT INTO hoge_table (syurui, namae, atai) VALUES ('種類C', '名前6', '6');
INSERT INTO hoge_table (syurui, namae, atai) VALUES ('種類C', '名前6', '66');
INSERT INTO hoge_table (syurui, namae, atai) VALUES ('種類C', '名前7', '7');
INSERT INTO hoge_table (syurui, namae, atai) VALUES ('種類D', '名前4', '4');
データ投入の確認
SELECT * FROM hoge_table;
id syurui namae atai
1 種類A 名前1 1
2 種類A 名前5 5
3 種類A 名前9 9
4 種類B 名前2 2
5 種類B 名前3 8
6 種類C 名前6 6
7 種類C 名前6 66
8 種類C 名前7 7
9 種類D 名前4 4

同値は別ランクを振る場合

syurui毎にnamaeの昇順でランクを取得。同値はataiの昇順で別ランク。

同値は別のランク
SELECT
     syurui
    ,namae
    ,atai
    ,IF(
         @befor_syurui = syurui
        ,IF( CONCAT(@befor_syurui := syurui, @idx := @idx + 1) = '', null, @idx)
        ,IF( CONCAT(@befor_syurui := syurui, @idx := 1       ) = '', null, @idx)
     ) AS rank
FROM
     (SELECT
         @befor_syurui := ''
        ,@idx := 0
     ) AS rowinfo
    ,hoge_table
ORDER BY
     syurui ASC
    ,namae ASC
    ,atai ASC
;
syurui namae atai rank
種類A 名前1 1 1
種類A 名前5 5 2
種類A 名前9 9 3
種類B 名前2 2 1
種類B 名前3 8 2
種類C 名前6 6 1
種類C 名前6 66 2
種類C 名前7 7 3
種類D 名前4 4 1

同値は同ランクを振る場合

syurui毎にnamaeの昇順でランクを取得。同値は同ランク。
もう少しスマートに書けそうな気もする・・・。

同値は同ランク
SELECT
     syurui
    ,namae
    ,atai
    ,IF(
        CONCAT(
            CASE
                WHEN @befor_syurui = syurui AND @befor_namae = namae THEN CONCAT( @idx := @idx + 1                )
                WHEN @befor_syurui = syurui                          THEN CONCAT( @idx := @idx + 1, @rank := @idx )
                ELSE                                                      CONCAT( @idx := 1       , @rank := 1    )
            END, CONCAT(@befor_syurui := syurui, @befor_namae := namae)
            ) = ''
        , null, @rank ) AS rank
FROM
     (SELECT
         @befor_syurui := ''
        ,@befor_namae := ''
        ,@idx := 0
        ,@rank := 0
     ) AS rowinfo
    ,hoge_table
ORDER BY
     syurui ASC
    ,namae ASC
;
syurui namae atai rank
種類A 名前1 1 1
種類A 名前5 5 2
種類A 名前9 9 3
種類B 名前2 2 1
種類B 名前3 8 2
種類C 名前6 6 1
種類C 名前6 66 1
種類C 名前7 7 3
種類D 名前4 4 1
Sign up for free and join this conversation.
Sign Up
If you already have a Qiita account log in.