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

MySQLで、Oracleのrank関数っぽいことを、SELECT文だけで頑張ってみた。テーブルの読み込みは１箇所だけだから、副問い合わせとかで複雑なSELECT文になっていても応用できそうな気がする。

#考え方

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

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

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


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

```sql:関数によるユーザー変数を含む式の評価
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
```

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

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


# 実践

## 元となるテーブルの作成

```sql:テーブルの作成とデータ投入
-- 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 ('種類Ａ', '名前１', '1');
INSERT INTO hoge_table (syurui, namae, atai) VALUES ('種類Ａ', '名前５', '5');
INSERT INTO hoge_table (syurui, namae, atai) VALUES ('種類Ａ', '名前９', '9');
INSERT INTO hoge_table (syurui, namae, atai) VALUES ('種類Ｂ', '名前２', '2');
INSERT INTO hoge_table (syurui, namae, atai) VALUES ('種類Ｂ', '名前３', '8');
INSERT INTO hoge_table (syurui, namae, atai) VALUES ('種類Ｃ', '名前６', '6');
INSERT INTO hoge_table (syurui, namae, atai) VALUES ('種類Ｃ', '名前６', '66');
INSERT INTO hoge_table (syurui, namae, atai) VALUES ('種類Ｃ', '名前７', '7');
INSERT INTO hoge_table (syurui, namae, atai) VALUES ('種類Ｄ', '名前４', '4');
```

```sql:データ投入の確認
SELECT * FROM hoge_table;
```

| id | syurui | namae | atai |
|---:|:-------|:------|-----:|
| 1 | 種類Ａ | 名前１ | 1 |
| 2 | 種類Ａ | 名前５ | 5 |
| 3 | 種類Ａ | 名前９ | 9 |
| 4 | 種類Ｂ | 名前２ | 2 |
| 5 | 種類Ｂ | 名前３ | 8 |
| 6 | 種類Ｃ | 名前６ | 6 |
| 7 | 種類Ｃ | 名前６ | 66 |
| 8 | 種類Ｃ | 名前７ | 7 |
| 9 | 種類Ｄ | 名前４ | 4 |


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

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

```sql:同値は別のランク
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|
|:------|:------|-----:|----:|
|種類Ａ | 名前１ | 1 | 1|
|種類Ａ | 名前５ | 5 | 2|
|種類Ａ | 名前９ | 9 | 3|
|種類Ｂ | 名前２ | 2 | 1|
|種類Ｂ | 名前３ | 8 | 2|
|種類Ｃ | 名前６ | 6 | 1|
|種類Ｃ | 名前６ | 66 | 2|
|種類Ｃ | 名前７ | 7 | 3|
|種類Ｄ | 名前４ | 4 | 1|


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

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

```sql:同値は同ランク
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|
|:------|:------|-----:|----:|
|種類Ａ | 名前１ | 1 | 1|
|種類Ａ | 名前５ | 5 | 2|
|種類Ａ | 名前９ | 9 | 3|
|種類Ｂ | 名前２ | 2 | 1|
|種類Ｂ | 名前３ | 8 | 2|
|種類Ｃ | 名前６ | 6 | 1|
|種類Ｃ | 名前６ | 66 | 1|
|種類Ｃ | 名前７ | 7 | 3|
|種類Ｄ | 名前４ | 4 | 1|